Browse Table Filter Options Topic is solved

[kev1n]

Timo, you need to increase the heights of nuActionHolder and nuBrowseTitle to make room for the dropdown:

Code: Select all

f( nuFormType() == 'browse') {

    // Increase holder height
	$('#nuActionHolder').css({'height': '60px'});
 
    // Increase title height
	var t = -20;
        $('.nuBrowseTitle').not('#nuBrowseFooter').css('top',t );
    
   // Add dropdowns with addBrowseTitleDropDown()
}

[Duski]

Hi there !
Many thanks to kev1n and Alohajoe5 for patience in this discussion - it helped me very much with coding my dropdown column filters ! Thank you !
But I came to a point, where I'm not able to continue. I'm trying to construct three static dropdown filters for columns which are getting data from joined tables. Very likely I have incorrect constructed my SQL statement, because I can see dropdown lists with values, but the browse form returns blank set of records I made many attempts with the SQL statement, but no success

Can U help me pls. ?

My situation looks like this:

Javascript variables:
if (nuFormType() == 'browse') {

var data1 = ["", "Mia", "Pelc Jan", "Bartl Zdeněk"]; // static values to be added to the dropdown
addBrowseTitleDropDown(1, data1); // add dropdown to column 2 (index 1)

var data4 = ["", "beletria", "historický", "zdravie"]; // static values to be added to the dropdown
addBrowseTitleDropDown(4, data4); // add dropdown to column 5 (index 4)

var data5 = ["", "Tlač", "E-kniha", "Audio-kniha"]; // static values to be added to the dropdown
addBrowseTitleDropDown(5, data5); // add dropdown to column 6 (index 5)
}
...

SQL statement:
SELECT
tbl_knihy.*,
tbl_autori.autor AS autor,
tbl_kniky.rok,
tbl_formaty.format,
tbl_jazyky.jazyk,
tbl_kategorie.kategoria AS kategoria,
tbl_media.medium AS medium,
tbl_serie.seria

FROM
tbl_knihy
JOIN tbl_autori ON tbl_knihy.tbl_autori_id = tbl_autori.aut_id
JOIN tbl_formaty ON tbl_knihy.tbl_formaty_id = tbl_formaty.for_id
JOIN tbl_jazyky ON tbl_knihy.tbl_jazyky_id = tbl_jazyky.jaz_id
JOIN tbl_kategorie ON tbl_knihy.tbl_kategorie_id = tbl_kategorie.kat_id
JOIN tbl_media ON tbl_knihy.tbl_media_id = tbl_media.med_id
JOIN tbl_serie ON tbl_knihy.tbl_serie_id = tbl_serie.ser_id

WHERE

((autor = '#nuBrowseTitle1_dropdown#' AND LOCATE('#', '#nuBrowseTitle1_dropdown#') <> 1 )
OR '#nuBrowseTitle1_dropdown#' = '' OR LOCATE('#', '#nuBrowseTitle1_dropdown#') = 1)

AND

((kategoria = '#nuBrowseTitle4_dropdown#' AND LOCATE('#', '#nuBrowseTitle4_dropdown#') <> 1 )
OR '#nuBrowseTitle4_dropdown#' = '' OR LOCATE('#', '#nuBrowseTitle4_dropdown#') = 1)

AND

((medium = '#nuBrowseTitle5_dropdown#' AND LOCATE('#', '#nuBrowseTitle5_dropdown#') <> 1 )
OR '#nuBrowseTitle5_dropdown#' = '' OR LOCATE('#', '#nuBrowseTitle5_dropdown#') = 1)

ORDER BY
tbl_knihy.nazov ASC

[kev1n]

In your Browse Form, press CTRL+SHIFT+I (Option Menu -> Form Info) to inspect the generated SQL.
Try running in phpMyAdmin to see if there are any errors.

[Duski]

phpMyAdmin says: #1054 - Unknown column 'tbl_autori.autor' in 'field list'
But structure of table tbl_autori looks OK with field autor

Snímka obrazovky 2022-02-01 145613.png

[kev1n]

Can you try removing the alias names ?

Instead of

Code: Select all

tbl_autori.autor AS autor,

write

Code: Select all

tbl_autori.autor,

etc.

[Duski]

I started my attempts without aliases - didn't work.
Now I removed aliases - and continues not working.

[kev1n]

Can you paste the generated SQL here?

[Duski]

SELECT kniha_id,nazov,autor,seria,jazyk,kategoria,medium,format,rok FROM tbl_knihy JOIN tbl_autori ON tbl_knihy.tbl_autori_id = tbl_autori.aut_id JOIN tbl_formaty ON tbl_knihy.tbl_formaty_id = tbl_formaty.for_id JOIN tbl_jazyky ON tbl_knihy.tbl_jazyky_id = tbl_jazyky.jaz_id JOIN tbl_kategorie ON tbl_knihy.tbl_kategorie_id = tbl_kategorie.kat_id JOIN tbl_media ON tbl_knihy.tbl_media_id = tbl_media.med_id JOIN tbl_serie ON tbl_knihy.tbl_serie_id = tbl_serie.ser_id WHERE ((autor = '#nuBrowseTitle1_dropdown#' AND LOCATE('#', '#nuBrowseTitle1_dropdown#') <> 1 ) OR '#nuBrowseTitle1_dropdown#' = '' OR LOCATE('#', '#nuBrowseTitle1_dropdown#') = 1) AND ((kategoria = '#nuBrowseTitle4_dropdown#' AND LOCATE('#', '#nuBrowseTitle4_dropdown#') <> 1 ) OR '#nuBrowseTitle4_dropdown#' = '' OR LOCATE('#', '#nuBrowseTitle4_dropdown#') = 1) AND ((medium = '#nuBrowseTitle5_dropdown#' AND LOCATE('#', '#nuBrowseTitle5_dropdown#') <> 1 ) OR '#nuBrowseTitle5_dropdown#' = '' OR LOCATE('#', '#nuBrowseTitle5_dropdown#') = 1) ORDER BY tbl_knihy.nazov ASC

[kev1n]

Can you post a screenshot with your browse columns (Form Properties -> Browse Tab)

[Duski]

Snímka obrazovky 2022-02-01 162128.png