Getting More Than One Parent Field into a Child Table

By Dan Blank

 

When you create a set between two tables you have a common linking field. That linking field is in both tables (the parent and the child). However, sometimes it is useful for the data in the parent table to also appear in the child table. For example when you create a set for an invoice you would typically link the set by an invoice number, and the invoice number would appear in both the parent and child tables. What if you want the child table to also contain a customer id number or a work order number? This information would be in the header of the invoice, so do you really want to type it again for each child?

 

Through the use of field rules you can let Alpha Five automatically do the data entry. Let's create two tables, then create a set from the tables for an invoice. The first table is the Invoice Header table, Figure 1. The second table is the Invoice Footer table, Figure 2. The set is created from the two tables using a one to many link, Figure 3.

 

 

Figure 1: Invoice Header table (invc_hdr).

 

 

Figure 2 Invoice Footer (invc_ftr).

 

 

Figure 3 Set created for the invoice.

 

Next go to Field Rules for the Invoice Footer table. Find the Cust_Id field, make it a calculated field type, and put: LOOKUPC("C",INVOICE_NO,"Cust_id","invc_hdr","Invoice_No") into the Calculated Field expression. For Work_Ord you would do the same thing only the expression is: LOOKUPC("C",INVOICE_NO,"Work_ord","invc_hdr","Invoice_No")

For Date use the LookupD function.

LOOKUPD("C",INVOICE_NO,"Date","invc_hdr","Invoice_No")

 

Save your changes, and close Field Rules.

 

From the Invoice Set we next create an invoice form, Figure 4. The invoice contains fields from the parent and a browse contains fields for the child. If you go to the browse and look to the right you will see the Customer ID, the Work Order number, and the date. Normally the client does not see these fields. These are shown only for illustration purposes.

 

 

 

Figure 4 Invoice

 

Okay, so now that you have read this far, and understand what is going on, you may be asking yourself, So What?

 

Well, if you create a new set with your Customer table as the parent and use the Invoice Footer table as the child you can see a history of all the items invoiced to that customer Figure 5.

 

The same thing can be done with Work Orders, Figure 6.

 

Figure 5 Customer History

 

Figure 6 Work Order Statuses

 

A demo is included (HERE) which will help further illustrate the workings for this example. You must have Alpha Five =>v4, or v5 or v6 to use this demo. Enter some records and play with the demo.

 

Back To Tips, Guidelines, Articles