3 Replies Latest reply on Feb 26, 2014 10:43 AM by philmodjunk

    Cascading conditional lookups, keep final result only?



      Cascading conditional lookups, keep final result only?


           I've got a 4 tier conditional lookup working now.  How do I store just the end result?

           More detail

           Inventory has a field called code.  This is an abbreviation I can use in a relation to pick up any chunk of the related species I need.

           Plants has large amount of information about each species/cultivar I grow. 

           I have a 4 tier set of conditional lookups.  They mostly work.  E.g.

           Class has a choice of Leaf or Cone.  Pick Cone.  Genus now has a list of Picea, Abies, Larix  Pick Larix.  Now I have a choice of larcina or siberica.  Pick larcina.  Now I have a choice of code of LarLar.

           Ok that works:  Sort of. 

           1.  As soon as I make a pick from the top tier, how do I clear the next ones.

           2. At the 4th level there is only one possible code.  But I still have to click it to make it change.  How do I default to the first available value?

           3.  I don't want to do all of this in the inventory table.  I only want to store the final code in the inventory table. 

           At present my tactic for this is to add a third table "Do_Lookup" that actually does the lookups.  Once code is determined, a script updates the current inventory record with the value of code, then deletes the single record in do_lookup.  Is there a better way to do this? 

           (Eventually, Code will have a button that will bring up a popover to do all of this.)


           Barking up the wrong tree?  Barking mad?



        • 1. Re: Cascading conditional lookups, keep final result only?

               1) Why would the fields not be clear already? A script trigger on the top tier field can perform a script that clears the other fields

               2) if there is only one possible code, why do you need it at all for your conditional value lists? (I am guessing that we are talking value lists here.)

               3) Can you explain what you are trying to accomplish with this?

               And why you don't want to do this in the Inventory table?

               How does that inventory table work? In some systems, you have one and only one record for each item in your inventory table you might have 4 Douglas Firs in inventory, but only one record for Douglas Firs in the Inventory table. In other systems, Inventory is the name of the field where you log each change in inventory. Propagate 3 more trees and log the increase in Inventory. Ship out a bunch of trees and log the decrease...

          • 2. Re: Cascading conditional lookups, keep final result only?

                 1.  Because if you are editing an existing record the first field may already be correct.  So clicking on any field should clear the remaining fields in the selection process.

                 Also:  This seemed like a good exercise to write a parameter based function.


                 2.  The idea is the Class, Genus and Species act as a 3 touch selector to chose code to put into the inventory database.


                 3.  As to what I'm doing:

                 It's spring.  I need to see what lived, what died, what sprouted, what got the top eaten by deer, what was sold since the last inventory, what new stuff that I haven't recorded yet.  It's the standard inventory check proceedure that most businesses have to do periodically to verify that reality and the the computer's view are brought back into sync.

                 The inventory stores the code, or abbreviation from the lookups.  It does not store the Class Genus, and Species.  Taht's redundent.  I may have 12 different blocks of white spruce in various ages.  In the inventory list I store PicGlauc, and while I'm taking inventory it may display Picea Glauca, White Spruce, etc, as a sanity check, but I don't want it to store it.  I've already stored it.  Indeed, given the limited space on the screen, probably just touching the Code to bring up the popover, to check, then touching cancel to dismiss it may be all I end up doing.

                 So Inventory in essence has



                 height range (two fields)

                 pot size

                 percent saleable 

                 date modified.


                 The values list is hopefully a way to enter the code for a new or incorrect entry by a series of touches:  One touch on code to bring up the popover window, one touch on class to popup  Cone, Leaf, Food, Perennial, One touch to select; two touches to do the same for genus; two touches to do the same for species.  At this point code is uniquely determined.  A species only has one code.  Ideally I don't need to touch it.  I've already made  7 touches.  One more touch to 'accept' or to 'cancel' At this point I want just the code value to be returned to the field in inventory. 

                 In some cases the class is correct, but the Genus or species is incorrect. E.g. A batch of black spruce mistakenly inventoried as black hills spruce.  Or alpine fir listed as balsam fir.  Or Hill poplar listed as Tower poplar. Hence the need to clear from any given entry point down.


                 4.  More of what I'm doing:

                 Later these codes + potsize generate a new table with additional fields for Target height, Target price, Salable height, discount rate if under target,

                 The inventory table, the Plant_Names table, and the Targets table can then be combined to produce an availability list for customers, as well as a current retail value of inventory statement for the tax man, and a short list of what needs to be transplanted, sorted by % over target height.


                 In time:  While I'm doing inventory with the ipad, take 3-4 snaps of each block of trees, adding them (or links to them) into the Inventory database.

                 In time, sales will decrement inventory, but with living things inventory is a slipery target.

            • 3. Re: Cascading conditional lookups, keep final result only?

                   1. Then script triggers on your fields can clear the "down range" fields when their value is changed.

                   2. Ok. but if the field always has the same value, your "3 touch selector" is really a 2 touch selector. And you don't need the field to be an editable field in your table if it always has the same value. You can either set up the field to auto-enter the needed value in each new record or you can define it as a calculation field that always returns the same value. In terms of your conditional value list functions, it doesn't even need to be present on your layout (Though you may have other very good reasons for placing it on your layout.)


                        3. That's redundant.

                   True, but it's only a few fields and it's the simplest way to get your conditional value lists to function as you need them to function. You can certainly stay with doing the look ups from the context of a different table, but that's just moving the complexity from one table to another, and I can't quite see how they will work for you from a popup if you are editing data from inventory. You could, if you want, make each of these fields except the last fields with global storage. That brings in a different set of trade offs, but then you will not be storing the additional data in each record of your inventory table.