1 2 3 Previous Next 73 Replies Latest reply on Dec 31, 2016 4:34 PM by BeatriceBeaubien

    Storing result of a calculation into a higher level table


      I have a database for investments, which contains (amongst other tables) a table describing each INVESTMENT (stocks, bonds, term deposits, mutual funds etc etc). Attached to this is another table of TRANSACTIONS (buys, sells, reinvested dividends, interest earned, stock splits etc etc). Each investment will have one or more transactions.


      I have a script which, using the transactions, calculates the adjusted cost base (ACB) for each stock-type investment, and it produces a report showing the results for each stock/mutual fund etc. The results are correct (this script is discussed elsewhere in this discussion forum, where I had a problem exiting nested loops -- that's been resolved). This script also attempts to store the ACB result into the "mother" INVESTMENT table, so I can readily see the latest ACB for any investment. The last command, before the exit, stores the ACB result (at the Transaction level) into a field in the Investment table, for the investment corresponding to these transactions. I use a Set Field command to move the ACB result into the higher table.


      However, this process doesn't always work as desired. After running the ACB script first, then looking at the content of the Investment table, about 90% of the  ACB's are stored correctly in the Investment table. For some strange reason, the others are either blank, or have erroneous results. It appears quite random, as I haven't figured out why certain investments are not getting their correct ACB figure (which appear in the ACB report). Repeating the process produces the same results, so the investments affected with the error don't change -- always the same investments. Strange, since they're all treated the same way in the ACB script, and that script does produce correct results for each investment, as shown in the resulting report. The mystery is why don't the correct ACB results always get placed in the higher (Investment) table? 

        • 1. Re: Storing result of a calculation into a higher level table

          In theory, what you describe should work correctly and 100% of the time. Since it doesn't, you'll need to both share the script and document the precise relationship you are using in hopes someone will spot why it isn't working for you. Note that you can post a script to this forum in a number of ways:


          1. You can use the picture icon above to add a screen shot of your script.
          2. You can generate a DDR in FileMaker Advanced and copy/paste the script as text from that report.
          3. You can print your script as a PDF and then copy/paste the text from the PDF to here in the forum.
          • 2. Re: Storing result of a calculation into a higher level table

            Is it scripted as part of the loop? In that case a later iteration of the loop of transactions could be overwriting the investment record with the 10% of errors.


            If I was thinking about recalculating a value for investments regarding my found set of transactions. I would simply use:

            Go To Related Records [ Investments ; Investments Layout ; Related to All in Found Set ]

            Replace Field Contents [ Investments::ACB ; ACB Calc ]

            That way I am updating from the Investments context, not the Transactions.


            Other things to check could be record locking or other errors preventing the set field command, in which case you can trap an error like:

            Set Field [ Investments::ACB ; ACB Calc ]

            If ( get(lasterror) <> 0 ]

                Show Custom Dialog [ ERROR ; get(lasterror) ]

            End If


            If you can run this on a smaller set of records and produce an error, try using the script debugger to step through and see where an error may occur.

            • 3. Re: Storing result of a calculation into a higher level table

              Thanks, Phil


              Here is the script I use for calculating Adjusted Cost Base. It's complicated, because it's like calculating a moving average of amount invested (or recovered) in each transaction for a given investment. And if a transaction is a 'sale' or 'split' of shares, the ACB for that transaction is taken from the previous transaction. I use a nested loop. The inner loop processes all the transactions for a given investment, while the outer loop resets fields for the next investment.  The final ACB, after all the transactions for a given investment have been processed, is the one I want to save in the Investment table. But this only works about 90% of the time. For the investments where it doesn't work, I haven't found a common thread amongst them that would explain why it doesn't work for them. It's strange, because the report produced by this script shows a correct final ACB for all the investments (I've checked the results with manual calculations to ensure they're correct). I've underlined the two commands (near the end of each loop) where I save the calculated ACB amount.   Hope this helps!


              Go to Layout [ “ACB ADJUSTED COST BASE Report v2 ALL Stocks” (TRANSACTION) ]

              Print Setup [ Orientation: Landscape; Paper size: 8.5" x 11" ] [ Restore; No dialog ]

              Show All Records

              Enter Find Mode [ Specified Find Requests:

              Find Records; Criteria: INVESTMENT::StockType: “==Common” AND INVESTMENT::Status: “=Active”

              Find Records; Criteria: INVESTMENT::StockType: “==ETFs” AND INVESTMENT::Status: “=Active”
              Find Records; Criteria: INVESTMENT::StockType: “==US_Common” AND INVESTMENT::Status: “=Active”
              Find Records; Criteria: INVESTMENT::StockType: “==Mutual_Fund” AND INVESTMENT::Status: “=Active”

              Find Records; Criteria: INVESTMENT::StockType: “==Income_Trust” AND INVESTMENT::Status: “=Active”

              Omit Records; Criteria: TRANSACTION::NetInvested: “=0” ]
              [ Restore ]

              Perform Find [ ]

              Set Field [ GLOBALS::gFoundCount; Get ( FoundCount ) ]

              Sort Records [ Specified Sort Order:

              TRANSACTION::StockAbbrev; ascending
              TRANSACTION::XtnDate; ascending
              TRANSACTION::XtnType; ascending ]

              [ Restore ]

              Allow User Abort [ On ]

              Freeze Window

              Enter Browse Mode

              Go to Record/Request/Page [ First ]

              Exit Loop If
              [ GLOBALS::gRecordNumber = GLOBALS::gFoundCount ] Set Field [ GLOBALS::gACBcost; 0 ]
              Set Field [ GLOBALS::gACBperShr; 0 ]
              Set Field [ GLOBALS::gUnits; 0 ]
              Set Field [ GLOBALS::gS1Units; 0 ]
              Set Field [ TRANSACTION::rACBcost; 0 ]
              Set Field [ TRANSACTION::rUnits; 0 ]
              Set Field [ INVESTMENT::ACBperShr; 0 ]
              Set Field [ GLOBALS::gStockAbbrev; TRANSACTION::StockAbbrev ]


                  Exit Loop If [ TRANSACTION::StockAbbrev GLOBALS::gStockAbbrev ]

                  Set Field [ GLOBALS::gS1Units; If ( TRANSACTION::XtnType = "split1" ; TRANSACTION::Units ; GLOBALS::gS1Units ) ]

                  Set Field [ TRANSACTION::cACBcost; If ( TRANSACTION::InvestedAmt 0 ; TRANSACTION::InvestedAmt ; TRANSACTION::Units * GLOBALS::gACBperShr ) - (TRANSACTION::CapReturn) + (TRANSACTION::NonCashDstrb) ]

                  Set Field [ TRANSACTION::cACBperShr; If ( TRANSACTION::cACBcost 0 ; If(TRANSACTION::CapReturn = 0 and TRANSACTION::NonCashDstrb = 0; TRANSACTION:: cACBcost / TRANSACTION::Units ; 0) ; 0 ) ]

                  Set Field [ TRANSACTION::rACBcost; If ( TRANSACTION::XtnType = "split1" or TRANSACTION::XtnType = "split2" ; GLOBALS::gACBcost ; TRANSACTION::cACBcost + GLOBALS::gACBcost) ]

                  Set Field [ GLOBALS::gACBcost; TRANSACTION::rACBcost ]

                  Set Field [ TRANSACTION::rUnits; TRANSACTION::Units + GLOBALS::gUnits ]

                  Set Field [ GLOBALS::gUnits; TRANSACTION::rUnits ]

                  Set Field [ TRANSACTION::rACBperShr; If ( TRANSACTION::XtnType = "split1" or TRANSACTION::rUnits 0; GLOBALS::gACBperShr ; TRANSACTION::rACBcost / TRANSACTION::rUnits) ]

                  Set Field [ TRANSACTION::cCapGains; If ( TRANSACTION::XtnType = "Sell" ; TRANSACTION::cACBcost - TRANSACTION::InvestedAmt ; 0 ) ]


                 Set Field [ GLOBALS::gACBperShr; TRANSACTION::rACBperShr ]    ## Save current ACB into a global field

                  Set Field [ GLOBALS::gRecordNumber; Get ( RecordNumber ) ]

                  Go to Record/Request/Page [ Next; Exit after last ]

                  End Loop

                  Set Field [ INVESTMENT::ACBperShr; GLOBALS::gACBperShr ]   ## After last transaction for an Investment, save latest ACB to Investment.

              End Loop

              Perform Script [ “Calc Last Page Number” ]


              • 4. Re: Storing result of a calculation into a higher level table

                the report produced by this script shows a correct final ACB for all the investments

                Does this mean that you don't see the missing data in that report or did you mean that you get the correct ACB for all the investments for which you get a value?


                If you truly meant all investments and that report uses:




                To show the value, then your script is working correctly and you have a layout design issues that is keeping it from displaying correctly on the layout where it is missing.


                If that's not what you meant and INVESTMENT::ACBperShr is truly empty, the I'd take a very careful look at this script step and any steps that affect the value of a field referenced in its calculation:


                Set Field [ TRANSACTION::rACBperShr; If ( TRANSACTION::XtnType = "split1" or TRANSACTION::rUnits 0; GLOBALS::gACBperShr ; TRANSACTION::rACBcost / TRANSACTION::rUnits) ]


                Perhaps this step is setting the global field to null (empty) instead of a value for certain records in your loop.


                The other possibility might be that certain investments are getting skipped by your script--for some reason they aren't in this found set at the time you perform this script.


                In both cases, watching the script in FileMaker Advanced's Script Debugger while monitoring field values in the data viewer might be your best shot at finding out why/how this is happening.

                • 5. Re: Storing result of a calculation into a higher level table

                  In the report produced by the script I get ALL the correct ACB's for ALL the investments in the found set. It shows exactly what I want and expect.  It seems logical that I should be able to save the (final) ACB for each investment for into the Investment table.


                  The report (Body section) lists every transaction for each investment and displays the ACB value on each transaction line. It makes for a lengthy report (44 pages) but this allows me to verify that the calculation is working correctly as it processes each transaction -- because I don't always automatically trust FileMaker results!!  The ACB on the final transaction for a given investment, is the final ACB value that I'm trying to store in the table for that investment. Why it works for some investments, and not others, is a mystery! The ones it doesn't work for are a mixed bag, some are common stocks, some are mutual funds, some income trusts.  No apparent rhyme or reason.

                  • 6. Re: Storing result of a calculation into a higher level table

                    The script and layout that lists the Investments (with their ACB's) is very simple and basic. I created it for testing the results of this ACB storage.  It merely displays four fields, including the investment name, investment type, number of units (shares) and the ACB.  I've made the ACB field extra wide on the layout to accommodate any large value. Same issue. For the 10% where ACB is wrong, it's either showing an incorrect amount or is blank. I don't think it's a layout issue in this case. The other three fields are correct.


                    In my ACB calculation script, I save the ACB figure calculated on the final transaction for an investment into that investment's record in Investment table (Investment table and Transaction table are linked based on name of investment).  At least that's my intent. Somehow, it's gets out of sync occassionally. 


                    I wonder if I should create another table, with key field of investment name (linked to main Investment table) just for saving the calculated ACB.  Would that make any difference? This table would contain only investment name and ACB.

                    • 7. Re: Storing result of a calculation into a higher level table

                      Set Field [ TRANSACTION::rACBperShr; If ( TRANSACTION::XtnType = "split1" or TRANSACTION::rUnits 0; GLOBALS::gACBperShr ; TRANSACTION::rACBcost / TRANSACTION::rUnits) ]


                      As for this command, the problem might have happened when the stock was splitting or when the cumulative number of units (shares) went negative for some reason. This isn't the case with those in error.


                      Furthermore, according to the report produced by this ACB script, every one of the ACB's is correct, for all investments in the found set!! It lists every investment's transactions with the corresponding ACB at the point of that transaction. I can veryify the calculations step by step this way. The mystery is why can't it copy that correct (final) ACB amount to the related Investment table all the time??!!!


                      Unfortuately, I don't have Debugger capability with this version of FM10, it seems.

                      • 8. Re: Storing result of a calculation into a higher level table

                        These was a FileMaker Advanced version for version 10. But upgrading would be a good idea if only to get an advanced copy for development purposes. Trying to develop any kind of serious solution without advanced is like getting in a fist fight with one hand tied behind your back.


                        You seem not to have addressed another suggestion of mine--That your scripted find might be failing to find some records and this is why you are getting missing or incorrect values. That would seem something that you can check by taking temporarily inserting an Exit or Halt script step immediately after the Find is performed so that you can inspect the found set and confirm whether or not all the records are actually "there" for purposes of calculating these values.


                        You might also consider using a summary report with sub summary parts and no body as way to get one row of data for each investment from a report based on transactions since a variation of that report now shows you the correct data.

                        • 9. Re: Storing result of a calculation into a higher level table

                          I am getting all the records in the Find!! The report produced by the script with the nested loops (the one that calculates the ACB for each investment) shows correct figures for all the investments. Nothing is missing. I also have another version of this same report, without the Body part, showing just the Summary line (i.e. one line for each investment) showing the calculated ACB for each investment, and it too is totally accurate and complete. The detailed report is 44 pages long, the shorter report is only 4 pages long.  Both use the identical script, only difference being that the Layout for the shorter version lacks the Body part. I'm happy with these results. I just can't figure out why I can't copy or transfer the results to the parent (Investment) table from the child (Transaction) table at the moment in time where it has processed the last transaction for that investment -- or why it works for 90% of the investments.  The field 'rACBperShr' is defined as simply a 'number' field in the Transaction table. I move the calculated ACB/share into this field in the script. The rACBperShr is also used in both Layouts and displayed (correctly) in Browse or Preview mode in both the Body and Summary parts. This is the field I attempt to copy into the parent table, but don't always succeed. Hence the mystery. 


                          Ultimately, I want to use this ACBperShr in further calculations, such as true yield (dividing the past year's earnings per share by the ACBperShr.  That is, divide the current annual earnings per share by the price paid for the shares. But first, I've got to get the ACBperShr at the "Investment" level. I've no problem with the total number of shares (units) -- they work out correctly in every case.

                          • 10. Re: Storing result of a calculation into a higher level table

                            Thanks, Mike


                            It is part of a loop. With each transaction (for an investment) it calculates the ACB, bit like a running average. After the last transaction (for that investment) I want to copy the ACB (rACBperShr value) at that point to the parent Investment table. For 90% of the investments, this works fine. For the rest, no -- it's either a blank value or wrong value.  Yet, the report produced by the script itself produces correct values (manually verified) for every investment in the found set, and even lists all the transactions so I can see at every stage what the ACB value is. It's 100% correct.  Now, why doesn't that final ACB value for each investment copy over into the Investment table???


                            I replaced my Set Field command with the Go to Related Record and Replace Field Contents like you suggest. Same result, no change.


                            I don't see any common characteristics about those investments where it's not working. Very strange.


                            I first run the script that calculates the ACB for the found set of investments, which produces a report showing the results (with correct values for all), and (hopefully) transfers the final ACB value to the investment record in the parent Investment table.

                            I then run another script which lists the contents of these same investments (from the Investment table) so I can check if the ACB value got transferred. This is where I find the missing or erroneous ones.


                            I've spent the last two days trying to figure this out!! There's got to be a way!!! (or obvious reason).

                            • 11. Re: Storing result of a calculation into a higher level table

                              This is driving me nuts!!


                              I put Show Custom Dialog commands at critical places (near beginning and end of inner loop) in the script to display values such as RecordNumber, StockAbbrev (stock name), TransactionType (buy/sell etc), and rACBperShr (ACB value at end of each transaction). I changed the Find criteria to reduce the number of records to seven investments, with a total of 10 transactions including a case of splitting shares. Of these seven stocks, only one had the correct ACB copied to the parent table. Of the remaining six, one had the ACB value 0 (zero) in the parent table, and the rest had blank ACB's in the parent table. The report produced by the script displays correct values in each case.


                              No dialog ever appears when running the script. I then inserted indefinite Pause commands immediately before the Dialog commands. Still no effect.  How does this work???? The script produces correct results, but doesn't share them!! And it ignores the Dialog and Pause commands altogether.

                              • 12. Re: Storing result of a calculation into a higher level table

                                This is driving me nuts!!

                                You really need that debugger. The possible points of failure appear to be these:


                                1. The scripting and calculated expressions that compute this value fail under certain undetermined circumstances
                                2. The script fails to find the correct records
                                3. The script fails to evaluate from the correct record or group of records when setting this value to the parent record,
                                4. A script trigger is being tripped and that script somehow interferes with the original script's ability to function correctly.
                                5. Some sort of damage exists in your file--a messed up index is one but not the only possibility here


                                Trying to isolate the cause without that step by step execution while monitoring and testing field and variable values at each step is very hard to do without FileMaker Advanced. Spotting the fact that a trigger performed script is interfering can be missed very easily without the debugger.


                                One thing I haven't spotted is the suggestion that you try this as a test in a recovered copy of the file. If #5. turns out to be the source of this trouble, either the recover process will report problems with your file or the recovered copy will produce correct results when your original copy does not. (The recover process deletes and rebuilds all indexes without testing the indexes for problems so you can get a "no problems found" result and still have the recovered copy work when the original does not.)

                                • 13. Re: Storing result of a calculation into a higher level table

                                  It seems my FileMaker software or database is messed up. Older scripts and reports seem to still work OK. But newly created ones do not. I've all but given up trying to transfer calculated ACB results into a parent table. The next option is to enter them manually from the report that is produced by the ACB script, which displays all the ACBs correctly.


                                  To this end, I created a new layout to list the investments (not transactions) so I can manually enter the ACB into the appropriate field for each investment. In a simple script, using this layout, I only want to display "Active" investments (there's a Status field indicating whether the investment is active or not), and only those types that are common stocks, mutual funds, investment funds etc. Simple Find commands.


                                  Doesn't work. FileMaker ignores the Find commands altogether. The result is a listing of ALL investments, active or not, and ALL types including ones I don't wish to see. I've tried many variations of Finds and Omits, in the Enter Find Mode specs, or in the Perform Find, and even Constrain command, to no avail. FM ignores whatever I put there. I can verify, by going to the table directly (table view), that the values I'm searching for are indeed there and spelled correctly. Status and Type are originally entered into each Investment record by using Drop-down lists, so their values are consistently spelled correctly.


                                  As for the ACB script, which was ignoring Show Custom Dialog commands, I've now taken those out. I also took out the Read [Next, Exit after last] command ...made no difference. And removed the command which attempted to copy  (Set Field) the calculated ACB result for each investment into the Investment table. Script still works. That is, it produces a report, as desired, showing correct ACB results for each desired investment.    I just don't get it.  FM has a mind of its own. I feel ignored!


                                  As for your suggestion to use a recovered copy of the database.... I've never attempted this.  I see the Recover command in the File menu, and I can locate the latest copy of the database as well as previous backups. Which one should I "recover" from?_______... the latest one I saved (with the most recent scripts and layouts in it) or an older backup copy (wherein I'd have to re-create these latest scripts and layouts). I hope the answer is latest file!  This sounds hopeful, because FM is behaving so irrationally that perhaps there are indexes or something that are messed up. Should I first make a copy of the database before attempting the Recovery (just in case something else gets buggered)?___


                                  Many thanks for your suggestions. I appreciate your time and advice.  I've resisted acquiring Advanced or the latest version of FM because this is a hobby of mine, and FM isn't cheap. I'm retired with time on my hands. Worked in IT for some 30 years, but sometimes have a hard time getting my head around how FM works. I did work with and design relational databases in my job but mainly on "old" IBM mainframes or PC's using MS Access.  At home I've always been a Mac user, and since there's no Mac version of Access, I use FileMaker. They're quite different. Each has their plusses and minuses. Still, I get some -- perhaps perverse -- enjoyment out of working with stuff like this. Keeps the grey cells lubed. I hope.

                                  • 14. Re: Storing result of a calculation into a higher level table

                                    If you could post a copy of the file, or share it by private message here, I suspect the problems could be identified very quickly.

                                    +1 for Phil's suggestion to upgrade. You'll save some of your hair if you just go ahead and upgrade to advanced. And you'll save OUR time as well. Learning the skill of being able to troubleshoot accurately will help with the grey cells. Ooooh, the data viewer! Great fun!


                                    Note that you can create a copy that is either an empty clone; or which you have populated the copy with example data rather than your own vast and envy-inspiring portfolio.


                                    Speaking of grey cells: another skill to add to your bag of tricks, and which does NOT require Advanced, is to:

                                    1. Enter Find Mode

                                    2. Click into a field

                                    3. View Index (command - I )


                                    Sometimes it's amazing to see the data values that actually exist in the field.

                                    1 2 3 Previous Next