Scripting a Relation

[bAttitude]

Hello - I am relatively new to FM7, although a programmer by trade...

I spoke w/ someone knowledgable in DB ways, and he pointed out in a sample I was building, that is is generally not good practice to create relationships based on Data in use, but rather use a generated index number. That way the data can change later, and the linked records are not affected.

In my sample, I have a table of 3600 NASDAQ stock symbols, and another table which is a record per transaction total for a month, by a certain company, for a certain stock as listed by Market_Symbol. I was linking by Market_Symbol and doing lookups, sums, etc.

It was suggested that I add a Symbol_ID in the NASDAQ Symbols table, then link to the transactions table by that. ok. I use the 'replace field contents' script step, with the appropriate options into a new auto-enter seriel Symbol_ID field in the Nasdaw Symbols table. works fine as a one time initialer.

Now I want to use the Symbol_ID in the transaction records DB to do the lookup into the mktSymbols table, not the Market_Symbol, as suggested, yes ??

So I guess I need to one-time init the table of transactions to contain not only the Market_Symbol, but a new Symbol_ID, retrieved from the NASDAQ Symbols Table where I just assigned them. So I write a script that does the following:

go to layout Transactions (TR) #in FM7, I assume that by going to a layout that has a particular table as the primary table, I am now "in" that table as default

set field gText to field Symbol_ID #a global I defined

enter find mode

set field Symbol_ID to gText

perform find #I now have selected all transactions with this symbol

goto layout NasdaqSymbols

enter find mode

set field mktSymbol to gText

perform find

set field gNumber to symbol_ID #capture the index I want to copy back to TR

goto layout TR

set field symbol_ID to gText # was empty, I'm trying to set the new index

replace field contents #all occurances of a transaction with that mktSymbol will now have the right symbolID

--

But, I would have to loop through all the transactions, doing the same steps, until all transations with unique mktSymbols are 'stuffed' with their new, correct symbolIDs. How do I go from one found set to the next? What are the exit conditions? Is this in fact what needs to be done ??

This is an academic exercise in order to learn databases. The Transactions table has 176000 records in it, with about 3000 unique mktSymbols, of the 3600 possible represented. Insight and pointers appreciated.

-Brian