Lookups in detail when there is a change in Master

[faheemhameed]

Hi experts,

I have a master database called Building and a detail db called Units.

There is a relationship from Unit to the Building using a Unique Building_Code. I have a Building_Name_Temp field in the Unit record which at the moment looks up the Building_Name from the Building database. You may wonder why I could not just show the Building_Name as a related field in the Unit database. I need the Building_Name in the Unit database locally for some other purposes.

It looks up the Building_Name properly when I create a new Unit record for a Building.

My problem is when there is a change in the Building_Name in the Building database I also want to update the Building_Name_Temp field in the Unit database. I have absolutely no idea how to do this.

Your help will be greatly appreciated.

Thanks,

Hameed

[Kurt Knippel]

Personally I only use lookups as a LAST resort or to capture information from a distinct moment in time (such as for invoicing). I almost always use related fields, whether they be a genuine calculation field in the database or simple a field through a relationship.

In your specific case, what I would recommend is that you lookup the CompanyID, and then just use that ID, which should NEVER change (unless the company associated with the record changes), to relate to all the other information.

See my article on IDs for more information on setting up these unique IDs: http://www.fmforums.com/threads/showflat.php?Cat=&Board=UBB8&Number=1472&page=1&view=collapsed&sb=5&o=&fpart=1

[Kundinger]

Hi Hameed,

I had a similar situation where a client had 'mobile' users with notebooks. They wanted to take their FileMaker data with them and create new data while away from the office.

The way I handled it was to create locked 'read-only' data files on the notebooks. These are data-only 'duplicates' of the master data files on the server. I did this by creating a set of scripts that 'download' certain 'read-only' files to the notebooks. This allows the users to work with

[faheemhameed]

Thanks Kurt and Bob,

Kurt,

I am sorry I do not understand clearly how your Auto entered RecordID solves my problem. I am not sure I have explained my problem clearly. Let me try to explain clearly:

File1 (Master)

--------------

Building:

--------

Building_Code

Building_Name (looked up in the unit db)

Field3 ( may also looked up in the unit db)

File2 (Detail)

--------------

Units:

------

Unit_Code

Building_Code (Used in the relation name "BCode" Units::Building_Code=Buiding::Building_Code

Building_Name_Temp (Lookup based on BCode relation)

The flow

1> A Building record is created and automatically assigned a unique code (Building_Code)

2> Building_Name field is entered and other fields are populated

3> A Unit record is created after choosing a Building from a list of Buildings

4> A unique Unit_Code is assinged and the Building_Code of the Building the User chose

is set to the Building_Code of the Unit db. Building_Name_Temp is automatically

looked up with the Building_Name field of the Building db (through BCode relation)

For ex: A Building record created and the Name of the Building is set as "ABC Centre"

After that a Unit is created for this Building and the Building name is looked up

automatically into the Building_Name_Temp field in the Unit db so that it says

"ABC Centre".

Now the user go to the Building db and changes the name of Building from

"ABC Centre" to "XYZ Centre". I want to reflect this change in the Unit db's

Building_Name_Temp field

I can use the oAzium events for the Building_Name field that invokes a script

which will loop through all the Unit records associated with that Building

and refreshes the value in the Building_Name_Temp field. But it seems ugly to me

and also I need to buy the plugin for just to solve this problem.

I am looking for a better and smart way to accomplish this.

Thanks for all of your help

Hameed