Lookups vs. Scripted Data Entry

[davidnickerson]

There must be an elegant way to achieve the following:

I need data to update in fields A, B, C (table A) based on changes made to field Z (table Z).

Fields A, B, C must be editable fields, not a calcs.

I can achieve this two ways for now, neither of which I like:

1. Set the target fields (A-C) as lookups, then set an OnObjectModify trigger on field Z that runs a re-lookup script. I don't like this particularly, because I generally avoid lookup fields when I can, and the re-lookup script step is performed on all records in the found set. I just need the current record to be updated.

2. Script the process completely. OnObjectModiry trigger on field Z goes through and sets the corresponding field values for A-C.

Isn't there a cleaner, simpler way to do this?

In other words, create an environment for a field to re-calculate auto-entered data, but still be editable?

[davidnickerson]

(NOTE: I posted this in Define Fields as well...not where it best belongs)

There must be an elegant way to achieve the following:

I need data to update in fields A, B, C (table A) based on changes made to field Z (table Z).

Fields A, B, C must be editable fields, not a calcs.

I can achieve this two ways for now, neither of which I like:

1. Set the target fields (A-C) as lookups, then set an OnObjectModify trigger on field Z that runs a re-lookup script. I don't like this particularly, because I generally avoid lookup fields when I can, and the re-lookup script step is performed on all records in the found set. I just need the current record to be updated.

2. Script the process completely. OnObjectModiry trigger on field Z goes through and sets the corresponding field values for A-C.

Isn't there a cleaner, simpler way to do this?

In other words, create an environment for a field to re-calculate auto-entered data, but still be editable?

[comment]

I need data to update in fields A, B, C (table A) based on changes made to field Z (table Z).

Can you explain why you need this? In general, an arrangement like this violates the rules of normalization - so you want to make sure you have a good reason for it.

It's also not quite clear when an update should be triggered: suppose the user has already edited the values to their liking; should a change in the related value reset these values and destroy the changes made by the user? And what about other A records that are also related to the modified Z record?

[comment]

Please do not double-post:

http://fmforums.com/forum/topic/86154-lookup-fields-vs-scripted-data-entry/

If you posted in the "wrong" section, the moderators will take care of it.

[Lee Smith]

(NOTE: I posted this in Define Fields as well...not where it best belongs)

I merged your two topics. comment has explained the procedures for you, if you have any questions about any of this, just send me a Private Message.

Lee

[davidnickerson]

Can you explain why you need this? In general, an arrangement like this violates the rules of normalization - so you want to make sure you have a good reason for it.

Sorry for the delay here.

I'll explain the scenario in more detail:

The database in question contains records (table A) for testing the specifications of a laser product. Each record is essentially one test run of various parameters.

After a new record is created, the user selects a product model (table Z) from a dropdown list. This test run will be for that product. The dropdown is a combined value list of both the model name/number, AND the foreign key of that table. Thus the relationship between A and Z is established when the user selects the model.

However, if the user makes a mistake a selects the "wrong" model, he can change the entry in the field.

Now, sometime later in the testing process are 3 fields which may or may not need to be filled in, based on which model was selected.

If the model selected does not have a Chiller, for example, "field A", will auto-enter the text "N/A". If it does have a Chiller, the field will be blank so that the user can fill in Chiller testing results. This condition is similar for fields B and C.

In addition, all fields in this section (one of several tabs) are required for completion of the section. When the user has completed each section, a script runs to check that the required fields have data in them.

Thus, "N/A" in the field meets the requirement. This way, the user doesn't have to manually enter data for fields that have no data for that particular laser model.

So, in order to allow for the user to change models, AND have the data ( "N/A" ) appear in fields A, B, C, AND be able to edit A, B, C I used option #1 from my first post. I'm merely asking if there's a more elegant solution.

Conditionally formatted text won't do: it needs to be data in the field to meet the required fields test and not force the user to manually enter "N/A".

Conditional scripting, i.e., having the required fields script check different groups of fields depending on the model, would be far more time consuming. A separate branch of the script, listing each and every field needed, would have to be written for each model.

Thanks again.

[comment]

Let me suggest an entirely different approach to the problem:

This way, when a new test record is created and the parent product selected, only the applicable test items will be presented - and the user can enter the corresponding result for each one.

The other option is to set the three fields as a lookup. There is no need to trigger a re-lookup - selecting another ProductID will automatically perform a re-lookup for the current record only.

I generally avoid lookup fields when I can

I can see no reason for robbing yourself of the advantages they offer.

[davidnickerson]

OK, thanks. There's clearly an issue with one of the relationships, then. The three fields in question ARE lookups, but are not updating when the ID changes. It's puzzling then why a re-lookup script step works at all, which it does in this case. More investigation...