4 Replies Latest reply on Sep 4, 2010 12:18 PM by LaRetta_1

    How to link tables and fields?



      How to link tables and fields?


      Hi all. I have a customer's database with multiple fields. I want to create a payment receipt with receipt number (that's being increased with each new record). So I created new table and copied all fields I want to be displayed on the receipt from the main table. Then i created relationship between same records in these two different tables. But unfortunately every time when I create new receipt/records on the receipt table It doesn't display the related fields, such as name, address and so on, the only thing it displays is the new receipt number. How can I import data from main table (current record) into "receipt" table. I've even tried "Set Field" to equal value from the main table but nothing is coming through. Thanks in advance. Any help would be appreciated.

        • 1. Re: How to link tables and fields?

          Your main table needs a primary key (number, auto-enter serial ... see field Options, auto-enter tab).  Let's call it MainID (it should be named to match your table but I don't know what it is named).  Then create a value list (File > Manage > Value Lists) ... 'New', Use Values from Field .  Select your Main table from the upper left pop-up and then select this primary key in the left pane.  Then select the name (or descriptor) in the right pane.  Below, specify 'use all values' and 'show values only from second field.'

          Then your Receipts table needs a MainID (number) but do NOT set up auto-enter on it.  Place it on your Receipts layout and attach your new value list to it and set it up as a pop-up menu.  In your graph, join as:

          Main::MainID = Receipts::MainID

          Now place your Main fields directly on your receipts layout (no need to duplicate information in both places unless needed for history purposes).  Now select from the pop-up and all main data will appear on your receipts layout.

          If you truly need to plant SOME of the data into the receipt then you can do so by using auto-enter calculation or Lookup (see Options auto-enter tab).  But duplicating data isn't usually necessary and defeats the purpose of relational structuring.

          • 2. Re: How to link tables and fields?

            Hello LaRetta and thank you for your reply, but I think I didin't explain myself clearly.

            In the "Main Table" I have "Main Layout" which has "Name, Address and other information" fields.

            When customer pays I run script that prints out the receipt for the customer with all the information on it plus the set fee.

            I like it the way it is, except for one thing - I can't give the receipt the reference number (let's say "Receipt-2010-001" and for the next customer "Receipt-2010-002").

            So I thought if I were to create a new "Table" and each time when i process payment in the "Main Table" it will automatically go to the "Receipt Table", create new receipt number "Receipt-2010-003 etc" and look up the values (Name, Address and other information) from the current record being displayed.

            My understanding is if I were to put "Auto-Create Number" in the "Main Table"- it will only create new number if I create new record in the "Main Table", which I don't need. I want to replicate information from the "Main Table" into "Receipt Table", while creating new record with a new counting number.

            • 3. Re: How to link tables and fields?

              Hi again,

              I kinda found the way around, the only problem I have now is with the script.

              I am trying to write a script so when the new record is created (in the "Receipt Tabel") it automatically puts in the "First Name" and "Last Name" from the "Main Table" into "Receipts Table". I fixed the relationships between two tables, so if I were to enter First and Last name manually it shows the information on the "Receipt Table", but how can I enter this information (First and Last Name) from the "Main Table" not manually. "Set Field" thing doesn't seem to work.

              What should I use (script function wise) to enter the information from the current/displayed record in the "Main Table" into "Receipt Table"?


              • 4. Re: How to link tables and fields?

                I provided an answer.  Yes, you DO need unique number in main table which is your Customers table.  Each customer should have a primary key.  Then when you create a receipt, the customer ID is inserted into the receipt and the RELATIONSHIP provides the customer details to the receipt automatically.

                If you are on your customer layout, you can automatically create your receipt via script, something like:

                Set Variable [ $customer ; CustomerID ]
                Go To Layout [ layout based upon Receipts ]
                New Record/Request
                Set Field [ Receipts::CustomerID ; $customer ]

                ... as I suggested, if you insert the customerID into your receipt (either via script or by selecting from pop-up menu on CustomerID in Receipts, the customer information will appear because the fields you place on your receipt will BE the customer fields.  Try it.  You might like it.  It is standard relational design and something you need to learn anyway. :^)

                You asked how you link tables and fields ... what I explained is how to do it.