9 Replies Latest reply on Apr 8, 2015 7:12 AM by philmodjunk

    Make a lookup field display a product type from a related parent table rather than a code to which...

    lbspath_1

      Title

      Make a lookup field display a product type from a related parent table rather than a code to which this field is physically related.

      Post

      I again came across with a problem. My main register table has a field containing product_id. This is just a number which is not very informative. How can i make this field display a textual product name so that I do not see the Product)id at all, and in the main register I deal with textual Product name only. Thanks.

       

        • 1. Re: Make a lookup field display a product type from a related parent table rather than a code to which...
          philmodjunk

          A product ID and a product name are two different items of data and should not be stored in the same field.

          Are you trying to implement a value list where you select a product by name, but enter it's ID in order to link the current record to a record in a table of products?

          • 3. Re: Make a lookup field display a product type from a related parent table rather than a code to which...
            philmodjunk

            Simplest option first:

            If you have a "use values from field" value list where field 1 is the ID and field 2 is the name, use a pop up menu format and specify in the value list set up to only show values from field 2.

            But if you really want that drop down list format...

            2nd option:

            Assuming this relationship:

            LayoutTable>-----Products

            Put the product name field from Products on your layout. When you select a product ID with your drop down list, this field from products will show the name.

            3rd option: (2 with a bit fancier design)

            Use the behavior section of the inspector's data tab to block browse mode access to the name field from the related field. Give it an opaque fill color and put it on top of your drop down list formatted ID field. (do not select the arrow option.) When you click on the name field, the drop down list hidden behind it will pop to the front and deploy. Once you have selected a product, it disappears back behind the name field that now updates to show the name of the selected product.

            4th, 5th etc. options:

            See "Adventures in FileMaking #2 - Enhanced Value Selection" for a number of more sophisticated methods that you might use for selecting a product and linking it by ID to your current record in the layout's table. Methods include:

            A drop down list where you can't see the ID number even when it is a previously selected value and you are selecting a different value in the current record.

            A name based list that allows auto-completion yet still links records by ID.

            and others using popovers and automatically updating search portals...

            • 4. Re: Make a lookup field display a product type from a related parent table rather than a code to which...
              lbspath_1

              Thanks a lot  PhilModJunk

              I will try all techniques mentioned in your file.

               

              • 5. Re: Make a lookup field display a product type from a related parent table rather than a code to which...
                lbspath_1

                Dear  PhilModJunk

                Thank you for your suggestions. 

                I tried several of them. Options 1 and 2 are not perfect because they still show numbers on entry to the object.

                Option 3 would be perfect, although quite time consuming, but is I would be able to save these steps somehow so that not to repeat them again and again, wold be the best solution. Unfortunately, even this option has some downsides. First, it takes some time to execute the script Go To Object. That is why three is a slight but quite noticeable delay in opening the dropdown list. Second and most inconvenient is - the objects related to calculation fields do not stop on Tab. That is not good at all. 

                Do you think I can use instead of the calculated Name filed the genuine Name field from the Products table somehow safely so that I prevent from accidental editing?

                 

                • 6. Re: Make a lookup field display a product type from a related parent table rather than a code to which...
                  philmodjunk

                  You seem to be confusing 3 with 4 as there is no script used with #3. Actually hiding the object and the script used to make that happen isn't really necessary unless you simply don't want the number value to ever appear under any circumstances. With 3, the number value only appears when you are selecting a product in the same field and record for the 2nd or subsequent time.

                  the objects related to calculation fields do not stop on Tab. That is not good at all. 

                  I afraid that I don't get what you mean by that and why it's a problem.

                  The demo file also describes several other options. My preferred method is an auto-complete enabled list of product names with a lookup enabled to link the records by ID as this allows you to enter a few letters of the name and then the list filters down to a much shorter list of values.

                  • 7. Re: Make a lookup field display a product type from a related parent table rather than a code to which...
                    lbspath

                    Dear  PhilModJunk

                    Yes it is option 4 with script. By Stop on Tab I mean that when you press the Tab key on the keuboard the cursor jumps to the next field. Many people including myself still prefer this way of navigation rather than by mouse. Unfortunately the cursor does not stop on dropdown box from option 4. I investigated this and in Tab Order Help it says the objects related to calculated fields do not stop on Tab in Browse, but do it in Find mode.

                    I also can't find how to open the drop down list by keys. In MS Access under Windows you press Alt + down arrow and the list drops down. Here in FM under mac I am struggling  to find the appropriate shorcut. Do you know it?

                    Next I will try your other option with autocomplete list.

                     

                    • 8. Re: Make a lookup field display a product type from a related parent table rather than a code to which...
                      philmodjunk

                      If a field is formatted with a drop down list and it does not have the "arrow" specified, simply putting the cursor into the field causes it to deploy. There is no keyboard shortcut for that other than using a script, tab, return or enter keys to move the cursor into the field in the first place.

                      What you could do to enable tab to open the hidden drop down is to set up OnObjectKeystroke on the field that precedes it in your intended tab order to perform a script that does nothing until the tab key is pressed. In that case, it uses perform script to perform the script to deploy the drop down list. I will repeat my observation that the only difference between 3 and 4 is very minor--you are only going to see the ID in 3 if you select the wrong product by mistake and then return to the field to select a different product.

                      Also both 3 and 4 suffer from the same limitations as a simple drop down list--once the list of values gets long, it's a bit of a bother to scroll through the long list looking for the right value. Being able to type the first letter to scroll the values to the first value with that letter helps, but it's still far from ideal. A number of the other examples in the adventure file demonstrate ways to search out and select a value from a longer list of values by entering some partial text that then filters the list down to a much smaller subset of the total list--thereby making the selection process much faster and simpler for the user.

                      • 9. Re: Make a lookup field display a product type from a related parent table rather than a code to which...
                        philmodjunk

                        BTW, I went back to the Adventure file and ran some tests as I didn't recall any noticeable "delay" when working with the demo. I did find a technical issue that I'll have to address in the next release, but I'm not observing any noticeable delay.