Dynamic Dependant Dropdown Menus

[psweb]

In a perfect world Dynamic Dependent Dropdown Menus are really easy, and generally for me quite reliant on the database structure. When I create a site and i know before starting that it might use geographic entities I might have a database structure like this.

Continent Table

id	continent
1	Africa
2	Europe
3	Asia

---

Country Table

id	country	continent foreign key id
1	Algeria	1
2	Angola	1
3	Benin	1
4	Botswana	1
5	Armenia	2
6	Albania	2
7	Andorra	2
8	Austria	2
9	Afghanistan	3
10	Azerbaijan	3
11	Bahrain	3
12	Bangladesh	3

---

City Table

id	city	continent foreign key id	country foreign key id
1	Algiers	1	1
2	Oran	1	1
3	Ambriz	1	2
4	Andulo	1	2
5	Aragatsotn Province	2	5
6	Ararat Province	2	5
7	Armavir Province	2	5
8	Tirana	2	6
9	Kabul	3	9
10	Kandahar	3	9
11	Agdash	3	10
12	Manama	3	11

---

So thats a perfect world example.

And here is a real world example.
I start off a simple website that is for a single hotel in Washington DC, I complete the website and all are happy. On the website there is an enquiry form that takes user details.

First Name
Last Name
Email
Physical Address
Enquiry
√ Sign up to our Newsletter

The users complete the form and 2 things happen, an email gets sent to the hotel, and an entry gets stored in the database, this entry has some added info like a datetime lets say.

So far all is looking quite straight forward, but 3 years later the client asks for a backend system so they can see all these enquiries as well as many other things. They want to create a mailing list to communicate with all these users.

Now my SQL table looks something like this

id	fname	lname	email	add1	add2	addCountry	source	status	date
1	First	Last	a@g.com	Road	Town	Africa	Website	Subscribed	2-10-2015 10:32:15
2	First	Last	b@g.com	Road	Town	USA	Website	Unsubscribed	7-10-2015 09:02:54

You get the idea, for the mailing list they want to filter all this data by Status then Source then Country
So they want 3 select menus to be dynamically filled with all this data

serverconnect1 - I make a simple query to get all records for display in a table
I then bind serverconnect1 to the status select menu with value and title set to status
I then bind serverconnect1 to the source select menu with value and title set to source
I then bind serverconnect1 to the countries select menu with value and title set to countries
My selects are now dynamic and dependant on what is selected but there are 100 duplicate entries for each record, and after trying multiple things, such as data formatters of unique I can not get it sorted out.

So I add other queries to only get distinct values and multiple conditions to only show dependant on the first selection, but each time a select is used it updates the others and they revert to the first value.
serverconnect2 - I query the same table but only the status field to get distinct values
serverconnect3 - I query the same table but only the source field to get distinct values
serverconnect4 - I query the same table but only the countries field to get distinct values

I must be just overthinking this, anyone got some ideas for me how to best do this, would be great to keep just the one query solution but get rid of all the duplication.
Sorry for the long post and explanation.

[Teodor]

Hello Paul,
You just need 3 separate action files for the 3 dropdowns - each containing a query for every option you need. Just as explained here: https://www.dmxzone.com/go/32764/dmxzone-app-connect-manual/32769/dynamic-dependent-drop-downs-part-2-large-data-sources-3000-records

So:

• serverconnect1 sets the status in dropdown1
• serverconnect2 sets the source in dropdown2 and is fitlered by dropdown1
• serverconnect3 sets the country in dropdown3 and is filtered by dropdown1 and dropdown2

This way when you change the status it WILL update the other two dropdowns, but changing country won’t update the rest.

[psweb]

Thanks @Teodor, so that is exactly what i have working perfectly right now, I will use your video as an example for a moment.

What if I wanted the user to have the ability to choose either the country or the airport dropdown and for both select menus to be dependent on eachother.

In my case I have it working perfectly if the user first sets status, then second sets source, then third sets country.
What i would like though is the user to be able to set any one they like in whatever order they like.

Is this possible.

[Teodor]

Well the answer is in the way you want to filter your data and dropdowns
If every dropdown depends on every other - on change the data is reloaded i.e. new data comes to the dropdown and its being refreshed.

[psweb]

hmm, yes, so then it should work surely, if i have 3 status values and 12 source values and 30 countries and the user only wants to see a list of all entries regardless of status or source in country USA then the data table refreshes and displays perfectly, but the dropdowns, change for a moment and then refresh back to their static default value of Please Select, haha, so it is perfect, but only for a second

[Teodor]

If your dropdowns depend on each others the data will always reload …
If you make dropdowns which do not depend on each others they will keep the values, but then the data you show on the page may be empty sometimes (if user selects a status and a country which doesn’t have this status).

[psweb]

Well thats exactly the issue i am having, it does make perfect sense to me, but i am like a bratty kid who still wants it their way, haahah.

So because the actual data table being displayed is perfectly correct and does all the filtering exactly as i want it to, is there no way to populate the selects with the same data as the filtered table, just with one different, to ignore all duplicate entries? that way it should do exactly what I want it to but just not display
USA
USA
USA
USA
Germany
Germany
Germany

It feels like i am so so close yet so far away.

[Teodor]

Isn’t that what you are doing exactly with the dynamic dropdowns … ? Use distinct on the 3 server actions for the dropdowns, it won;t show duplicates.

[psweb]

Yes, so it is a vicious circle, if i use 3 distinct queries i eliminate the duplicates but have to work in a structure or order of dropdown selection.

If i use the single query for the display table and the dropdowns then it works perfectly and in any order i like but then i have no distinct on that query and can not get rid of the duplication.

Ok, i will concede and use it the way i have it, I was just just hoping there was another way, and figured i would ask just incase. I will go and throw a tantrum now, and then get over it, and move onto the next thing, lol.

This is why i like wappler, i always have some big idea in my head that i know will probably not work before even doing it, but i try anyway, at least in this case i only wasted an hour, with manual coding i would have been at this for 3 days, haha.

[Hyperbytes]

Only skimmed this (still in bed!) But maybe rather than binding 1 query directly to another use the state manager to filter the queries at app connect end and use the onchange() events to set/ unset the query manager values. This should simplify the queries, give more control and then maybe help with the distinct issue. May be talking crap but it was an idea that came in to my head

[psweb]

I tried that and already failed unfortunately, also tried populating an array with the data rather, then applying unique to the array, then populating the select from the array with split, but alas I failed at that idea too. Then I tried 2 variables, one to store the real value onchange and another to hold the original value, but I got so confused with my own idea that i gave up. LOL.

You have the database I am working on, as well as access to the backend of my site, so if you come up with a way somehow i would be super happy

[Hyperbytes]

When i drag my backside out of bed i will take a look, major headcold this am

[psweb]

Ahh so now you brain feels like mine on a standard day, dumbed you down a little. haha.

[patrick]

Why not do it all client-side, not sure how many records your database has, but even with over 10000 records it is very fast to do it client-side.

First add a serverConnect wich will get all the records.

For the selects you can use dmx-bind:options="serverconnect1.data.unique('status')", dmx-bind:options="serverconnect1.data.unique('source')" and dmx-bind:options="serverconnect1.data.unique('addCountry')".

Then add a Data View, as data source use serverconnect1.data and as filter use status == form1.select1.value && source == form1.select2.value && addCountry == form1.select3.value.

You can now use the filtered data from the data view to display the results in a table, you can also use the data view to sort and add paging.

[psweb]

Aha, awesome, I am going to try that in just a moment and will let you know, i think i was using unique incorrectly anyway by looking at your sample, I was going to the status and then trying to add unique which obviously would never add because it was not an Array, so i feel like a bit of a dummy now looking at your sample.
Thank you

[drymetal]

@psweb, if @patrick could of been a fly on the wall last night when we tried the unique stuff, he’d probably disown us. We found one million ways developers should never think.

[psweb]

I think Patrick would have cried listening to our conversation, lol. We mere mortals.

[psweb]

Ok @patrick you idea works as expected but the performance is really bad and was to be expected I have 56000 records with 50 columns in each row, @Hyperbytes is busy trying to fix it as soon as he stops laughing at it but until then I could have an issue with this client side.

Second strange thing is that it does not work unless i remove the optiontext=“textbinding” and optionvalue=“optionid” which means the option text and the option id both have to now be the same in the relational database design.

Wondering in the meantime if I did it a littleincorrect to have not been able to retain the bindings in the Data Source and Text Field and Value Field, maybe

[turn3636]

Just a suggestion, have you tried doing it this way?

I had the same issue. I tried doing my dependent drop downs on client side but performance was too bad. Using this method worked for me.

[psweb]

Unfortunately yes, and that is what i have working right now perfectly, my issue is like in that example on the video, the Airports dropdown only becomes populated once the Country is selected, In my case I do not have many variables, 3 select menus with 3 options in the first, 9 in the second, and about 25 max in the third.

I want all three selects to be able to be used in any order, as independent, or as two or all three, and regardless of what i alter first the other selects must remove the options that are not available.