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:
Fields: Buyer Name, Qty, Amount, Country
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.