6 Replies Latest reply on Jul 27, 2017 9:15 AM by Sciman

    Conditional Value List Limiting Items to Unsold

    Sciman

      I would like to create a conditional value list that only populates with items that have not been sold yet.

       

      The basic structure I am using is three related tables: a table for Buyers, a table for Items, and a join table with entries that are made when an item is sold (capturing BuyerID and ItemID).

       

      I am trying to limit the Item value list to only those items not sold yet, based upon a global Filter field in the Buyer table (set to 0) and a calculated Count field in a related ItemAvailable TO that counts the number of occurrences for each item (ItemID) in the join table.  Available items will have a count of 0 and should populate the Item value list, while sold items will have a count of 1 and should not populate the Item value list.  The Item value list, however, is not populating with any items. 

       

      When I change the calculated Count field in the Item table to a number entry field and manually enter 0’s and 1’s for the items, all is fine, so the overall setup seems fine.  As soon as I change back to the calculation to populate the field in the Item table with their actual counts from the join table, however, the item value list will no longer populate…even though the Count field in the Item table is still showing 0’s and 1’s just as when the values were manually entered.

       

      Any idea what I'm doing wrong and how to get this conditional value list to work as desired?

       

      Thanks in advance!

        • 1. Re: Conditional Value List Limiting Items to Unsold
          philmodjunk

          This is due to the fact that your calculation field has to be unstored and thus does have the needed index for a match field on that side of the relationship.

           

          Either use a selection portal with a pirtal filter that omits sold items or use a triggered script to update an indexed number field in order to select for unsold items in your relationship.

          1 of 1 people found this helpful
          • 2. Re: Conditional Value List Limiting Items to Unsold
            Sciman

            Thank you for letting me know the options available at this point.  I was hoping there might have been a way to do this directly in a drop-down list without scripting, but I believe both options you mentioned will require this.

             

            Looking at the selection portal option, I was able to create a portal on the layout that filters based upon the unstored calculated Count field and gives the desired list populated with only available items (looking just like I would have liked in the drop-down list).  Interesting (and somewhat frustrating) that the portal can be populated based upon an unstored calculated field, but the value list can not...

             

            I think I know what I need to do from here, but didn't want to head down these alternate paths unless it was necessary.

             

            Thanks again!

            • 3. Re: Conditional Value List Limiting Items to Unsold
              philmodjunk

              And what scripting is needed for the filtered portal?

               

              using a script to update current totals on hand for inventory management is a pretty common approach as it can improve system performance.

              1 of 1 people found this helpful
              • 4. Re: Conditional Value List Limiting Items to Unsold
                Sciman

                No script was needed to create the list of available items in the filtered portal. 

                 

                Isn't a script required, however, to create the new record in the join table upon selecting the item in the filtered portal?  This isn't a huge deal, of course, but selecting from a drop-down list populated only with available items would win the simplicity award...if it worked in this case.

                 

                Given the options you mentioned above, I actually prefer to use the indexed number field option as it will fit better with the existing layouts.  My concern with this approach, however, is how to ensure that the count remains accurate (e.g. what if one makes changes to the join table records outside of those areas expected and supported by the scripts maintaining the count).  This concern wouldn't exist with the original approach I was hoping for.

                 

                Originally, I didn't want to head down alternate paths due to these concerns, but now I know that it is necessary, so thank you again for clarifying.

                • 5. Re: Conditional Value List Limiting Items to Unsold
                  philmodjunk

                  Yes, a very simple script is needed to set a field to the value selected from the portal. Note that by putting such a portal inside a popover, it can take up no more space on your layout than a drop down list or pop up menu.

                   

                  Keeping the count or status up to date is the major issue behind using that method, Generally, you keep the stored field and unstored calculation field on hand so that you can use one to update the other should you discover that you have missed an event that produced a difference between the stored and calculated values. However, in most systems, the number of events that change inventory usually make up a pretty short list of cases where you need to use a button or script trigger to update the stored data field.

                  1 of 1 people found this helpful
                  • 6. Re: Conditional Value List Limiting Items to Unsold
                    Sciman

                    Thank you so much for your helpful comments and suggestions!