7 Replies Latest reply on Mar 30, 2010 8:49 AM by wadef

    Using a calculated table::field source address in Set Variable or Set Field script

    wadef

      Title

      Using a calculated table::field source address in Set Variable or Set Field script

      Post

      I'm new to databases & am trying to use FMP 11 to manage several different investment accounts.

       

      Because I have several different accounts, each to be populated with many years of history, I want to keep them in separate tables.  I've been able to script a report that summarizes the current holdings of an account, and I can run it on each account independently.  I'm trying to script a loop that allows me to select one account (table) after another and summarize each in turn.

       

      I've set up a list (in another table) of accounts and can navigate from account to account using a variable in a "Go To Layout [$AccountLayout]" statement.  I then do a Find to select a subset of records and go to the last record found.  This works fine.  However, when I try to pick a field of data from that record, I can't figure out how to specify the source table::field in a Set Variable command (the table changes with each different $AccountLayout).  I can't seem to figure out how to set up a calculated string that works as the source table/field.  Is it even possible to use a calculated string to enter as the source table/field in a Set Field or Set Variable command?  

       

      I've tried a Copy/Paste operation which simply operates on the current record, but all of the data gets pasted into a single field in the target record - not too helpful.  How else can I get the data from a calculated table/field address into some variable so I can store it in my report.

       

      Thanks for any help you can give me.

       

      Wade

        • 1. Re: Using a calculated table::field source address in Set Variable or Set Field script
          philmodjunk

          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.

          • 2. Re: Using a calculated table::field source address in Set Variable or Set Field script
            wadef

            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!

             

            Thanks again

            Wade

            • 3. Re: Using a calculated table::field source address in Set Variable or Set Field script
              philmodjunk

              "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.

              • 4. Re: Using a calculated table::field source address in Set Variable or Set Field script
                wadef

                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????

                 

                thanks

                Wade

                • 5. Re: Using a calculated table::field source address in Set Variable or Set Field script
                  philmodjunk

                  I suggested two tools: Set Field by Name and GetField.

                   

                  GetField should do what you need here.

                  • 6. Re: Using a calculated table::field source address in Set Variable or Set Field script
                    wadef

                    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!


                    • 7. Re: Using a calculated table::field source address in Set Variable or Set Field script
                      wadef

                      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!