Only Unique Values in Dynamic Drop Downs

I have now managed to make my three Dynamic Drop Downs to work (with some much appreciated help) but now have another issue.

Setup: Select Practice (Dynamic List works fine), select Date (works fine), select Vet - This one works but not fully. What it does is show all Vets associated with a Practice but these are not then ALSO filtered by the Date selection in the previous drop down as I was expecting.

I am probably just getting muddle up in how the database and dynamic drop downs operate but I cannot see my error (i suspect it is with Create Dynamic Array). Once everything selected I need it to show me what Vets are in that Practice on that Date - nothing else. The child and grandchild tables share the same database table.

Also:

How can I return only unique values in the drop downs? I have one drop down based on dates and the same date can repeat in the database but I would like it to only come up once to be able to select it.

Thank you.

the trick to making this work is to concatenate the branch and date in the child recordset and use that as the child value in the Child Create Dynamic array.
Do the same concatenation in the grandchild recordset, and use it as the parent value in the grandchild create dynamic array behavior.


If you need help with this send a backup of your database and I will give more specifics.

Hi Jason,

I know of concatenation but have never actually used it - I would not be sure how to use it in this implementation. I have included a backup of the relevant table that both the Child and grandchild use and the code from the page is on the top post.

Thanks.

your parent list uses a the branch table, can you send this table as well so I can create a sample for you.

Here it is.

So the child and grandchild recordsets will get created as :




child:

SELECT veterinary_surgeon_table.branch_number, veterinary_surgeon_table.surgery_date, CONCAT(veterinary_surgeon_table.branch_number, veterinary_surgeon_table.surgery_date) as childID
FROM veterinary_surgeon_table

grandChild:

SELECT veterinary_surgeon_table.vet_id, veterinary_surgeon_table.vet_name, CONCAT(veterinary_surgeon_table.branch_number, veterinary_surgeon_table.surgery_date) AS parentID
FROM veterinary_surgeon_table

When adding the rsChild Create Dynamic Array behavior, you will not be able to select the childID alias created by the recordset, so set the child id to the unique_id column, you will then have to go into the code and change the unique_id column name to childID to use the alias.

same thing when createing the dynamic array for the rsGrandchild recordset, you will need to set the Parent ID to the unique_id column, then go into the code and change it to use the parentID alias.

Hi Jason,

That seems to have worked though I will have to look at it more closely to understand what it is doing.

One other question - it feels like I am almost there!

How do I ask it to return only unique values in the drop down. You will see from my set up that the child drop down could return the same date three time. Selecting any of the three identical dates correctly shows the three available vets in the grandchild drop down but it would be neater to simply have one of the same date...

Thanks again.

add:
GROUP BY veterinary_surgeon_table.surgery_date

to the child recordset

SELECT veterinary_surgeon_table.branch_number, veterinary_surgeon_table.surgery_date, CONCAT(veterinary_surgeon_table.branch_number, veterinary_surgeon_table.surgery_date) as childID
FROM veterinary_surgeon_table
GROUP BY veterinary_surgeon_table.surgery_date

Hi Jason,

My child recordset now looks like this (I have a session variable in there too):

SELECT veterinary_surgeon_table.branch_number, veterinary_surgeon_table.surgery_date, CONCAT(veterinary_surgeon_table.branch_number, veterinary_surgeon_table.surgery_date) as childID FROM veterinary_surgeon_table WHERE practice_id = %s GROUP BY veterinary_surgeon_table.surgery_date ORDER BY surgery_date ASC

But now for one surgery it returns no date and the other only 1 date, 21st March 2010. There is definitely more data in the database, can you see where I am going wrong.

remove the group by statement.


since you want to have the end result filter by both the data and the branch number you are going to have to show the duplicate dates.

The way this is working is that the first list passes the branch number.

in the second list we are using the branch number concatenated with the date to filter the third list.


values for the second list will look like:

30512-30-3010

this is branch number: 305 date: 12-30-2010