9 Replies Latest reply on Mar 12, 2013 11:14 AM by weedonpaul

    Linking Tables by ID

    weedonpaul

      Title

      Linking Tables by ID

      Post

           I have the starter solution time Billing. I want to alter it so that when I click customer it drops down a list of my current customers which are stored in a seperate table. I have made the mistake of using names before instead of unique numbers, so I want to avoid that.

           I have linked "Customers::Customer_ID" with "LineItemsByBillingID::K_ID_Customer" in the manage tables relationship I have created a value list using the companies from the customer table.

           My question is this: how do I get it so that I select a company and it copys the ID across instead? 

        • 1. Re: Linking Tables by ID
          philmodjunk

               In manage | Value LIsts, you can select the "use values from a field" option.

               In the dialog that pops up with this option you can select values for the value list from two fields. The field you select for the left hand field (the primary field), will provide the values that are entered into a field when a value from the value list is selected. Select the Customers::Customer_ID field as your primary field. The secondary field will display data that will assist the user in selecting the correct value. Select a name field as the secondary field. You may need to define an auto-entered text field that combines first and last names to get a single field with the customer's full name for use as the secondary field in your value list.

               Such a method is the standard "beginner level" value list set up for linking records by ID. It encounters limitations when your value lists become longer. There are other, script supported methods that may be used that allow you to use an auto-complete enabled value list for selecting a name yet still linking your records by ID numbers.

          • 2. Re: Linking Tables by ID
            weedonpaul

                 If i do it that way i would have to put the customer id field into theh layout which i would like to avoid is there a way that i can look up the customer from the customer table and it put the id in the relevent field without it being on my layout?

                  

            • 3. Re: Linking Tables by ID
              philmodjunk

                   Why is that an issue?

                   You do not put the customer Id field from the customer table on your layout. You put the ID field for invoices on your layout.

                   This ID field has to be on the layout, but the ID number entered into it need not be visible to the user.

                   You can use a pop up menu with the value list set to only display values from the second field. The field then displays the name even though it stores the ID number.

                   You can also hide a drop down list formatted ID field behind the name field from the customer table. Just give the name field an opaque fill color and use behavior settings to deny access to the field when in browse mode.

                   You could pop up a window with the drop down list in that window but that seems like overkill in this situation.

              • 4. Re: Linking Tables by ID
                weedonpaul

                     Ok I have set the list up to display company names (secondary field), but it still showes ID number in the filed on the layout, what do I need to do to make it show company?

                      

                • 5. Re: Linking Tables by ID
                  philmodjunk

                       This ID field has to be on the layout, but the ID number entered into it need not be visible to the user.

                       You can use a pop up menu with the value list set to only display values from the second field. The field then displays the name even though it stores the ID number.

                       You can also hide a drop down list formatted ID field behind the name field from the customer table. Just give the name field an opaque fill color and use behavior settings to deny access to the field when in browse mode.

                  • 6. Re: Linking Tables by ID
                    weedonpaul

                         Thank you second one has worked. I have used a calculation function to fill in the customer field, but I have noticed that if i enter one value and after change it  the customer doesn't change

                    • 7. Re: Linking Tables by ID
                      weedonpaul

                           don't worry have sorted that problem

                           thanks for all you help

                      • 8. Re: Linking Tables by ID
                        philmodjunk

                             Just in case, you didn't figure this detail out:

                             You can remove the calculation field from your table as you do not need it. You can use the name field from your related customer table in its place.

                        • 9. Re: Linking Tables by ID
                          weedonpaul

                               That would have been simpler. some times I can't see the forrest through the trees.