5 Replies Latest reply on Jun 8, 2012 6:57 PM by russbrad

    Maintain Relationship when Fields are Empty

    russbrad

      I've set up 4 conditional value lists to narrow down the options for data-entry. Each list's items are related to all the selections before them, in order:

       

      Product Category >> Manufacturer >> Price Group >> Description

       

      I've used the recommended method of establishing a table relationship for each new field, then basing the value list on that relationship. The system works well for most products, but some product categories don't have a manufacturer or a price group. When that happens, there are no related records displayed.

       

      For example I have a product category Administration Costs for which there are no Manufacturers or Price Groups, but only Descriptions. If I select that product category, then the product descriptions aren't available, because the table relationship requires that all three preceding fields be a match. I'm assuming that when a field is empty, there is no relationship inferred.

       

      What I'd like is for the relationships to be maintained even when there are empty fields. There is another way to do it, but it's a bit of a hack solution that I'd rather avoid.

        • 1. Re: Maintain Relationship when Fields are Empty
          russbrad

          Okay, after a bit more searching I've found a solution here:

           

          https://fmdev.filemaker.com/message/15043#15043

           

          The method suggested is to concatenate the values in a separate field, then compare the concatenated values.  This is an old post, though, so if there's a better solution I'm interested to hear it.

          • 2. Re: Maintain Relationship when Fields are Empty
            GordonShewach

            In addition to the solution you found, you could consider requiring users to enter values for all 4 categories. I'm thinking they all likely have Product Categories and Manufacturers, but perhaps not Price Groups. If so, you can just use something generic (like "A") for the price group.

             

            If my guess is right that it's just the 3rd category (Price Group) that they don't all have, you could also consider using 3 categories, eliminating Price Group. Then you could concatenate Price Group and Description for those that have a Price Group (e.g., A-Widget, A-Gadget, B-Thingamajig, etc.).

             

            Gordon Shewach

            Desktop Services

            Ann Arbor, MI

            • 3. Re: Maintain Relationship when Fields are Empty
              LyndsayHowarth

              As Gordon suggests, you could require or artificially create values for 'empties'.

               

              And, yes... you could concatenate values...

               

              I am wondering what is the purpose for this particular heirarchy? There are many ways to achieve what I think you are wanting and I think the heirachical methodology is a limiting one. WHat happens if you want to use the heirarchy starting with Manufacturer? you can't.

               

              What say you had the fields to choose from or leave empty as required... and calculated a list as a field of the values selected for the LHS of the relationship.

              Then... In the actual records, you create a list of the values from each of the fields as a autoenter calculation field for the RHS of the relationship.

              This would allow you to find records where the collated values exist in the records lists field.

              This would mean that you could eg just choose a Manufacturer to get all products by that manufacturer then you could select a price group to further filter.

               

              HTH

               

              - Lyndsay

              • 4. Re: Maintain Relationship when Fields are Empty
                russbrad

                Thanks for the suggestions.

                 

                Manufacturer here refers to other companies, so anything produced in-house doesn't have a manufacturer or a price group.

                 

                Also, requiring entries for all categories just opens the door to errors when someone inevitably forgets.  I could write a script that inserts "N/A" into any field that can't be left empty, but if the solution could be invisible to the user, that would be the best way.

                • 5. Re: Maintain Relationship when Fields are Empty
                  russbrad

                  If I understand your suggestion correctly, you'd have 7 relationships:

                   

                  1 Category

                  2 Manufacturer

                  3 Price Group

                  4 Category & Manufacturer

                  5 Category & Price Group

                  6 Manufacturer & Price Group

                  7 Category, Manufacturer & Price Group

                   

                  Then filter by whichever relationship covered the selected options.  Is that right?

                   

                  I'll give that a go, anyway, and let you know how I get on.