Displaying Info From One Table On Another

[coy886]

Hey Guys,

I have what I think is a simple question... I'm creating a database that will allow me to create work orders. Each work order will display a customers information. I currently have two tables.

1. WORK ORDERS

2. CUSTOMERS

My question is this... How do I display the fields from "CUSTOMERS" on the "WORK ORDERS" layout? I would ultimately like to be able to type in a customers name on a work order and have all of their information show up. 

Do I need to create the same "Customer" fields in the WORK ORDERS table? Or is there another way to display them?

[LaRetta]

Assuming there will only be one customer on a single work order, what you describe is known as a one-to-many (one Customer AKA 'parent' to many Work Orders AKA 'child').

You only need the fields (other than the ContactID) if you need to preserve their value for history/audit purposes.  For example, if you enter the shipping address but later the Customer changes their address, you want the old Work Order to display the Customer's address at the time of the shipment.  So, with Invoices, Work Orders, Contracts and such, it many-times makes sense to duplicate the data into your 'child' (Work Orders) table.

You place the parent's unique key (CustomerID) in the child's table.  Then, from perspective of Work Order, you select the CustomerID in the Work Orders::CustomerID field using an attached value list which is based upon data, left pane is CustomerID, right pane is customer name, all values, and below, display values from second field.  Use a popup control.  If you use a drop-down control then it will only display the ID after User leaves the field and you will be required to place the Customers::name field next to the ID for display.

The relationship will be:  Customers::customerID = Work Orders::CustomerID

If you need to retain the data, create duplicate fields in Work Order and use either auto-enter or Lookup to have the values in Work Order fill in when you create a new Work Order.  If you do not need to insert the data static, you can always simply display the 'parent' values on your child layout.  After creating the relationship, simply place the fields from Customers directly onto your Work Orders layout, select a Customer ID from the value list popup and watch your values fill in or appear on your layout from Customers.

There are various training videos and sample files around showing how to create a simple 1:n (one-to-many) relationship using IDs.  Do not be tempted to use names for these KEY relationships.  You want to use a meaningless ID because otherwise, if you change a name and it is used for a relationship, you can break your relationships and broken relationships are never a good thing.  ;-)

[coy886]

Thank you for the response... Say I wanted to create a new work order and link it to a customer by adding their customer info at the top, would I do this with a portal? Ultimately I would like to create a work order and have the first field be a customer name field. Once I start typing the customer name I would like it to start guessing what customer it is and once I click return, I would like it to auto fill all of that customers other info on the work order... Phone, email, address, etc... This is hurting my head!

[LaRetta]

I explained what you need to do to accomplish your request.  If you begin working through it and trying my suggestion, you can then post a question if you get stuck.  

Here is an example file by Comment which shows a basic join relationship between Invoices and Contacts illustrating the structure needed and as I have explained in my prior post:  http://fmforums.com/topic/63425-auto-fill-one-field-with-text-from-two-fields/?do=findComment&comment=300150 and download the InvoicesDemo.fp7 file.

As for selecting the customer, you can use a value list or a portal for filtering down your customer selection.  Once you get the structure in place and understand my prior post, you can decide whether to select customers via a portal instead of using a value list.  Usually, we use a portal if the value list will hold more than a few dozen entries.  But you will TRULY want to understand how this join relationship is working AND understand how to create value lists  so spending some time in the trenches absorbing these principles will be highly beneficial at this point. 

Edited January 15, 2017 by LaRetta