FM 8.5 pro
I have a TotalAmount field, Calculation
Net + Tax + ShippingCost - OrderDeposit
but I want to attach a Case function to it.
Case (Status = "1 Ordered" ; TotalAmount; Status = "2 Invoiced";
TotalAmount
; Status = "Shipped" ; TotalAmount; Status = "4 Paid"; " " ; Status =
"4 Cancelled"; " ")
How do I do this?
Thanks
buck.matthew74@yahoo.com wrote:
> FM 8.5 pro
>
> I have a TotalAmount field, Calculation
>
> Net + Tax + ShippingCost - OrderDeposit
>
> but I want to attach a Case function to it.
>
> Case (Status = "1 Ordered" ; TotalAmount; Status = "2 Invoiced";
> TotalAmount
> ; Status = "Shipped" ; TotalAmount; Status = "4 Paid"; " " ; Status =
> "4 Cancelled"; " ")
>
> How do I do this?
If those are the only possibilities:
Case (Status = "4 Paid" or Status = "4 Cancelled"; " "; TotalAmount)
You can replace TotalAmount with the actual "TotalAmount" calculation if
you wish.
In article
<03d1dba9-9d8e-416e-800f-f4921168f550@s27g2000prg.googlegroups.com>,
buck.matthew74@yahoo.com wrote:
> FM 8.5 pro
>
> I have a TotalAmount field, Calculation
>
> Net + Tax + ShippingCost - OrderDeposit
>
> but I want to attach a Case function to it.
>
> Case (Status = "1 Ordered" ; TotalAmount; Status = "2 Invoiced";
> TotalAmount
> ; Status = "Shipped" ; TotalAmount; Status = "4 Paid"; " " ; Status =
> "4 Cancelled"; " ")
>
> How do I do this?
>
> Thanks
I'm not quite sure what you're asking, but I'll try these two replies
and maybe one will answer your question.
REPLY #1 - Calculations within Case statements
--------
The Case command itself is structured as:
Case (Test_1; Result_When_Test_1_Successful;
Test_2; Result_When_Test_2_Successful;
Test_3; Result_When_Test_3_Successful;
etc.
Optional_Result_When_ALL_Tests_Fail
)
The command doesn't have to have the lines formatted this way, but it
makes it more readable asnd easier to work out what is going on than:
Case (Test_1; Result_When_Test_1_Successful; Test_2;
Result_When_Test_2_Successful; Test_3;
Result_When_Test_3_Successful; Optional_Result_When_ALL_Tests_Fail)
The "tests" can be any form of Calculation that works out as 'true' or
'false'. For example, a test of "A = B * C" will be successful / 'true'
if A equals the product of B multipled by C. If they are not equal the
test fails / 'false'. (There is a more complicated explanation dealing
with 'Boolean logic'.)
The 'results' can also be any form of Calculation that returns to
appropriate result type that you want (ie. number, date, etc.).
eg.
MathAnswer Number result
= Case (MathType = "Addition"; A + B;
MathType = "Subtraction"; A - B;
MathType = "Multiplication"; A * B;
MathType = "Division"; A / B;
MathType = "Average"; (A + B) / 2;
MathType = "Maximum"; Max (A, B);
MathType = "Minimum"; Min (A, B)
)
This will return different calculated results depending on the text
data in the MathType field. If MathType contains "Average" the
Calculation will work out the average of the numbers stored in fields A
and B.
REPLY #2 - Case Statements within Calculations
--------
The result of a Case command can be used anywhere in a Calculation that
the appropriate type of data is used (including as parameters for some
other functions and Script commands).
eg.
Sentence Text result
= NumApples
& " apple"
& Case (NumApples = 1; " is";
NumApples > 1; "s are"
)
& " left."
This Calculation will correct the plural of "apple" and sentence
grammar depending on how many apples there are.
ie.
NumApples = 1
Sentence = "1 apple is left."
NumApples = 5
Sentence = "5 apples are left."
Another example might be adding differing tax percentages based on a
region code.
eg.
AmountIncTax Number Result
= AmountExTax + Case (RegionCode = "A"; AmountExTax * 0.10;
RegionCode = "B"; AmountExTax * 0.15;
RegionCode = "C"; AmountExTax * 0.20;
0
)
will add 10% tax for RegionCode "A", or 15% tax for RegionCode "B", or
20% tax for RegionCode "C" or add no tax for any other RegionCode.
Since in this example the amount of tax is always based on the
AmountExTax field, it could also be written as:
AmountIncTax Number Result
= AmountExTax + AmountExTax * Case (RegionCode = "A"; 0.10;
RegionCode = "B"; 0.15;
RegionCode = "C"; 0.20;
0
)
Either version will give the correct answer.
Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)
Thank you Vandar and Harry. Actually Vandar understood the problem and
his solution worked. That said, is there a more elegant way to join
two Case functions?
If TotalAmount =3D Net + Tax + ShippingCost - OrderDeposit, when Status
=3D "1 Ordered" ; TotalAmount; Status =3D "2 Invoiced" etc.
On Jan 24, 7:08=A0pm, Helpful Harry
wrote:
> In article
> <03d1dba9-9d8e-416e-800f-f4921168f...@s27g2000prg.googlegroups.com>,
>
>
>
> buck.matthe...@yahoo.com wrote:
> > FM 8.5 pro
>
> > I have a TotalAmount field, Calculation
>
> > Net + Tax + ShippingCost - OrderDeposit
>
> > but I want to attach a Case function to it.
>
> > Case (Status =3D "1 Ordered" ; TotalAmount; Status =3D "2 Invoiced";
> > TotalAmount
> > ; Status =3D "Shipped" ; TotalAmount; Status =3D "4 Paid"; " " ; Status =
=3D
> > "4 Cancelled"; " ")
>
> > How do I do this?
>
> > Thanks
>
> I'm not quite sure what you're asking, but I'll try these two replies
> and maybe one will answer your question.
>
> REPLY #1 - Calculations within Case statements
> --------
> The Case command itself is structured as:
>
> =A0 =A0Case (Test_1; Result_When_Test_1_Successful;
> =A0 =A0 =A0 =A0 =A0Test_2; Result_When_Test_2_Successful;
> =A0 =A0 =A0 =A0 =A0Test_3; Result_When_Test_3_Successful;
> =A0 =A0 =A0 =A0 etc.
> =A0 =A0 =A0 =A0 =A0Optional_Result_When_ALL_Tests_Fail
> =A0 =A0 =A0 =A0 )
>
> The command doesn't have to have the lines formatted this way, but it
> makes it more readable asnd easier to work out what is going on than:
>
> =A0 =A0Case (Test_1; Result_When_Test_1_Successful; Test_2;
> =A0 =A0Result_When_Test_2_Successful; Test_3;
> =A0 =A0Result_When_Test_3_Successful; Optional_Result_When_ALL_Tests_Fail)=
>
> The "tests" can be any form of Calculation that works out as 'true' or
> 'false'. For example, a test of "A =3D B * C" will be successful / 'true'
> if A equals the product of B multipled by C. If they are not equal the
> test fails / 'false'. (There is a more complicated explanation dealing
> with 'Boolean logic'.)
>
> The 'results' can also be any form of Calculation that returns to
> appropriate result type that you want (ie. number, date, etc.).
> eg.
> =A0 =A0 =A0MathAnswer =A0 =A0 Number result
> =A0 =A0 =A0 =A0 =A0 =3D Case (MathType =3D "Addition"; A + B;
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 MathType =3D "Subtraction"; A - B;
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 MathType =3D "Multiplication"; A * B;
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 MathType =3D "Division"; A / B;
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 MathType =3D "Average"; (A + B) / 2;
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 MathType =3D "Maximum"; Max (A, B);
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 MathType =3D "Minimum"; Min (A, B)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0)
>
> This will return different calculated results depending on the text
> data in the MathType field. If MathType contains "Average" the
> Calculation will work out the average of the numbers stored in fields A
> and B.
>
> REPLY #2 - Case Statements within Calculations
> --------
> The result of a Case command can be used anywhere in a Calculation that
> the appropriate type of data is used (including as parameters for some
> other functions and Script commands).
> eg.
> =A0 =A0Sentence =A0 =A0Text result
> =A0 =A0 =A0 =3D NumApples
> =A0 =A0 =A0 =A0 & " apple"
> =A0 =A0 =A0 =A0 & Case (NumApples =3D 1; " is";
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 NumApples > 1; "s are"
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0)
> =A0 =A0 =A0 =A0 & " left."
>
> This Calculation will correct the plural of "apple" and sentence
> grammar depending on how many apples there are.
> ie.
> =A0 =A0 =A0NumApples =3D 1
> =A0 =A0 =A0Sentence = "1 apple is left."
>
> =A0 =A0 =A0NumApples =3D 5
> =A0 =A0 =A0Sentence = "5 apples are left."
>
> Another example might be adding differing tax percentages based on a
> region code.
> eg.
> =A0 =A0 =A0 AmountIncTax =A0 =A0Number Result
> =A0 =A0 =A0 =A0 = AmountExTax + Case (RegionCode =3D "A"; AmountExTax=
* 0.10;
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0RegionCode =
=3D "B"; AmountExTax * 0.15;
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0RegionCode =
=3D "C"; AmountExTax * 0.20;
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A00
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 )
>
> will add 10% tax for RegionCode "A", or 15% tax for RegionCode "B", or
> 20% tax for RegionCode "C" or add no tax for any other RegionCode.
>
> Since in this example the amount of tax is always based on the
> AmountExTax field, it could also be written as:
>
> =A0 =A0 =A0 AmountIncTax =A0 =A0Number Result
> =A0 =A0 =A0 =A0 = AmountExTax + AmountExTax * Case (RegionCode =3D "A=
"; 0.10;
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0RegionCode =3D "B"; 0.15;
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0RegionCode =3D "C"; 0.20;
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A00
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 )
>
> Either version will give the correct answer.
>
> Helpful Harry =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
> Hopefully helping harassed humans happily handle handiwork hardships =A0;o=
)
In article
> buck.matthew74@yahoo.com wrote:
>
> > FM 8.5 pro
> >
> > I have a TotalAmount field, Calculation
> >
> > Net + Tax + ShippingCost - OrderDeposit
> >
> > but I want to attach a Case function to it.
> >
> > Case (Status = "1 Ordered" ; TotalAmount; Status = "2 Invoiced";
> > TotalAmount
> > ; Status = "Shipped" ; TotalAmount; Status = "4 Paid"; " " ; Status =
> > "4 Cancelled"; " ")
> >
> > How do I do this?
>
> If those are the only possibilities:
>
> Case (Status = "4 Paid" or Status = "4 Cancelled"; " "; TotalAmount)
>
> You can replace TotalAmount with the actual "TotalAmount" calculation if
> you wish.
You should ALWAYS put extra brackets / parentheses around multiple
tests to make sure they are performed as you want them to be. In this
example:
Case ( (Status = "4 Paid) or (Status = "4 Cancelled"); " " ;
Total Amount
)
If you don't put those extras in, FileMaker may perform the test very
differently to what you expect. For example, if FileMaker was to
perform the test as
Status = ("4 Paid" or Status) = "4 Cancelled
where the bracketed piece is performed first, then you would get some
very strange results.
Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)
buck.matthew74@yahoo.com wrote:
> Thank you Vandar and Harry. Actually Vandar understood the problem and
> his solution worked. That said, is there a more elegant way to join
> two Case functions?
>
> If TotalAmount = Net + Tax + ShippingCost - OrderDeposit, when Status
> = "1 Ordered" ; TotalAmount; Status = "2 Invoiced" etc.
If I understand you correctly, you could incorporate a Let function,
declaring the variable "TotalAmount", then use the same Case function:
Let (
TotalAmount = Net + Tax + ShippingCost - OrderDeposit;
Case (Status = "4 Paid" or Status = "4 Cancelled"; " "; TotalAmount)
)