Problem with Look-ups

[Graham Wickens]

Scenario:
In my main Table "Logfile" I have two fields "Operator_Name" (52 Characters) and "Op_Unique" (8 Characters). In table "Operator" I have the same two fields plus "Country" (12 characters). The reason for the Op_Unique is that Logfile-> Operator values can be the same, but have different "Country" values.
Currently I use the "Operator" table as a look-up for values for Logfile->Operator_name and Logfile->op_unique. This works just fine, but means I have redundant data in the Logfile->Operator_name field. When I update operator->Operator_name, I also have to update the Logfile->Operator_name field
Question:
How do I go about creating a look-up for values in Logfile->Operator_name but only using the Logfile->op_unique field as the look-up value.(so that I no longer need the Logfile->Operator_name field)? ie when I update Operator->Operator_name the new value will be displayed in Logfile->Operator_name field.

[Tom Cone Jr]

RE: Problem with Look-ups

Graham,

When you retrieve the operator_name value from the lookup table and store it in your logfile table record that preserves the value of operator_name as it existed at the time the transaction is saved. This is often desirable, indeed essential.

If you now want the current value of operator_name to be shown whenever you view related records in the logfile table you will have to change your design. Instead of using lookups to retrieve operator_name values and storing them in the logfile, you could create a set linking the lookup table to your logfile table through some common key field. Then, instead of storing the operator_name in the logfile it would be available through the set. If the value of the operator_name changes, the current value will always be available. This is often desirable, indeed essential. HOWEVER, this approach prevents you from knowing what the value of the operator_name was at the time of the transaction, since it only shows the current value.

To recap. One would link related tables to have current informaiton always available. One would LOOKUP field values when one wants to record what those values were at the time the pending transaction is saved.

-- tom

[Graham Wickens]

RE: Problem with Look-ups

yep got all that. Presumably I cannot have both! if I use the Logfile->Operator_name on the set Form I can use the lookups to change the record OR I can use the Operator->Operator_name but then I am unable to use lookups.

[Tom Cone Jr]

RE: Problem with Look-ups

since the scenarios I described are mutually inconsistent why would you want both?

-- tom

[Graham Wickens]

RE: Problem with Look-ups

the Logfile->Operator_Name can change quite regularly, and its quicker to use the lookup facility to change it. But it would still be nice to be able to change all occurrences at once. Maybe I need to write a script that I can run against the Operator->Operator_Name that will update Logfile->Operator_Name on the saving of the updated record in Operator->Operator_name?

[Tom Cone Jr]

RE: Problem with Look-ups

Sounds as though you want the 'current' value of operator_name, regardless of what it might have been when the first transaction was recorded. This is similar to wanting the current fax phone number, instead of the original fax phone number when a member joined up, in my application. Instead of looking up this kind of data, I store it in a child table that I link to theprimary table through a transaction idnumber. Then, if the fax number changes I just change it in one place... i.e. the child table. No update through the primary table is required. Maybe something like this would be of use to you.

-- tom