8 Replies Latest reply on Jul 23, 2012 1:18 PM by BryanN

    " Trimming Down " a Value List

    BryanN

      Title

      " Trimming Down " a Value List

      Post

      See attached db relationships to help.

       

      In order to relate work orders to child tables (packing lists, inventory, purchase orders, etc), I currently have a drop down list populated by a value list that will insert the work order's pk in the the child tables fk fields (it uses the pk serial# but shows the better known, manually assigned unique order# generated by employees to make it easy).

       

      Issue here being that we will have about 6,000 jobs once I do my db import from accounting.  Not good to have a value list with 6,000+ jobs and growing.  

       

      I remember something about table occurances and such to where you can run 'query' type calculations, but I don't know if that would apply here.  Once work orders are completed, they are marked with a status of 'closed'.  Ideally, I'd like to make the value list be populated with only '≠ closed' work orders.  Any suggestions as to how?

      Screen_Shot_2012-07-23_at_11.54.11_AM.png

        • 1. Re: " Trimming Down " a Value List
          philmodjunk

          I'm going to spell out a short, quick answer, but then include a list of links where you can explore your options further:

          In your WorkOrders table, you can define this calculation field:

          If ( status ≠ "Closed" ; _pk_WO_Work_Order_ID )

          This field will be empty when the WO is closed. Thus, you can set up your value list to refer to this calculation field instead of _pk_WO_Work_Order_ID and get a list of ID's from only those WO's that are not closed.

          This is a simplified version of a conditional value list, but one that avoids defining an additional relationship in order to make it work. That's the "pro". The "con" is that it's a lot less flexible than using a relationship to do this.

          In the "tutorial" link, this method is described as "option 1":

          Forum Tutorial: Custom Value List?

          Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

          Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

          Hierarchical Conditional Value lists: Conditional Value List Question

          Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

          Since you are working with potentially long lists of values, you may also find the methods illustrated in this demo file worth a look:

          https://www.dropbox.com/s/0pm1gdqcfi2ndpv/EnhancedValueSelection.fp7

          • 2. Re: " Trimming Down " a Value List
            BryanN

            Also, I would love to be able to sort that value list by the secondary field in decending order.  Would that still work?

            • 3. Re: " Trimming Down " a Value List
              philmodjunk

              I'd love to tell you differently, but ascending order is the only possible order.

              Note: if you use the secondary field in a sort order, the values in the secondary order must be unique or duplicate values in the secondary field will disappear from your value list even though values in the first field are unique.

              example:

              1 John Smith
              2 John Smith

              where the numbers are field 1 and the name is field 2.

              If you specify sorting by the secondary (name) field, you get:

              1 John Smith

              and the second value with "John Smith" is omitted from the list.

              • 4. Re: " Trimming Down " a Value List
                BryanN

                So this works correctly if I am making a new purchase order towards an open work order.  However, if I were to close the work order but go back to the purchase order, it now only displays the pk_WO_ID instead of the second field (lets call it work order name).

                 

                To be clear:

                The field fk_WO_ID which is a pop up-value list based on calculated values omitting closed work orders, fills in fine upon data entry.  After that WO has been closed, the Work Order Name disappears from the fk_WO_ID field and shows only the pk_WO_ID - omitting the Work Order Name due to it being omitted being closed.

                 

                Any thoughts?

                • 5. Re: " Trimming Down " a Value List
                  philmodjunk

                  So this works correctly if I am making a new purchase order towards an open work order.  However, if I were to close the work order but go back to the purchase order, it now only displays the pk_WO_ID instead of the second field (lets call it work order name).

                  Yes, this is typical of a popup menu where you hide the ID and display the name from the secondary field. I suggest changing your field format to use a drop down list and then add the name or description field from the related table as this will not change when the status changes on the WO.

                  • 6. Re: " Trimming Down " a Value List
                    BryanN

                    So this works correctly if I am making a new purchase order towards an open work order.  However, if I were to close the work order but go back to the purchase order, it now only displays the pk_WO_ID instead of the second field (lets call it work order name).

                    Yes, this is typical of a popup menu where you hide the ID and display the name from the secondary field. I suggest changing your field format to use a drop down list and then add the name or description field from the related table as this will not change when the status changes on the WO.

                     

                    Got ya.  Lesser of 2 evils.  Not thrilled with the look of it but it'll have to do.

                    • 7. Re: " Trimming Down " a Value List
                      philmodjunk

                      With a bit of creative layout design, the result can be very close to what you have now.

                      Put the name field on your layout, give it an opaque fill color that matches your layout background. Use behavior settings to deny access to the field while in browse mode. Put this field on top of the drop down list formatted ID field so that the field is either completely covered or all but the arrow feature is covered.

                      In browse mode, clicking on the name field puts the cursor into the ID field hidden behind it and the value list deploys (If there is no arrow feature, with the arrow feature, user has to click the arrow just like any other field with an arrow). After selecting a WO from the drop down list, it disappears back behind the Name field.

                      • 8. Re: " Trimming Down " a Value List
                        BryanN

                        That's a slick way to do it.  I can even just use a normal pop up at that point and it works the same.  Thanks!