9 Replies Latest reply on Mar 29, 2011 9:44 AM by philmodjunk

    Simple(?) lookup question



      Simple(?) lookup question


      Just when I think to understand the basics of FMP...

      Hello all,

      In a table, I like to lookup the first- and surname related to a global value gCustomerID. Preferably these first- and surnames concatenated to each other. Please advice me what method and calculation I should use? Thanks in advance!!

      gCustomerID     First name     Surname

      1 John Smith

      2 Ric Faulkner

      All the best,


        • 1. Re: Simple(?) lookup question

          What do you mean by "look up"?

          You could just perform a find on that table for the specified ID number, but that may not be what you have in mind.

          You can have a value list that lists the IDs in column one and column 2 can display the contents of a calculation field that combines the first and last names.

          You can have a layout where selecting the ID 'looks up' the names from a related table.

          What exactly did you have in mind here?

          • 2. Re: Simple(?) lookup question

            Hi Phil!

            Thanks for you reply. The situation is as follows:

            On Layout1, a customer's record is chosen. I use a Script Trigger "OnLayoutExit" to set the gCustomerID with the value of CustomerID.

            On Layout2, a product page, I like to make a button to bill the product to the customer on Layout1. Therefor a text should be shown "Bill article to: " (and then the first/last name of the customer corresponding to the global value gCustomerID).

            Hope this describes the situation a little better?


            • 3. Re: Simple(?) lookup question

              Take a look at this thread: Auto Fill

              It gives you two different options. Examine both options carefully as both have significant differences in how you manage any future changes to the data being looked up.

              • 4. Re: Simple(?) lookup question

                Hi Phil,

                As how I understand your suggested thread, I'm not sure if this will be the solution what I'm looking for. As I understand, in that example you will need to select a person from a dropdown list. The person related to gCustomerID however should be displayed automatically. Please let me know if I misinterpreted your thread?

                All the best,


                • 5. Re: Simple(?) lookup question

                  If you do not select a person, how does the system know which one to display "automatically"?

                  There's no gCustomerId field described in that thread. It starts from a brand new record, so until you select a value, there's no way to link it to a record in the other table.

                  • 6. Re: Simple(?) lookup question

                    True. the gCustomerID field is the one I referred to in my own situation as shown above. I mixed up two situations, sorry for the confusion.

                    So in your example the situaion is that a person is selected from a drop down list, which doest not exactly reflect my situation.

                    What I try to achieve is that in Layout 1 a record with a customer is selected. While exiting layout 1 the global value gCustomerID is set. In layout 2 (for product selection) I like the chosen customer to be displayed to make sure that the products to be selected will be billed to that customer. To accomplish this, my approach was to display a name, which is in the same record as the global field gCustomerID.

                    I hope this clarifies my situation?

                    All the best,


                    • 7. Re: Simple(?) lookup question

                      Define gCustomerID in the table referred to in Layout 2. Then you can define a relationship from layout 2's table occurrence to a table occurrence of the table from Layout 1. Now you can place fields from layout 1's table on layout 2 and they will display the data from the current record in layout 1.

                      "Table occurrences" are the boxes found in Manage | Database | Relationships and are what are used to define relationships between tables. They are also what you are selecting when you select a name from a "show records from" drop down such as the one found in Layout setup... for each layout you create in your database and this is what I mean by "Layout 2's table occurrence".

                      If you want to learn more about table occurrences and how you can use them:  Tutorial: What are Table Occurrences?

                      • 8. Re: Simple(?) lookup question

                        Thanks for you reply Phil (and your patience Wink),

                        I think I understand the Table Occurence part.

                        I (already) got the relationship in place. What I notice now is the following what may cause things not to work: situation is, there are - amongst others - the tables 'Customers' and 'Products'. I got a one-to-many relationship set up from Customers to Products.

                        In the Products Layout (Layout 2), the value of customers::gCustomerID is shown. The other values, like customers::surname or customers::FirstName remain empty. Is it because I'm quering from the 'wrong' side of the one-to-many relationship?

                        • 9. Re: Simple(?) lookup question

                          Yes. That's why I suggested defining the global field in Products instead of customers.

                          You might also examine the invoicing starter solution or this simple Invoice demo file created by Comment to see some alternative approaches that use a portal to do this so that it all takes place on one layout: