2. PHPRunner General questions
  3. [SOLVED]  How to have two 'Parent" drop down lists to filter data in a third list
This topic is locked
[SOLVED]

 How to have two 'Parent" drop down lists to filter data in a third list

 		8/20/2014 6:40:55 PM
PHPRunner General questions
M
 mfriend author

Hi Everyone:
I have used a different PHP product in the past that allowed multiple Parent fields to filter the results of a drop down list in a data entry form, but I can't figure out how to do it in PHPRunner.
Here is what I have:

  1. I have a table that has three fields I want to use.
    The First drop down list is called "Stackyards".
    Once they select the appropriate stackyard it filters the results of the second dropdown lists called "Crops."
    The third drop down (called "Cutting") needs to be filtered by both the Stackyard and Crops dropdown. If it is filtered by just Stackyards, then it lists all the Crops (many of which will not be at that stackyard). If it is filtered by just the Crop list, then it will contain all the cutting numbers available in the table instead of just the cuttings for the specific crop types available at a stackyard.
    So: a stackyard is a storage location.

    a crop is a type of hay (alfalfa, grass, etc.)

    a cutting tells how many times the field was cut (a later cutting is better quality hay normally than an earlier cutting).
    So What I need to to know exactly what crops and their cutting numbers are available at a specific stackyard. (cutting numbers being limited by both stackyard and by the crop selected in those two dropdowns). I hope that makes sense. The other product I used previously allowed 5 different parents fields to filter by. I'm hoping I'm just missing something.
    EDIT:
    After doing further research i found a couple of posts that suggested using something like:

    "STACKYARD ='".$_SESSION["STACKYARD"]."'" in the where statement (I also tried it in this format: " OrderType=".$_SESSION["OrderType"]). The Stackyard field is a varchar2 Oracle field.. This didn't seem to work for me, but it may be heading in the right direction.
    Here is a pic of how I have it set up on the cuttings dropdown:


The Table structure looks like this:


So I need to save the results of the Stackyard combo box and use it in the where statement to filter the cuttings and crops. I'm using Oracle 11g R2 for the database.
Thanks,

Matthew
HJB 8/21/2014

http://xlinesoft.com/livedemo/web/login.php
This generally sounds to be a job for the PHPRunner built-in CREATE A REPORT feature where your wished FILTERING is done by using the ADVANCED SEARCH button, here to select the appropriate stackyard. Above URL demo talks about PHPRunner's Enterprise Version Add-On called "Web Reports" which could be an alternative.
M
 		mfriend author 8/21/2014



http://xlinesoft.com/livedemo/web/login.php
This generally sounds to be a job for the PHPRunner built-in CREATE A REPORT feature where your wished FILTERING is done by using the ADVANCED SEARCH button, here to select the appropriate stackyard. Above URL demo talks about PHPRunner's Enterprise Version Add-On called "Web Reports" which could be an alternative.



Hi Walk2fly:
Thank you for the idea(s) on filtering with reports. I guess I didn't specify in my original question but this is for data entry screen(s). I want to limit the possible number of crops folks can choose by using the stackyard dropdown as a filter for crops. Then the third drop down needs to be filtered by both the stackyard AND the Crop dropdowns:
Example: There are a possible 80 or so stackyards (storage locations). There are a possible 14 or so crops, though any one stackyard normally will have only 2 or 3. There are a possible 6 or so Cuttings of each crop, though for each crop there likely will only be 2 or at the most three cuttings available.
So say stackyard 14D has Alfalfa 1st and 2nd cutting, Alfalfa/Grass 3rd and 4th cutting, and Bean Stubble 1st cutting.

By filtering the drop down screens on the data entry screen once they select Stackyard they have a shortened list of three items.
Once they select the Crop then they have the stackyard and crop selected.
But here is the problem: If the Cutting is filtered by just the Crop field, it will look at every occurrence of Alfalfa in the table (which may have other cutting numbers at other stackyards) and give the end user 3 or 4 different cuttings to choose from, resulting in some error.
If it is filtered by just the stackyard, then the end user will be given the option to select from all 4 of the Cuttings present at the stackyard (cutting 1, cutting 2, etc.).
By filtering by both the Stackyard and Crop as its parents, the end user will only be presented with the option of Cutting 1 or 2 which will prevent entering the wrong cutting number into the database. I tried this with the old product I used to use yesterday and it worked perfectly. I'm rather new to using PHPRunner for more in-depth projects so I'm probably just being dumb missing how to do it in PHPRunner.
Matthew
HJB 8/22/2014

Matthew,
fully got your point under the keyword "targeting the most error-free usability". Rather than to overthrow your steps already taken, I think, it's worth to start from the user's login.
Example: After user login, the drop down selection is filtered by multiple chained drop downas per the tutorial seen under http://www.xlinesoft.com/tutorials/MultipleDropdowns.html while number of cuttings field should be set to number and under "avoid duplicates" feature which is giving that very safety not to enter a wrong cutting number to the selection.
Next to it, it seems that the built-in option "user and can only edit his own entered data" could/would even increase the safety in regard to usability while built-in DYNAMIC users administration can fine tune of what tables are going to be offered to the user for selections.
In other words, LESS means sometimes MORE when it comes to fields and content to be offered to the user.
Stay tuned - walk2fly
M
 		mfriend author 8/22/2014



Matthew,
fully got your point under the keyword "targeting the most error-free usability". Rather than to overthrow your steps already taken, I think, it's worth to start from the user's login.
Example: After user login, the drop down selection is filtered by multiple chained drop down...

Stay tuned - walk2fly



Hi Walk2fly:
I've already tried using the multiple chained dropdowns and it does not do what I need.
Multiple chained dropdowns filters like this:

  1. User selectes the stackyard (where the crops are stored).

    -- so they are limited to the crops that are available and instock at that stackyard.
  2. The user then gets a filtered Crops dropdown that displays just the crops available at that stackyard... again good.
  3. The third dropdown and where my difficulty is, is with the cuttings. Each crop can have several cuttins (between 1 and 6 per year).
    === So if I set Crops to be the control (master) for Cuttings then I will get the cuttings that that crop has for every one of those crops in every stackyard: Example: If I select Alfalfa, then I will get (using distinct) a list of every cutting for that crop in the entire table: probably 3 or 4 different cuttings.
    === If I set the cuttings dropdown to use stackyard as the control (master) then I will (again using distinct) get pretty much every cutting there is at that stackyard (and since there are several varieties of crops that will be way too many).
    What the other PHP product provides easily, and what I want to accomplish somehow using PHPRunner is:
  4. Filter by two Masters or Controls: So for Cuttings it is limited at the same time by Stackyard (to just the crops in that stackyard) and at the same time to cuttings available for the specific type of crop. This will ensure that the users chose a crop and cutting number that are instock at that specific stackyard without them having to guess.
    The other PHP product does this very easily (in fact it allows up to 5 different parents/masters) but there is so much more that PHPRunner does as far as designing the screens, customizing, etc. that I would much rather use PHPRunner. I'm hoping to get a few good jobs done with it so I can justify to my supervisor the need to upgrade to 8.0 (and hopefully get more licenses also).
    Thanks,
    Matthew
M
 		mfriend author 8/26/2014

I guess since no one has said how to filter a field by two parent fields this is not possible in PHPRunner?
I have to decide for sure which PHP development program to use in the next couple of days (project due next Wednesday) and being able to filter by two parents is really necessary (I like PHPRunner more than the other program, but I made the mistake of showing the person I'm developing the app for how the fields could be filtered using two parents using the other program and he liked it a lot, especially since it would cut down on the number of incorrectly entered data.
I spent a couple hours again this evening trying a few things from the advanced section of the PHPRunner's user manual but they did not work for me.
Please help, anyone...
Matthew
Sergey Kornilov admin 8/27/2014

PHPRunner doesn't have an option to filter dropdown list by two parent controls.
However it's fairly easy to implement since PHPRunner allows you to use custom view as a source for dropdown box. I will explain from standard Cars database point of view. First dropdown box is Make, second dropdown box is Model, third one is YearOfMake. Imagine that Model field is not unique and cannot be used to filter YearOfMake values. However Make+Model combination is unique and using a custom view as a source for Model dropdown box you can handle this.
Here is the sample SQL query:

select ID, Make, Model, concat(Make,Model) as MakeModel from Cars


Third dropdown box can be also based on custom view:

select ID, concat(Make,Model) as MakeModel, YearOfMake from Cars
M
 		mfriend author 9/25/2014



PHPRunner doesn't have an option to filter dropdown list by two parent controls.
However it's fairly easy to implement since PHPRunner allows you to use custom view as a source for dropdown box. I will explain from standard Cars database point of view. First dropdown box is Make, second dropdown box is Model, third one is YearOfMake. Imagine that Model field is not unique and cannot be used to filter YearOfMake values. However Make+Model combination is unique and using a custom view as a source for Model dropdown box you can handle this.
Here is the sample SQL query:

select ID, Make, Model, concat(Make,Model) as MakeModel from Cars


Third dropdown box can be also based on custom view:

select ID, concat(Make,Model) as MakeModel, YearOfMake from Cars



Hi Sergey:
The method you suggested may work for a table with a singular primary key. My 'table' is actually a view. The primary key would have to be a composite primary key composed of Stackyard, Crop, and Cutting. I can't select just the stackyard to auto-fill the BALE_YEAR, CROP, and CUTTING fields as a result. Is there a way to use multiple parents to filter when there is no single pk column?
Sergey Kornilov admin 9/26/2014

I have absolutely no idea how primary keys are relevant here. You do not need primary keys at all to build dependent dropdowns. You can link them by any single field or by concatenating multiple fields into single one.
M
 		mfriend author 10/4/2014



I have absolutely no idea how primary keys are relevant here. You do not need primary keys at all to build dependent dropdowns. You can link them by any single field or by concatenating multiple fields into single one.



Hi Sergey:
Sorry, I figured out a different way and had responded while thinking of a different way.
Instead of worrying about having a field filtered by multiple parents, I used your idea of a concat field on the view I used to supply the available stackyards.
Since the view didn't have a primary key it wouldn't let me auto-fill fill the dependent four fields. I used the concatinated field as the "primary key" for the view within PHPRunner which then allowed me to auto-fill the following 4 fields. (so for example, the dropdown might include: 18D - 2013 - Alfalfa - 1 - 300 (meaning Stackard 18, baled in 2013, crop is alfalfa, cutting is 1, and number of bales available is 300).
Thanks for the idea.
Matthew