Conditional formula problem

[agorjest]

Hello,
I'm using CR2008, ODBC. SQL Command combining labor, materials, and workorders tables as data source.
User chosen parameters: {?Start Date}, {?End Date}

Trying to use a conditional formula to get one field's value to equal another field's value under a certain condition. Here is the basic layout of the report:

Detail fields (suppressed)
command.wonumber command.glacct command.laborhrs @laborrate @laborcost command.mattrans @totalcost @annualbudget @dailyrunrate @budget @site command.rowstamp

GF2 (GL Account #)
command.glacct sum (@totalcost) @budget

GF1 (Site)
@site sum (@totalcost) #budget running total

Formula definitions:
@laborrate = Select command.glacct, Case (assigns a fixed labor rate to each GL account #.)
@laborcost = command.laborhrs * @laborrate
@totalcost = command.mattrans + @laborcost
@annualbudget = Select command.glacct, Case (assigns a fixed budget amount to each GL account #.)
@dailyrunrate = @annualbudget / 365
@budget = @dailyrunrate * datediff ("d", {?Start Date}, {?End Date})
@site = Select command.glacct, Case (assigns a fixed site string for each GL account #.)

Note that the formula @budget is the same on both the details and GL Account group levels, so that there is only 1 budget figure per GL account #. At the Site group level it is a running sum total of @budget, evaluated by the following formula: {Command.rowstamp} = maximum ({Command.rowstamp}, {Command.glacct})
-this is so only one budget figure gets totaled for each GL account #.

What I want to do is set a condition in @budget so that if the GL Account # starts with "1", then @budget is automatically set to equal the result of the @totalcost formula. (Note command.glacct is a string field). If GL Account # starts with any other number then I want it to display the default @budget. The reason for this is that some GL account numbers have $0 budget, which makes for a distorted variance between total costs and budgets, and I want to make the total costs and budget figures even for those accounts.


I'm not getting the right results using if-then-else statements within the @budget field. Any suggestions?
Thanks...

 

[Turkbear]

Hi,
Can you post your If..Then..Else logic for @budget that you say is not working?

Meanwhile try:

@budget
EvaluateAfter(@totalcost)
If command.glacct[1] = "1" 
then
@totalcost
Else
@dailyrunrate * datediff ("d", {?Start Date}, {?End Date})

Also try adding the EvaluateAfter control to each of the other formulas as needed to force a result before it is needed in another formula.





To Paraphrase:"The Help you get is proportional to the Help you give.."