5 Replies Latest reply on Jan 28, 2013 10:51 AM by philmodjunk

    Conditional Value List by Non-Indexed Field



      Conditional Value List by Non-Indexed Field


           I am making a basic inventory system. For our purposes let's say I have the following tables: "Inventory", "Inventory Depletion", "Inventory Addition", and "Build". The "Inventory" table contains the actual inventory record. In "Inventory" the remaining amount is calculated by subtracting the sum of the amounts in all related "Inventory Depletion" records from the amount in the related "Inventory Addition" record. In "Build" you select an inventory item from a drop down (the value list consists of the Inventory ID field in "Inventory") and pull a specified amount into the recipe you are building by creating an associated "Ingredient Depletion" record from the "Build" layout with a script. I want the drop down list in "Build" to contain only the Inventory ID's for the "Inventory" records which have remaining amounts greater than zero. The fact that the values for the remaining amounts in the "Inventory" records are not indexed is creating issues for me. What is the proper way to do this?

        • 1. Re: Conditional Value List by Non-Indexed Field

               Option 1;

               Use script triggers on your depletion and addition layouts that perform a script that computes the current inventory for that one item and updates either an inventory quantity field or a flag field that is set to 1 if inventory is greater than zero. Either method produces an indexed field that can be used with your conditional value list.

               Option 2:

               Don't use a value list. Set up a portal to your inventory with a filter on it to omit records with a balance on hand of zero. A relationship using the X operator instead of the default = sign will produce a relationship that matches to all inventory records. A button in this portal row can be used to select the desired inventory item for your build record when clicked. Note that a more sophsicated combination of filtering and scripting can be used with such a portal to allow you to type part of the inventory item's name and the portal can update keystroke by keystroke to list only items in inventory that start with or contain the text you enter.

          • 2. Re: Conditional Value List by Non-Indexed Field

                 Thanks Phil I think I am going to start with option one until I have the thing up and running then get fancy.

            • 3. Re: Conditional Value List by Non-Indexed Field

                   Note that there is a "safe but slow" option for script based computing of the inventory balance and a "fast but risky" way to compute it.

                   "fast but risky", simply adds the quantity from the newly added "Addition" record and subtracts the quantity from a newly added "depletion" record. You have to be very careful not to let such a script be performed on the same record twice. And things get especially tricky if you change the existing quantity field--say to correct a mistake as you then have to subtract the original value added in your "addition" table, then add in the new quantity.  A single error in that and your total will be wrong on each subsequent record that adds or depletes the quantity.

                   "safe but slow" pulls up the related inventory record and computes a new inventory balance for that one item from the context of this one record by adding all the related "Addition" quantities and subtracting all the related "Depletion" quantities. This second method can be much slower than the first--especially if you have 1000's of change records logged for that one item, but wil be 'self correcting'--unlike the first method.

                   You may find you have to go with "fast but risky" to avoid unacceptable delays, but if you do, I suggest also implementing "safe but slow" as an alternative so that you can recalc the inventory balances for one or a group of inventory items should you discover that some of your script computed inventory balances are not correct.

              • 4. Re: Conditional Value List by Non-Indexed Field

                     What I have is a formula in a total field in "Inventory" which adds all the related "Inventory Additions" and subtracts all the related "Inventory Depletions" to derive a total. Does this fall into either category? I'm fairly well versed in Excel but not with programming which is why I thought to do it that way I suppose. I think this would be the reason I needed to create a flag field to indicate when the balance is zero and the lot should be taken off the list of available inventory since the total is a claculation and not indexed. 

                • 5. Re: Conditional Value List by Non-Indexed Field

                       A script that copies the contents of that field to a stored, indexed field to make your conditional value list work would be an example of the "safe but slow" option. "Slow", as I am using the term here, is a relative term. "Slower" (than the "risky option) might be more accurate.