Personnally, I would keep all the data in one table. You can perform finds that pull up all the data for a given year instead of having to switch from table to table.
To do what you request, check out the Set Field By Name script step and GetField function in filemaker's help system. They'll allow you to reference tables and fields via a calculated value.
Thanks for the prompt response.
Yes, I realize that keeping all the data in one table, and including account numbers as part of that table, would eliminate some of these problems, but I can't get my head around what happens to 15 years of historical data from 10 different accounts if something goes wrong! Also, the different accounts are for different people - I'm uncomfortable mixing them, even though we are related. And finally, I'm concerned about how long searches would take on one gigantic table. However, I do see that it would simplify some things for implementation.
I'll try the Set Field By Name and Get Field, and see what trouble I can get into with them!
"but I can't get my head around what happens to 15 years of historical data from 10 different accounts if something goes wrong!"
With any database you should make frequent backups.
"Also, the different accounts are for different people - I'm uncomfortable mixing them, even though we are related."
As long as each record is clearly identified there shouldn't be any trouble here. You may be uncomfortable with this, but it will work and it simplifies a lot of reporting issues as well as the script you describe.
"I'm concerned about how long searches would take on one gigantic table."
That's a valid concern--espcially when searching a file hosted over the network and/or when unstored/unindexed fields are involved. If I were you, I'd import the data into a single file and test it to see how well if performs. If typical searches take too long, I'd investigate design changes that improve the response time first, then if it still looks necessary, I'd consider ways to store the data in the fewest possible number of tables and/or files.
I will look further at combining all my accounts into one table, however, I would still like to persue the alternative solution so I can evaluate both options. But I have one problem with what you are suggesting.
My target field is fully defined and fixed (per record); it is my source field that varies by table (with fixed fieldname). I note that Set Field by Name replaces the contents of a calculated target field. How can I set up a calculated source field????
I suggested two tools: Set Field by Name and GetField.
GetField should do what you need here.
Maybe my problem is that I am incorrectly calculating the table/field name.
I'm trying to set the source address to "Account 1::Running_Balance_of_Shares"
I'v tried in Set Variable, Set Field, Set Field by Name etc, but can't seem to get anything to work.
e.g. in a test I preset the account name and tried to pick up the value in the field Running_Balance_of_Shares and tried to set it into a global variable that I could use later.
Set Variable [$AccountName; Value:"Account 1"] to simulate using a variable table of accounts
Set Variable [$$Running_Balance; GetFieldName ( $AccountName & "::Running_Balance_of_Shares" )]
I did a Show custom dialog of $$Running_Balance and it indicated a '?' - I presume I'm doing something wrong, I just don't know what!
Oops! I don't know how many times I tried that - each one with a typing mistake! Of course I saw it the moment I posted the last message.
OK, it works now!