Hi clever people ... I have a question,

I've developed a timetracking system for my comapny, and they ahve asked me to impliment a way to flag when a job has nearly reached its estimate.

For example, job is quoted at $500, flag it as "nearly at quote" when it gets to $400.

My thinking would be to have a calculation that works out the percentage of the quoted amount and when it reaches "X%" have a another field display the message?...

now, the question is , how the bloody hell do one do that?

Thanks in advance.

Al

Something like:

Case ( Sum ( JobItems::Cost ) / Estimate ≥ 0.8 ; "Message" )

perhaps?

Hi again... another probably very dumb question, i've been at it for an hour or so now with no joy.

ok, using this script...

If(quote Total Amount >"0";Case ( Sum(Actual total) / quote Total Amount ≥ .8; "NEARLY AT BUDGET" );0)

i've managed to get it to diplay a message saying "nearly at quote" when costings reach 80%.. which is brilliant - thanks to "Comment" for pointing me in the right direction.

Now they ahve asked that the message changes when the costing have gone over budget. I tried this...

If(quote Total Amount >"0";Case ( Sum(Actual total) / quote Total Amount ≥ .8; "NEARLY AT BUDGET" )

xor

Case ( Sum(Actual total) / quote Total Amount < 1; "OVER BUDGET" );0)

but is not showing the massage, just displaying a 1 or a 0

Thanks again for all your help

Al

Case(

(Sum(Actual total) / quote Total Amount >= .8) and (Sum(Actual total) / quote Total Amount < 1) ; 

"NEARLY AT BUDGET" ; 



Sum(Actual total) / quote Total Amount > 1 ;

"OVER BUDGET"

)

Or if you wanted to make it shorter, or rather more readable for future purposes..

Let( amount = Sum(Actual total) / quote Total Amount ;



Case(

amount >= .8 and amount < 1; 

"NEARLY AT BUDGET" ; 



amount > 1 ;

"OVER BUDGET"

)



)

I'm not sure your quote Total Amount > 0 is necessary though -- you can't divide by zero, so it wouldn't return a number greater than 1 or greater than .8 (think it returns a question mark).

Hope that helped .

Do not double post in the Forum.

Because you have responses to both topics, I will merge the newer one with the older one.

Lee

Thanks Genx ... Its nearly there, i can feel it!

using this script

Let( amount = Sum(Actual total) / quote Total Amount ;



Case(

amount <= .8; 

"on track" ; 



amount >= .8; 

"NEARLY AT BUDGET" ; 



amount > quote Total Amount ;

"OVER BUDGET"

)



)

It works, kind of, In the system, sometimes a job gets quoted beforehand, sometimes not.

In cases where the job hasnt been quoted, using this script it displays "nearly at budget" all the time

When a job has been quoted, it works and only displays the message when the .8 target figure is met, however when the job goes over budget it still dispays "nearly at budget" message.

Once again.. i really appreciate any input you have on this one.

My background is in design and the scripting side of FMP boils my brain :

Cheers

Al

Sorry, my mistake... you have to catch for less than 1 in the second condition

Let( amount = Sum(Actual total) / quote Total Amount;



Case(

amount <= .8; 

"on track" ; 



amount >= .8 and amount <= 1; 

"NEARLY AT BUDGET" ; 



amount > 1 ;

"OVER BUDGET"

)



)

you have to catch for less than 1 in the second condition

And ONLY for that - since the test amount <= .8 MUST be false if the evaluation ever proceeds to the second test:

Case (

amount <= .8 ; "on track" ; 

amount <= 1 ; "NEARLY AT BUDGET" ; 

"OVER BUDGET"

)

Good point.

Thanks again for all your input... you have been a HUGE help in pointing me in the right direction.

FYI, this is the script i ended up with

If(quote Total Amount >"0";

Let( amount = Sum(Actual total) / quote Total Amount ;



Case (

amount = 1 ; "" ; 

amount <= .8 ; "" ; 

amount <= 1 ; "NEARLY AT BUDGET" ; 

"OVER BUDGET"

)



)



)

Basically they only wanted to show a message when close to the quote or over the quote (?!? - I'm sure they have reasons why)

Cheers

Al

Just FYI: The following will give you the same result without the two dud statements...

Let( amount = Sum(Actual total) / quote Total Amount ;



Case (

amount > 0.8 and amount <= 1 ; "NEARLY AT BUDGET" ; 

amount > 1 ; "OVER BUDGET"

)



)

Again: if you change the order, you can save an evaluation.

sigh, you and your logic

Case (

amount > 1 ; "OVER BUDGET" ;

amount > 0.8 ; "NEARLY AT BUDGET"

)