Referential Integrity, link on multiple fields

[BruceA5V4]

In most of the sets I have now, I have to not have any referential integrity, because Alpha V insists on having only one linked field for referential integrity. Is there anyway around this? What happens to me if I don't have referential integrity included is I have to duplicate the data entry in 2 or more fields between linked tables in the set. Its always been a drag, but I've never known any other way to do what I need to do.

Any ideas?

Thanks,


Bruce Roberson

[MoGrace]

Re: Referential Integrity, link on multiple fields

One way might be to create a link field that is a combination of the two fields you want to pass to the child.

Ex: Create a field called KEYLINK and set its value to
ut(CUSTNO)+"-"+ut(INVNO) as a calculated field. Add that same field to the child table as a user entered type and link your set on that field. In the child table set the calculated value for the CUSTNO field to word(keylink,1) and in the INVNO field to word(keylink,-1).

Note: You may have to run an Update operation before setting the field rule for KEYLINK to calculated to get the field populated for your existing records in the parent table.

[BruceA5V4]

Re: Referential Integrity, link on multiple fields

Ex: Create a field called KEYLINK and set its value to
ut(CUSTNO)+"-"+ut(INVNO) as a calculated field

These calculated fields work, and I did the update before putting in the field rules


In the child table set the calculated value for the CUSTNO field to word(keylink,1) and in the INVNO field to word(keylink,-1).

I guess this would of worked, but field 1 could and often did have 2 or more parts, and field 2 always had 2 characters, so I had to do the following instead:

Child Table, Field 1 field rules = LEFT(KEYLINK,LEN(TRIM(KEYLINK))-3)
Not sure why -3 was needed instead of -2 but that is how it works now.

Child Table, Field 2 field rules = RIGHT(TRIM(KEYLINK),2)


I would of used the "words" function if I could of made it do what I wanted, so I wasn't sure how to make it pick more than one word with the function "word"

[MoGrace]

Re: Referential Integrity, link on multiple fields

Bruce,
There are many variations for extracting the string you need - I just put up a simple one. So is this going to work for you? The AT() function could also be used since we added the '-' character to separate the fields joined together.

[Stan Mathews]

Re: Referential Integrity, link on multiple fields

I would of used the "words" function if I could of made it do what I wanted, so I wasn't sure how to make it pick more than one word with the function "word"

Output_String as C = WORD( Input_String as C, Word_Number as N [, Word_Delimiter as C [, Word_Count as N ] ])

? word("now is the time", 2, " ", 3)

= "is the time"

[Tom Cone Jr]

Re: Referential Integrity, link on multiple fields

Bruce, I'm curious why you're building compound keys to link your tables together? In many apps it's not necessary for the user to ever see the link field values. For my own apps I try hard to keep things simple and avoid using a single field for multiple purposes. -- tom

[BruceA5V4]

Re: Referential Integrity, link on multiple fields

Tom:

I have a three tiered set approach in many of my sets so I can have an embedded browse table to view child records, and then a tab for history records of oil and gas severance tax where those records relate to the first child in a one to many relationship.

For example, the parent table is a property name, number, and product code, be it oil or gas. Then, the first child that has a one to many relationship set up needs to link to both the property and the product code of the parent, thus the linking of two fields in this case with the field keylink. The reason I want to have separate parent records for each product within a property name is the third level down, which is a child to the first child (a grandchild if you will). The linking between the child and the grandchild has four fields.

I have a property form setup where I setup both the parent and child at the same time, and want it to cascade adds and deletes, thus the need for referential integrity.

Sorry for the short lesson in oil and gas severance in taxation etc but the child will have a property name, then it can have multiple records with the same property name in which it has one or more PUNs and suffixes, which are two additional fields in the child, and then the product code, that links both to the parent as well as grandchild. For the second link at this point, I am not worrying about the keylink concept because the grandchild is a history of payments that are imported each month from Excel, rather than entered manually.

After all that response, am I missing something in the way I arranged my sets all these years, given the level of detail I want to view, print and query data on?

Bruce

[Tom Cone Jr]

Re: Referential Integrity, link on multiple fields

Bruce, if it's working for you then don't change it. I've seen situations over the years where folks design their link fields so that the field value itself is meaningful to the user. These are put together from other fields or pieces of other fields so that a unique value is ultimately produced. This of course is required. The link field value must uniquely identify the linked child record.

Where the design will fail however is if the user is permitted to edit some of the subsidiary field values after the new record is entered. Say, for example, in your case, if the user changes a product code in a child table. I'd be worried that this would break the link between that record and the original parent table record.

But, as I say, if it's working don't change it!

-- tom

[MoGrace]

Re: Referential Integrity, link on multiple fields

Tom,
He was looking for a way to pass the needed values to the other fields used as links to other tables. I suppose it would require that these child records only be created in this set. Since in the child the fields are calculated values, they could not be changed unless the parent key fields are changed which in turn updates the link field and the calc fields. I suppose he would have to be careful not to show the linking field to the user in the child, since it could be changed.

[DaveM]

Re: Referential Integrity, link on multiple fields

I would love to see the linking schematic and details in set form for this one. Thought I was the only one did wierd things.

Like Tom said, if it works, dont change it.

Dave

[MoGrace]

Re: Referential Integrity, link on multiple fields

I would love to see the linking schematic and details in set form for this one. Thought I was the only one did wierd things.

Like Tom said, if it works, dont change it.

Dave

Beauty is in the eye of the beholder...

[goldberg2727]

Re: Referential Integrity, link on multiple fields

I have the same issue, I am going to give this a try. I have a three tear set due to an extremely awkward item rates scheme.

The first table is a workorder table and contains
WO_Num
Job_ID
Customer_ID
Contact_ID
Address
Date_start
Date_end

The second table is a worksheet and has a one to many and multiple field link.
Wrksht_Num
Job_ID
WO_Num (workorder->wo_num)
Cust_ID (workorder->cust_id)
Cont_ID (not linked because it may be different than workorder)
Time_start
Time_end
Bill_Rate_id
Pay_rate_id
Total_Billed
Total_Cost
Total_Paid

The third tier table is a worksheet_items table with a one to many and multiple field link to the worksheet table
WS_Item_ID
Item_Code (*Field Rule Lookup Table; as described below)
Description
Units
Wrksht_ID(worksheet->wrksht_id)
Cust_ID (worksheet->cust_id)
Pay_rate_ID (worksheet->payrateid)
Bill_rate_id (worksheet->billrateid)
Bill_Rate
Pay_rate

*The worksheet_item->item_code field has a lookup table field rule to the Item_Rates table. The Item_rates table lookup field rule contains a filter (item_rates->Cust_ID = worksheet_items->Cust_id). The Item_rates table contains the following fields

Rate_ID
Cust_ID
Item_Code (fills the worksheet_items->items code field)
Description (fills the worksheet_items->description field)
Units (fills the worksheet_items->Units field)
Bill_Rate1 (fills the worksheet_items->Bill_rate field if Bill_rate_ID = bill1)
Bill_Rate2 (fills the worksheet_items->Bill_rate field if Bill_rate_ID = bill2)
Pay_rate1 (fills the worksheet_items->Pay_rate field if Pay_rate_ID = pay1)
Pay_Rate2 (fills the worksheet_items->Pay_rate field if Pay_rate_ID = pay2)
Pay_rate3 (fills the worksheet_items->Pay_rate field if Pay_rate_ID = pay3)
Sub_Rate1 (fills the worksheet_items->Pay_rate field if Pay_rate_ID = pay4)
Sub_Rate2 (fills the worksheet_items->Pay_rate field if Pay_rate_ID = pay5)
Sub_Rate3




The reason for all this is because each customer has it's own unique set of bill rates, pay rates, and sub rates for each unique item_code. The customer also wants the option to change the bill_rate or pay_rate for each item on each worksheet because of variables such as location of item being installed, night job, weekend job, bad weather, etc...

I will let you guys know if the method posted above works in this scenerio but this is the situation where i see no other way to meet the clients needs

[Tom Cone Jr]

Re: Referential Integrity, link on multiple fields

Lee, another option is to run the set without referential integrity enabled, right? You control edits to the parent table. If a change or delete occurs that should be cascaded down to related children you script that to occur behind the scenes. -- tom

[goldberg2727]

Re: Referential Integrity, link on multiple fields

Tom, that seems like a lot of scripting if i am linking a lot of fields. What would be the advantage to scripting those changes? Do you think it would be easier?