3 Replies Latest reply on Aug 23, 2011 10:06 AM by philmodjunk

    Carry over totals



      Carry over totals


      I have used FMP for years since the first one out.  But never really got deep into it.  Need help. 

      Simply stated.   How can I have one record field "Start field" equal the "end Field of the previous record?

      I know that this is a simple problem but for some reason, I can't seem to do it.


        • 1. Re: Carry over totals

          A more detailed explanation of what you are doing might enable us to better suggest an answer. An actual example with realistic data is best for that purpose.

          To refer to data from the "previous record" depends on what criteria identify that "previous record".

          If you have a found set of records, getNthRecord can refer to a specific record in the found set. If you use:

          GetNthRecord ( Field ; If ( Get ( RecordNumber ) = 1 ; 0 ; Get ( RecordNumber ) - 1 )

          You are referring to the preceding record in the found set. If you sort your records into a different order, perform a find or otherwise work with your found set, the record referred to by this calculation will change.

          Thus it may be safer to establish a relationship for referring to that "previous record", though exactly how you set that up depends on the structure and function of your database.

          • 2. Re: Carry over totals

             Thanks Phil...   I guess I did not make myself clear.    Each record is a transaction of product X

            Fields :    A.  Amount on hand.    B.  Amount obtained,    C.  Amount disposed of     (all kinds of other data regarding clients etc.    D.  New Amount on hand.  (  A + B - C)    

            Now the new record for a new transaction     I want the new record to put  D  from previous record into the A amount on hand so that I will have a running total.   

            I realize that the found set will cause changes in this by using the previous record, but ...   I need an up to the minute (up to current transaction) amount that I have on hand or available.  

            I know that this may sound somewhat basic but that is me.   Thanks.


            • 3. Re: Carry over totals

              I've set up check ledgers and Inventory Log "ledgers" that do this. Here's how I do it:

              Define Amount Obtained (B) and Amount Disposed (C) as number fields.

              Define this calculation field, cBal, to combine the values and assign a negative to Disposed values: Amount Obtained - Amount Disposed

              Define a summary field, sBalance as a running total of cBal.

              If you are using this for several different items such as different inventory items or different bank accounts, you can specify the "Restart summary for each sorted group" option to restart the running total for each item or bank account in your found set. (You have to then sort your records by account or item to properly goup them in order for this to work.)