AnsweredAssumed Answered

Auto-creation of related records

Question asked by rgriff on Apr 27, 2010
Latest reply on Apr 28, 2010 by LaRetta_1


Auto-creation of related records


I'm having some trouble understanding how to make this work the way I need it to work (FMPro 11) ... or if it can work this way. Here's a subset of my tables and some of their fields:


Table: Sales

Fields: Buyer Name, Qty, Amount, Country


Table: Countries

Fields: Country [related to Sales:Country], Count (Sales:Country)


The idea is that the Countries table shows a count of buyers by country...and it does. The problem is that this table isn't updated when I import new data. If the new data has a new country, it doesn't create the required Countries:Country entry, so that it can be summed. As a result, I have to review the imported data for any new countries, then manually create their records in Countries.


I thought the "allow creation of records in related table" would do this for me, but further reading shows that was a bad assumption. So I'm guessing I need to run some sort of a script or something, post-import? In my head, it seems simple, but time-consuming: loop through each record in Sales, doing a find on each record's country against the Countries table. If it comes up with no match, create the record in Countries.

But there must be a better way, right? All ideas appreciated.