Linking a Set Using More Than One Linking Field

By Barry Rochford

Stop for a second and think how you usually design an Input Form for a Set having a Child Table. You probably drop & drag an embedded Browse Object for the Child Table. You dress up the form, add some buttons, save it and then start entering some Test Data. Alpha Five automatically places the linking field into each new Child Record.

Now, assume the Child Record has to be linked to the Parent by two fields, let's say "Invoice_num" and "Project_Id". Now you are back to Form Design. Your first hint that something has gone awry appears when you drag and drop an embedded Browse Object for the Child onto your Form. What's this! Both the "Invoice_Num" and the "Project_Id" fields appear on the Browse! Well, that's certainly strange isn't it? With that sinking feeling and your stomach acids churning about, you page through your manual to the explanation of "Linking Fields" – it even shows an example using two fields. Wait just a second, what's that NOTE say? – "If you use more than single field in the Linking Fields boxes, you cannot use referential integrity." The bottom line is that Alpha Five is not going to automatically enter either of the Linking Fields into your Child Records.

Figure 1 – DW_Header Record Layout

Figure 2 – DW_Invitms Record Layout

Figure 3 – DW_Inv_set Set Design

Figure 4 – Entry Form

Well, all is not lost – there are 3 ways that I know of to handle this situation:

1. You could make the Input Person enter the "Invoice_num" & the "Project_Id" field for each Child Record. (Well, you could but that's just not acceptable to anyone.)
2. You could write a short Xbasic script and save the two Linking Fields yourself. You could put this Script in the CanSave record event, check to see if you are in the correct Form and save the fields. But, let's do it an easier, more straightforward way.
3. Let's fool Alpha Five into thinking that there's only one linking field. We won't let it know that we are linking the table with two fields. Go to the Control Panel, go to the first Table, DW_Header, Right-Click, then Edit Structure. We will add another field to the end of the Table. Let's call it "Link". We will make it a Character type with a length of 10. Each of our "real" linking fields are Character type with a length of 5 each so our new "Link" field must be 10. Save the change you made. Close the Restructure Window and now Right-click on DW_Header again, then select Field Rules. Go to the "Link" field rule and on the Field Type Tab, click "Calculated", then enter the following expression:

Save the field rules and now back at the Control Panel, Right-click on DW_Invitms, again Edit structure, add a new field to the end of the table, again call it "Link" Character type with a length of 10. Save the change, close the Restructure Window and Right-Click on DW_Invitems again and select Edit Field Rules. Now we want to take care of our two "real" Linking Fields, so select the "Invoice_num" Field. On the Field Type Tab, click "Calculated", then enter the following expression:

Now do the same thing for the "Project_Id" field, but use this expression:

Save the Field Rules and then let's go back to our Set. Right-click on DW_Inv_Set, select Edit Set. Highlight Dw_Invitms, click on Edit Link. Change the original Linking Field to "Link". Save the changes and now go to the Entry Form in design mode. The two Fields that we fixed need not display on the Browse so we will delete them. Right-click on the browse, move your mouse to the first field to be deleted, click so it is selected, press Delete. Do the same for the second field, save the Form and we are ready to test the validity of our changes.

Figure 5 – Entry Form after Changes

If you need to Link using a Character and a Date field, no problem, use the CDATE() Function in your expression. Just don't forget to convert it back to a Date type field in your Field Rule. Same for using a Numeric type field - use the STR() Function.

Barry Rochford is an Alpha Developer located near Newtown, PA. He can be reached at brochford@enter.net

6/10/99

Don't forget, we need your feedback to make this site better!

Return to home