lookup? replace contents? of variable in existing table

[cattail]

Hi, to no avail I have spent all day trying to figure out how with 2 already existing tables to get an already existing value from a field in one table to automatically fill into a field in a 2nd already existing table. Basically I don't want to manually fill in a unique # associated with a unique name 900 times and want FileMaker to find the empty variable, go to a 2nd table, look up the # assigned that variable name, then fill that # in.

I have a script that creates a new record in a 2nd table and autofills in territory name and territory number (unique serial # generated upon creation of new territory). Both name and # are in each table and linked with a relationship. When I create new records this works perfectly. A new record is created in the territory visit table (table 2) with the name and # carried over from territory table (table 1).

The problem is, I have an entire years worth of data with 900 records+ of data where the territories (table 1) are already named and numbered and the territory visits (table 2) are already named (they were previously entered by hand) but do not have the unique territory number generated in Table 1. Now I want to get the territory # from table 1 moved into the territory visit table (table 2) as this number is my common link between several tables. This is a many to one relationship where the territory # is unique in table 1 but can be repeated in table 2, i.e. you can only have one territory 22, but you can have infinite # of visits to territory 22.

How do I fill in the territory # field in the 2nd Table by having it look up the # of the same territory in Table 1? I have tried defining the field as a lookup value and pointing it to Table one territory # field and then running "relookup field contents", which seems like the easiest option but nothing happens.

Any help pointing me in the right direction would be greatly appreciated!

[bcooney]

You need a temporary relationship from Territory visits to Territories by name. Then you can use replace to populate the foreign Territory ID. After this is done, you can remove the relationship that is by name.

[cattail]

I have a relationship between territory name fields in both tables as well as territory number. I have attached a screen shot of my relationships. All together the database works well for entering new data I am just struggling with getting the old data incorporated.

NEWDB.pdf

[bcooney]

I have a relationship between territory name fields in both tables as well as territory number.

Actually, I see relationships that includes a match on both territory name and territory number. This is wrong.

Which table needs the Territory ID? I can't tell from your screen shot.

EDIT: I think you may need to rethink your data model. What are your primary entities? Territories, Nests, Visits, Locations...

Edited September 6, 2010 by Guest

[cattail]

Thanks so much! It is the territory_visit_dataentry table that needs to have the territory_number_de field filled via the territory_number field in the territory_summary table. I have tried doing lookups with both relationships turned on and off in all combinations and nothing has worked. I definitely learned a lot about FM today but still not this!

[bcooney]

You need a relationship between Territory_visit_dataentry and Territory_summary tables by Territory Name. Do not include the Number!

Once you have this relationship, you have allowed the Territory_visit records to see their corresponding matching record (by name) in Territory_summary.

Now go to the Territory Visit layout and AFTER SAVING A BACKUP, click in the Territory_NUMER field. Use the Replace field contents and replace it with the Territory Number using the new relationship (by name).

You should end up with all Territory Visit records having a TerritoryID if there is a match on name.

I still feel that your data model needs to be analyzed some more.

[cattail]

Oh my gosh, I may be a little bit in love with you right now! Thanks so much for that! I really appreciate you sticking with me on this. I will say that while yesterday was frustrating I did learn a lot while thrashing around!

Much good karma is coming your way.

C

[bcooney]

Glad you're all set (and I certainly can use some good karma, thanks).