1 Reply Latest reply on Jul 30, 2012 2:52 PM by philmodjunk

    Reseting field "Budget Used"

    MarcMcCall

      Title

      Reseting field "Budget Used"

      Post

      Hey guys,

       

      I need a little help setting the calculation up.  (I'm guessing this would be a Auto Enter Calculation pending contents of Budget_Period)

       

      I have 4 fields

      Budget, Budget_Used, Budget_Period, Budget_Start_Date

      I want the Budget_Used to reset to 0.00 when the it is time to reset via the Budget_Period and Budget_Start_Date.

      The Budget_Period is attached to a value list containing Not Specified/Daily/Weekly/Mointhly/Quarterly/Yearly

      When it is set to Not Specified, it will not need to do anything.

      Budget isn't really pertainate to this question, it is just the amount of the Budget for the Budget_Period.

       

      Thanks in advance for your assistance.

      Marc

       

        • 1. Re: Reseting field "Budget Used"
          philmodjunk

          I'd be inclined to keep each record for each budget and start each new budget with a new record. That enables you to review past budget informations to compare to the current.

          This cannot be done with an auto-entered calculation because references to the system clock via Get ( CurrentDate ) won't trigger an update to this field. You'd need to either make it a calculation field or update it with a script.

          Let ( T = Get (currentDate ) ;
                  Case ( Budget_Period = "Daily" And Budget_Start_Date < T ; 0 ;
                             Budget_Period = "Weekly" and ( Budget_Start_Date + 7 ) < T ; 0 ;
                             Budget_Period = "Monthly" and Date ( Month ( Budget_Start_Date + 1 ) ; Day ( Budget_Start_Date ) ; Year ( budget_start_Date ) ) < T ; 0 ;
                             Budget_Period = "Quarterly" and Date ( Month ( Budget_Start_Date + 3 ) ; Day ( Budget_Start_Date ) ; Year ( budget_start_Date ) ) < T ; 0 ;
                             Budget_Period = "Yearly" and Date ( Month ( Budget_Start_Date ) ; Day ( Budget_Start_Date ) ; Year ( budget_start_Date ) + 1 ) < T ; 0 ;
                             Budget_Used // Else
                            ) // Case
                 ) // let