Subform field search using lookup object

[Costa]

Hi everyone,

back here with more questions. I have a form and a subform so once in edit mode I see the form header and all the subform records linked to the form. The subform has various fields but I would need to do a search by creating 3 filters on 3 fields using 3 lookup objects placed in the edit mode form. It is obvious that the use of the lookup object is used to choose the fields of that specific column.
I looked at what was done on the demo site with filters and iframes and topics on the forum, but I didn't find what I needed and my knowledge of PHP is not enough to find a solution.
If anyone can give me the right direction and some tips it would make me happy.
Thank you

[kev1n]

Hi,

Does it have to be a subform or would an embedded browse form suffice?

[Costa]

Hi Kev1n

Subform should be the best but embedded form with print option can be suffice.

Thanks for tour help

[kev1n]

If you're using an embedded iFrame, as in the demo, there's a filter button that sets a hash cookie and refreshes the iFrame.
The embedded browser uses the hash cookie in it's SQL. You don't need PHP to do this, just some JavaScript.

[Costa]

Hi Kev1n,
sorry if I didn't reply right away but I have been traveling for work for the last 5 days.
Using the demo site I tried to do reverse engineering but I'm missing some steps that maybe you could help me understand.
I created a table called "test" with the following fields idtest, test1, test2 and test3.
I created a form with 3 text objects and I set the form's run object as an iFrame.
This Form is exactly like the addresses form in the demo, including the "delete record" flag.
Then I added two text objects called test1_filter and test2_filter and lastly I created a button input object called filter_button including the custom code in which I replaced the field name with test1_filter.

Code: Select all

var f = $("#Button_Run_iFrame")[0].contentWindow;
f.nuSetProperty('Filter_Firstname', $('#filtro_test1').val());
f.nuGetBreadcrumb();

but I couldn't get it to work. Just one doubt, I couldn't find anywhere what is called 'Filter_Firstname' in the button custom code..... Did I miss something?

Thanks
Costa

[kev1n]

In the onclick event of the filter button add this code:
Replace "Button_Run_iFrame" with the object Id of your run object.

Code: Select all

var f = $("#Button_Run_iFrame")[0].contentWindow;
f.nuGetBreadcrumb();

(Note that the nuSetProperty() is not required)

Then, in the embedded form, the Browse SQL looks something like this:

Code: Select all

SELECT * FROM address
WHERE 
first_name like '%#Edit37_iFrame_Filter#%' AND last_name like '%#Edit38_iFrame_Filter#%'

In this example, first_name and last_name are columns of the address table.
Edit37_iFrame_Filter and Edit38_iFrame_Filter are the object IDs of the fields on the main form (rename them accordingly).

[Costa]

Hi Kev1n,
I'm writing again in this post because I found an error in the use of filters that I can't understand.

The situation is as described above: a form with an embedded iframe and multiple select objects set as filters for the iframe.
Everything works perfectly except when I ask the iframe module to add or clone records because I receive the following sql error:

Code: Select all

[0] : ===PDO MESSAGE===

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE  = ''' at line 1

===SQL===========

SELECT * FROM  WHERE  = ? 

===BACK TRACE====

/share/CACHEDEV1_DATA/Web/chico/core/nuform.php - line 734 (nuRunQuery)

/share/CACHEDEV1_DATA/Web/chico/core/nuform.php - line 773 (nuGetOtherLookupValues)

/share/CACHEDEV1_DATA/Web/chico/core/nuapi.php - line 95 (nuGetAllLookupValues)

The error itself does not seem to affect anything but sometimes, especially if it is the first opening of the form, the record for which the clone is requested is not saved.

The where section of the query is as follows:

Code: Select all

WHERE
    ((compmat_materiale like '%#filtromateriale#%') AND 
    (compmat_centro_di_costo like '%#filtroCdC#%') AND 
    (compmat_iddocumento like '%#filtroprogetto#%') AND 
    (compmat_fasi_lav like '%#filtrofaselav#%') AND 
    (compmat_idddt like '%#filtroddt#%') AND
    (compmat_idsal like '%#filtrosal#%'))

If I remove the where section and try to clone or add records, I don't get any sql errors so it's all related to filters.

I thought the problem was in the filters as some of them could return a null value if they didn't have a selection and so I replaced the where string using COALESCE

Code: Select all

WHERE
    ((compmat_materiale like COALESCE('%#filtromateriale#%',"")) AND 
    (compmat_centro_di_costo like COALESCE('%#filtroCdC#%',"")) AND 
    (compmat_iddocumento like COALESCE('%#filtroprogetto#%',"")) AND 
    (compmat_fasi_lav like COALESCE('%#filtrofaselav#%',"")) AND 
    (compmat_idddt like COALESCE('%#filtroddt#%',"")) AND
    (compmat_idsal like COALESCE('%#filtrosal#%',"")))

but nothing has changed.

Do you have any of your great tips?

Thanks for any help.
Costa

[kev1n]

Hi Costa,

Would it be possible to export the form (using the cloner) and also the table(s) so that I can try to recreate your problem?
(Remove any confidential information before uploading, either here or send it to me by private message)

[Costa]

Problem solved thanks to Kev1n who discovered a piece of code in a lookup object introduced for testing and then forgotten to remove.

I would like to thank Kev1n for the fantastic support he has always given to everyone, from the most experts to the poor arteriosclerotic old men, regardless of whether the problem is nubuilder or lack of experience, always showing great professionalism.