LookUp Problem...

[stevie.at]

I thought all the way to the metro about it, but couldn't find a real solution...so I got a specific problem with some Look Ups...Here's the scenario:

I got 2 files, one for Contacts, another one for Organizations. In my Contacts database if a user enters a new record and links it to an organization, all the designated information will be auto-filled as LookUps (Address...). In case of more specific data the user of course can change this manually. So far so good. If an organizations' data will be modified of some reason a script can handle the Re-Look Up of all these fields - BUT - I just want to Re-look Up all these records, that had not been modified by any user since.

Here's where the problem starts, because I need some kind of back-up fields in the organizations database that still hold the original data (after any modification) to compare it to the current data in the Contacts' records...and these can't simply be calc-fields of the data-entry fields...

What's the right (user-friendly) way???

[Brian C]

- BUT - I just want to Re-look Up all these records, that had not been modified by any user since.

Here's where the problem starts, because I need some kind of back-up fields in the organizations database that still hold the original data (after any modification) to compare it to the current data in the Contacts' records...and these can't simply be calc-fields of the data-entry fields...

Could you explain your reference to storing the history of what the organization fields used to contain?

I will try to help by maybe pointing you in a helpful direction...

Use "flag" fields on the contact record to assist in determining if a record needs to be updated.

This could be made more flexible if you also add a field to store the user's modification date for the organization data as well.

Example:

Create a new field to be a "flag" which would determine if a user has changed any of the fields from the default (related) values stored in the Organizations table.

For the sake of this example I will call this new "flag" field: "OrgUserModFlag".

You can also create a field to store the date for when/if the organization was modified by the user. I suppose OrgUserModDate is as good a name as any.

OrgUserMod will be a default autoenter value = "0"

0 = Not Modified By The User

1 = Modified By The User

OrgUserModDate will be empty unless the Organization was modified by the user, in which case it will store the date of the last user modification.

(easy way)

A script can trigger prior to the user being allowed to edit the data

(hard way)

A script should trigger after the editing is done. Using the relationship to the Organizations table, a comparison is done to determine if any of the fields have changed from the stored default.

Result:

Now to update only selected records you can now do a search on fields that are flagged as edited by the user after a specific date. You can also do search on records never edited by users and relookup only that found set.

[stevie.at]

Well, thanks!

It's definitively a better idea to start at the Contatc's side than directly in Organizations!

I was too focused on that way...

Another idea that occurs to me (also at another point in my database) is if there's an easy way to give some kind of modification history to the users, e.g. just provide a read-only list with data that has been entered into specifc fields before modification.