Case Statement

[Jonah74]

Am I righting in thinking I can use a case statement to evaluate two fields and return an answer into a third field ie: Field A has the number 27 in it. Field B has the number 32 in it.  Could the case statement look at the difference and put the difference in Field 3.The difference being 5? 

I’m also trying to get this to do the math on data entry, so the figure in Field A is 27 but when the user enters the new figure in Field B (i.e 32), it looks at that difference between A and B, copies the difference of 5 to Field 3 and then also copies the figure 32 from Field B into Field A thereby updating Field A to the new value.

Help, suggestions or thoughts will be greatly appreciated.

[Steve Martino]

Field 3 could just be a simple calculation of the difference between Field A and Field B.

But your last statement is confusing.  After Field A gets the value of 32, the same as Field B, Field 3 would then be ''0".

So what is the use for this?  Could you describe what you are doing in real-world terms and scenarios?

[rwoods]

Hi Jonah

The Case statement is used in a calculation, and doesn't seem very relevant to your question.

On your first question, you can have field 3 either be either

a) a calculated field or

b) a number field with an auto-enter calculation

The calculation should be (= FieldA - FieldB). Field3 will then automatically update whenever you edit either FieldA or FieldB.

 

The second part would need the use of script triggers, and would need to be done instead of the above options, not as well as them (as they would clash with each other)

To do the second question, have all three fields just be standard number fields, no calculations.

Then, put a script trigger on Field B (onObjectSave), that runs a script. Add a script trigger by right-clicking FieldB when you are in layout mode. The script should do the following

Set Field ( Field3 ; Value: Field A - Field B )

Set Field ( Field A ; Value: Field B )

Set Field ( Field B ; Value: "" )

 

[Jonah74]

23 minutes ago, Steve Martino said:

Field 3 could just be a simple calculation of the difference between Field A and Field B.

But your last statement is confusing.  After Field A gets the value of 32, the same as Field B, Field 3 would then be ''0".

So what is the use for this?  Could you describe what you are doing in real-world terms and scenarios?

Hi Steve

OK, Field A is a client cost that would have been quoted when a job for the client was booked for a journey, i.e £27.00. However, the true cost of the journey actually became £32.00 because of the additional mileage incurred so this is entered into Field B. I need to record the additional cost of £5.00 (the difference) in Field C (sorry not field 3). Then, the original cost field A, needs to be updated to reflect the true cost as shown in Field B £32.00. Hope this now makes sense!! 

31 minutes ago, rwoods said:

The second part would need the use of script triggers, and would need to be done instead of the above options, not as well as them (as they would clash with each other)

To do the second question, have all three fields just be standard number fields, no calculations.

Then, put a script trigger on Field B (onObjectSave), that runs a script. Add a script trigger by right-clicking FieldB when you are in layout mode. The script should do the following

Set Field ( Field3 ; Value: Field A - Field B )

Set Field ( Field A ; Value: Field B )

Set Field ( Field B ; Value: "" )

 

Hi, Thanks for the help. The script triggers seem to be the way to go. Thank you.

[Lee Smith]

Hi Jonah74,

When asking a question on the FM Forums, please don't use generic terms for things such as your fields, layouts, scripts, etc. because it adds confusion to the question. Names such as Field A, Field B, Field 1, Field 2, do not mean anything (unless that is your actual names). 

Using your real names can assist us in understanding what your question is and speed up the process in your receiving the answer that you are needing.

TIA

Lee

[Jonah74]

Hi Lee

In this case it was just a rhetorical question to see if it could be done without actually putting it into the application scenario. However I see your point and I've given the scenario to the members replying now.

Regards

Chris

[Fitch]

It seems redundant to put the true cost in field A when you already have it in field B.