When I am creating a drop down lookup field I see there is an Advanced button there. Clicking this advanced button shows me the Select * SQL that brings all records from the lookup table in to the drop drown. So far so good!
What I want to do is to show only the records from the lookup table that have not yet been assigned into a record by way of my drop down. Imagine the following
Table 1 contains my kids names and the fruits that they ate today.
Table 2 contains a list of fruits in my fridge at a given time.
My kids can't eat the same fruit 2 times. So ...
When I go into record for 1st child and use the drop down, I see Apple, Orange and Banana. 1st child ate an Orange so I choose it.
Then I go into the record for 2nd child and use the drop down, I want to see only Apple and Banana. Selecting one of these should further reduce the options from next time around...
This means I need to change my Select * that brings the data into the dropdown but I don't know how... Anyone tackle something like this yet? Thanks much for your help.
How do I limit the drop down lookup records?
Re: How do I limit the drop down lookup records?
Are you looking to have this happen at the same time? IE - do child 1 then child 2 right away? Or child 1 then child 2 maybe an hour later?
Assuming a time frame between the children then you need to, basically, create an inventory system to debit inventory then a system to increase inventory as you replenish it.
Or is each child unique and have their own inventory? Or is there 5 of 1 item, 3 of another etc.? You will then need to look at quantities available, when did child last pick that item and base the menu upon that fact.
More details are needed. But this is a basic inventory system with a bit of a twist unless you are assuming there is a Apple, Orange and Banana for each child and as each child picks a fruit the next time they cannot see that fruit even if there is quantity in the fridge.
Then you need to program in a counter based upon the day. Say have the entire system rollover at midnight. This is an if statement that checks to see when the last fruit was picked by the child and if before a certain time then they can pick it again.
Provide more details please. Not a very hard thing to do with basic php/mysql coding. This I see as a good exercise to learn coding as it is simple but requires some thinking for a good implementation and use of if/then/else statements.
Alan
Assuming a time frame between the children then you need to, basically, create an inventory system to debit inventory then a system to increase inventory as you replenish it.
Or is each child unique and have their own inventory? Or is there 5 of 1 item, 3 of another etc.? You will then need to look at quantities available, when did child last pick that item and base the menu upon that fact.
More details are needed. But this is a basic inventory system with a bit of a twist unless you are assuming there is a Apple, Orange and Banana for each child and as each child picks a fruit the next time they cannot see that fruit even if there is quantity in the fridge.
Then you need to program in a counter based upon the day. Say have the entire system rollover at midnight. This is an if statement that checks to see when the last fruit was picked by the child and if before a certain time then they can pick it again.
Provide more details please. Not a very hard thing to do with basic php/mysql coding. This I see as a good exercise to learn coding as it is simple but requires some thinking for a good implementation and use of if/then/else statements.
Alan
Calgary, Alberta, Canada - Using Appgini 5.50 -
Re: How do I limit the drop down lookup records?
Hi Alan,
Thanks much for your interest. I think I made the example more complicated that it should have been. Let me try to be more specific and simplify this at the same time.
I am working on an event related application. I have a table where I keep a list of exhibitors who will have presence in this event. I have another table called booths where I list all available booth locations and booth related details. Exhibitor table has a lookup on booth to populate the booth field in the exhibitor table. This is a drop down. So when I create a new exhibitor record I click the Booth drop down and choose a booth id which is prepopulated in the booth table.
All I want to do is to control what is shown in that drop down such that when a booth is allocated to an exhibitor it no longer shows in the drop down as it is no longer available. I was thinking of a complex SQL query to accomplish this task but I think you gave me an idea already.
I will add a new field to the booth table. Lets call it vacant. It will default to true in all records. When I assign the booth I will catch this in the exhibitor table's update/insert hooks and update the booth record such that vacant field for that field will be updated to false.
I will then simply change the dropdown's SQL statement in the Advanced window and add something like where vacant = true. This should then result in not showing the booths that are already taken.
This should work. Let me try! Thanks once again.
Thanks much for your interest. I think I made the example more complicated that it should have been. Let me try to be more specific and simplify this at the same time.
I am working on an event related application. I have a table where I keep a list of exhibitors who will have presence in this event. I have another table called booths where I list all available booth locations and booth related details. Exhibitor table has a lookup on booth to populate the booth field in the exhibitor table. This is a drop down. So when I create a new exhibitor record I click the Booth drop down and choose a booth id which is prepopulated in the booth table.
All I want to do is to control what is shown in that drop down such that when a booth is allocated to an exhibitor it no longer shows in the drop down as it is no longer available. I was thinking of a complex SQL query to accomplish this task but I think you gave me an idea already.
I will add a new field to the booth table. Lets call it vacant. It will default to true in all records. When I assign the booth I will catch this in the exhibitor table's update/insert hooks and update the booth record such that vacant field for that field will be updated to false.
I will then simply change the dropdown's SQL statement in the Advanced window and add something like where vacant = true. This should then result in not showing the booths that are already taken.
This should work. Let me try! Thanks once again.
Re: How do I limit the drop down lookup records?
Ok. I have some success to report with one major setback... Documenting it here for other's who might need a similar feature.
First off, added vacant field (TinyInt) into my booth table. Set as checkbox in AppGini.
Exhibitor booth dropdown setup - click Advanced and edit the Select * to look like this :
SELECT `booth`, `booth` FROM `booth` WHERE `vacant` = 0 ORDER BY 2
Then I add the following in the exhibitor table's hook file in the after_update section :
sql("update booth set vacant = 0", $eo);
First set all records in booth table to have the vacant field reset to 0. This is the easy way out of switching this on & off for each booth that would be updated. Small table so I can get away with updating all records.
sql("update booth set vacant = 1 where booth in
(select distinct booth from exhibitor where booth is not null and booth <> '')", $eo);
Basically this is a nested SQL statement where the second part, the (select distinct booth from ... bit gets a list of all booths which are currently registered in exhibitor records. The first part then goes through the booth records and updates the vacant field to 1 whenever the booth field is found to be in the list compiled using the second part of the query. This works marvelously!
Now if you go to another exhibitor record and drop down the list of booths you will see that the booth you allocated previously does not show.
Here's one major setback though. As soon as you choose a booth and click save, the nested query does its business and the booth table vacant field gets updated to 1 for this booth. Remeber the selective dropdown? Now the dropdown is trained not to show that booth anymore... So guess what happens? The booth code is still in the exhibitor table but because of my constraint set on vacant field the booth code no longer shows on screen.
Is there a way for lookup field to show whats in the native table's relevant field but when drop down is initiated use the regular drop down feature? Investigation continues...
First off, added vacant field (TinyInt) into my booth table. Set as checkbox in AppGini.
Exhibitor booth dropdown setup - click Advanced and edit the Select * to look like this :
SELECT `booth`, `booth` FROM `booth` WHERE `vacant` = 0 ORDER BY 2
Then I add the following in the exhibitor table's hook file in the after_update section :
sql("update booth set vacant = 0", $eo);
First set all records in booth table to have the vacant field reset to 0. This is the easy way out of switching this on & off for each booth that would be updated. Small table so I can get away with updating all records.
sql("update booth set vacant = 1 where booth in
(select distinct booth from exhibitor where booth is not null and booth <> '')", $eo);
Basically this is a nested SQL statement where the second part, the (select distinct booth from ... bit gets a list of all booths which are currently registered in exhibitor records. The first part then goes through the booth records and updates the vacant field to 1 whenever the booth field is found to be in the list compiled using the second part of the query. This works marvelously!
Now if you go to another exhibitor record and drop down the list of booths you will see that the booth you allocated previously does not show.
Here's one major setback though. As soon as you choose a booth and click save, the nested query does its business and the booth table vacant field gets updated to 1 for this booth. Remeber the selective dropdown? Now the dropdown is trained not to show that booth anymore... So guess what happens? The booth code is still in the exhibitor table but because of my constraint set on vacant field the booth code no longer shows on screen.
Is there a way for lookup field to show whats in the native table's relevant field but when drop down is initiated use the regular drop down feature? Investigation continues...