1 2 Previous Next 26 Replies Latest reply on Aug 15, 2012 8:18 AM by philmodjunk

    Carry Over If Statement

    dg3321

      Title

      Carry Over If Statement

      Post

      I have a monthly income/expense table where I would like to set up a formula for a 'loss carry over' field.  Basically, this field only has a figure in it if the net operating income in the prior month (prior record) is negative.

      Simply put, i would like to create a formula that basically says:

      If 'Net Operating Income<0 in the previous record (last month), then insert that figure into the current record's 'loss carry over' field.

      I could do this manually as it is very rare, but i would like to set it up automatically.

      Thanks.

        • 1. Re: Carry Over If Statement
          schamblee

          I think the easies way to do this is to setup a global field.  If there is a carry over amount then you set that amount into the global field.   Then use this global field in your calculation.  This global carry over field is separate from the local field.   On the local carry over field use a script trigger on objectexit to set the global field.

          • 2. Re: Carry Over If Statement
            dg3321

            Ok.  And how do i set it so that its putting the carry over figure into the global field on the next record?

            • 3. Re: Carry Over If Statement
              schamblee

              I wouldn't place the Global field on the record.    I would have a separate table with this carry over global field amount.   I would create a button that creates the new record for the next month that checks this global field and then set the local field. 

              • 4. Re: Carry Over If Statement
                dg3321

                I see, so the button i currently have that creates a new record, needs a script that checks the global field to see if there is a figure in it?

                As far as creating a relationship between the two tables, would the global field (in the new table) = the loss carry over field?

                • 5. Re: Carry Over If Statement
                  philmodjunk

                  This auto-enter calculation will copy any negative value from the previous record in the current found set's sort order:

                  If ( Get ( RecordNumber ) > 1 ; If ( GetNthRecord ( net operating income ; Get ( recordNumber ) - 1 ) < 0 ; GetNthRecord ( GetNthRecord ( net operating income ; Get ( recordNumber ) - 1 ) ) )

                  It's also possible to set up a self join relationship that links to the record of the previous month and then a calculation can refer to that record in such an auto-enter calculation or just a calcualtion field for that matter.

                  • 6. Re: Carry Over If Statement
                    schamblee

                    You wouldn't need a relationship.  Thats why you use a global field.  You are going to have one record in this new table.  

                    The global field would change each month.   It's basically  a holding field month to month.

                    You would need a button when you create the record to check this field. 

                    To update the global field (end of month) you would need a button or you could use a script trigger to save this amount.

                     

                    • 7. Re: Carry Over If Statement
                      philmodjunk

                      With a relationship, you don't need a script. Either method can be made to work.

                      • 8. Re: Carry Over If Statement
                        schamblee

                        There are several ways to do things.   I just offer one way.  Use which ever method suits your needs.  

                        • 9. Re: Carry Over If Statement
                          dg3321

                          Is the global field a number or calculation?  What is the formula for said calculation?

                          • 10. Re: Carry Over If Statement
                            dg3321

                            Phil,

                            When I used your formula in the 'loss carry over' calculation field, substituting the specific name i have for the ;net operating income field', i'm getting an error that says "There are too few parameters in this function".  What should i do to correct it?

                             

                            • 11. Re: Carry Over If Statement
                              schamblee

                              Just a number field.   Again this field is just a holding field.  Example :  CarryOverTable and a field Named CarryOverAmount. 

                              You press button to create new Record, which runs script to get  CarryOverTable::CarryOverAmount then sets current months table lost carry over amount field.

                              When you are done with all figures you could have a post button that post this Carry Over Amount or could Trigger Script.

                              Thinking thur this more you could just have a button to set a variable to $CarryOverAmount then  Create the "New Next Month Record" and set the current carry amount field from this $CarryOverAmount 

                                   

                              I try to give a quick answer and found out that are several ways it could be done.

                               

                              • 12. Re: Carry Over If Statement
                                philmodjunk

                                I had a typo with an extra call teo GetNthRecord. Try this:

                                If ( Get ( RecordNumber ) > 1 ; If ( GetNthRecord ( net operating income ; Get ( recordNumber ) - 1 ) < 0 ;
                                                                                    GetNthRecord ( net operating income ; Get ( recordNumber ) - 1 )
                                                                                   )
                                    )

                                • 13. Re: Carry Over If Statement
                                  dg3321

                                  Phil,

                                  That worked seamlessly!  Thank you!

                                   

                                  -David

                                  • 14. Re: Carry Over If Statement
                                    dg3321

                                    Phil,

                                    One last question: How do i set it up so that if there is no figure in the loss carry over field, that it shows $0.00?

                                    1 2 Previous Next