Multiple Lookup Tables

[Heather Robinson]

I have a fairly basic database that I am working on. My question is as to whether or not you can have more than one lookup table in a single form.

I created a Customer table. Then I created a Billing table. Then I created a Work Order table. The Work Order table is the parent table with the Billing and Customer tables each as child tables to the Work Order. In the form, the lookup works for the Billing info but not the Customer info. Ugh!

Does it matter if I link both child tables to the work order by the same field? For example, each table has the "work order number" field. Or is it that I should just have the work order as the parent table and Customer as child, then Billing as grandchild? I am just grasping at straws and I apologize if this is a simple question.

Please let me know if I can provide more details. Thank you so much for your help!

Heather Robinson

[Keith Hubert]

RE: Multiple Lookup Tables

Heather,

The simple answer to your first question "can you have more than one lookup table in a single form", Yes. But that is not the way to think of it. A form is built onto a table or set, the lookup fields are designed in Field Rules. So what is done there will show up on the form.

With regard to how your app is constructed, have another very good look at Alphasports. See how the Invoice form works with all the lookups on it, also take note of the construction of the Set the Invoice form is built on.

I think you do need to have a good look at field rules to see how to make the input of date flow.

Thank you for posting your app, it does help to understand where you have got stuck.

RegardsKeith HubertAlpha Guild MemberLondon.KHDB Management Systems

[Jason Edwards]

RE: Multiple Lookup Tables

I am confused. Are you trying to do a lookup or are you trying to create a relationship. I am assuming both. You want to relate the customer information with the correct work order number. You also need it to lookup existing customers when creating a new work order. The way I would do it would be create the relationship between work order and customer via a customer number and not the work order number. Same thing for billing. You would want to having a billing number (I am assuming billing as in an insurance company or something) setup for each different billing entity and relate the work order to the billing information via the billing entity's number.

Creating the relationship does not automatically create the lookup. Lookups would be created in the field rules. For instance, on your work order screen you have a customer number field (this is the customer number field that is in the work orders table) and this would be the lookup field. If you have set your form up correctly based on the set, when you choose a value from the lookup table, the lookup field rule should fill in the rest of the customer data for you. Of course, this is done if you set up your lookup rule correctly.

Hope this help. If I am way off on what you are tying to do then please excuse me. I stayed up too late last night.

Jason