10 Replies Latest reply on Jan 6, 2017 2:10 PM by filopastrymaker

    Enter data by either of 2 lookups

    filopastrymaker

      In a Products table I have SKU and Description.

       

      In a Order Line Items table I also have SKU and Description.

       

      When creating new records in the Order Line Items table, there will be instances where the user knows the SKU and instances where the Description is known.

       

      I can create a lookup which looks up the Description from the SKU. I can also create a lookup which looks up the SKU from the Description.

      My understanding is that two Table Occurrences are required.

       

      However if the user selected the wrong SKU or description, how can it be edited? The data remains the same, presumably because the two lookups are conflicting?

        • 1. Re: Enter data by either of 2 lookups
          philmodjunk

          Yes, you would need two relationships to two different occurrences of Products, one that matches by SKU, one by Description.

           

          No, this should not render either the SKU or the Description field uneditable. Entering a new SKU should look up a new description and entering a new description would enter a new SKU. Make sure that you are using fields that auto-enter values and aren't defined as calculation fields. (I haven't downloaded your file as I don't have time to track down the needed older version of FileMaker it would take to open a .fp7 file.)

           

          With a newer version of FileMaker, I'd suggest setting up a search portal inside a popover. Entering text in the portal's search field "filters down" the list of products in the portal to just those that contain the search text and then clicking a row in the portal adds the SKU (or better yet, internally generated product ID) From Products to a line item record for your order. This then works from a single relationship that looks up only a product ID whether the user enters a SKU or a product description. This is still possible in older versions of FileMaker, but you can't put the portal inside a popover so it takes up a lot more layout space than with current versions.

          • 2. Re: Enter data by either of 2 lookups
            filopastrymaker

            Thanks for the response. It appears that what I have done should work.

             

            Entering either SKU or Description populates the other field correctly, but trying to change either once there is a value in each field does not work, it reverts to the entered value

            • 3. Re: Enter data by either of 2 lookups
              golife

              It may be that you have to uncheck that existing field values can not be overwritten? This is in the field definitions.

              • 4. Re: Enter data by either of 2 lookups
                philmodjunk

                Best guess is that the two look ups are interfering with each other. Have you tried auto-enter calculations instead? You can get the same basic look up behavior if you clear the "do not replace..." check box. But I wouldn't be surprised to see issues arise from it as well.

                 

                You may need to put in place a widget that enters the same ID from the same occurrence of Products whether you enter a SKU or part of a description.

                • 5. Re: Enter data by either of 2 lookups
                  golife

                  Wirh two relationships, either name inserts a new SKU or a new SKU inserts the corresponding name. It must work.

                  • 6. Re: Enter data by either of 2 lookups
                    philmodjunk

                    How to set up a selection portal:

                     

                    Use this relationship to start:

                     

                    Invoices::SearchField X Products::anyfieldcanGoHere

                     

                    Set up a portal based on this relationship and give it the following portal filter expression:

                     

                    PatternCount ( Products::Description ; Invoices::SearchField )

                     

                    Set up the OnObjectModify trigger on the search field to do this:

                     

                    Set Variable [$SelectionStart ; value: Get ( SelectionStart ) ]

                    Commit Records [ ]

                    Set Selection [Invoices::SearchField ; start position: $SelectionStart ; end position: 0 ]

                     

                    The commit records causes the portal to refresh with each keystroke. The rest of this code puts the cursor back where it was at the time you pressed a key.

                     

                    Then turn the fields in the portal row into a button that does this:

                     

                    Set variable [$InvoiceID ; Invoices::__pkInvoiceID ]

                    Set Variable [$ProductID ; Products::__pkProductID ]

                    Go to layout [ "LineItems" (LineItems) ]
                    New Record/Request

                    Set Field [LineItems::_fkInvoiceID ; $InvoiceID ]
                    Set Field [LineItems::_fkProductID ; $ProductID ]

                    Go to layout [original layout]

                     

                    Note: The above is quickest to describe, but an altenative method for creating records in a related table can be used called "MagicKey" that does not require changing layouts like this. It requires an additional relationship but avoids issues that can arise from the layout change. You can research that name if you are interested in that approach.

                     

                    A simpler, less user friendly approach is also possible:

                     

                    Set up the SKU field with a value list of SKUs and descriptions. You can use this one value list to select products by SKU or description. This is not as friendly a method once you get a fairly large number of products appearing in your value list.

                    • 7. Re: Enter data by either of 2 lookups
                      filopastrymaker

                      golife wrote:

                       

                      Wirh two relationships, either name inserts a new SKU or a new SKU inserts the corresponding name. It must work.

                      Did you download the file? I agree it must work, but it doesn't...

                      • 8. Re: Enter data by either of 2 lookups
                        golife

                        Unfortunately not. I can not read fp7 files. The format is too old. It needs to be converted at least to FM12 I believe.

                        • 9. Re: Enter data by either of 2 lookups
                          keywords

                          Here is a thought for a different approach. Create a productID in the Products table and a foreign key field in Line Items talk into which you import the productID. base your relationship on that. Then both fields can be set to auto-enter the relevant detail from the same relationship. Then, if you wish you can have two instances of the fk field on your layout, each with a different value list, so that users can choose from whichever list they want and get the same result. Alternatively you could create a combined details field and use that in a single list. I've applied this method in the attached revision of your test file.

                          1 of 1 people found this helpful
                          • 10. Re: Enter data by either of 2 lookups
                            filopastrymaker

                            Thanks for the example. I don't fully understand it but I will study it some more and hopefully get my head around it.

                             

                            Do you know what makes the original example not work?