Stringing together multiple if statements?

[Moon Rabbit]

I can't for the life of me figure out how to do this: I have a field (Tests Requested) that contains carriage-return separated values based on a checkbox set. I figured out how to search for a specific value and set a second field (Order Total) based on the price assigned to that value in a related table. The problem is, I can't figure out how to do it more than once in a calculation. I get an error when I try to put more than one if statement in a calculation. I tried using "&" to string if statements together, but then I get a value 10X the expected value, and if I check more than one checkbox, it multiples 10X again! How do you string together if statements?

Order Total=

If (PatternCount( "¶" & Tests Requested & "¶"; "¶Purity¶")=1; + Crops::Purity Price;+0) &

If (PatternCount( "¶" & Tests Requested & "¶"; "¶Germination¶")=1; + Crops::Germ Price; +0)

For example, when the "Purity" checkbox is checked, the expected value for a particular record is 35.00. The value displayed is 350.00. If "Germination" is selected, the expected value displays correctly. If both are checked, the total displayed is (expected Purity X 100) + expected Germination value.

[LaRetta]

Well, try something like this (result is number)

Min ( 1 ; PatternCount ( TestsRequested ; "Purity" ) ) * Crops::PurityPrice +

Min ( 1 ; PatternCount ( TestsRequested ; "Germination" ) ) * Crops::GermPrice

What you are requesting seems a bit unclear so if this isn't it, tell me how I need to adjust it.

[eos]

Or do it like this:

Order Total=

If ( PatternCount ( Tests Requested ; "Purity" ) ; Crops::Purity Price ; 0 ) + If ( PatternCount ( Tests Requested ; "Germination" ) ; Crops::Germ Price ; 0 )

The key is that you don't add within the If statements (and then tie the results with the & operator...), but rather you add the results of each single statement.

Cheers

eos

[LaRetta]

Well done.

I was thinking of multiline (and was counting for multiple-same values once). But, of course, value lists will only contain ONE of each value. :wink2:

[FileMaker Magazine]

Your first problem is searching for a string within a checkbox field, (just another text field) using paragraph returns as delimiters, (which is good programming) won't work because there is no starting return and no trailing return in the field data. So you have to "pad" the checkbox field with those values - at both the start and end.

Your next problem is the math. You just need to add one result to the next. You don't need to use the ampersand (concatenation) because you're not working with strings of text. All you need is the plus sign.

This is what you are looking for...

Let(

[

_tests_field = ¶ & tests_request & ¶;

_purity_test_price = 35;

_germination_test_price = 35;

_purity_test_pass = PatternCount ( _tests_field ; "¶Purity¶" );

_germination_test_pass = PatternCount ( _tests_field ; "¶Germination¶" )

];



Case( _purity_test_pass; _purity_test_price ) +

Case( _germination_test_pass; _germination_test_price )



)





/*

Older calc



If (PatternCount( "¶" & tests_request & "¶"; "¶Purity¶")=1; + 35;+0) &

If (PatternCount( "¶" & tests_request & "¶"; "¶Germination¶")=1; + 35; +0)



*/

[comment]

We only see a small portion of the whole here, but it seems the REAL problem here is not the calculation, but the data structure. At minimum, I would break the test prices into individual records. Then the total would be simply:

Sum ( TestPrices::Price )

The checkbox field too would probably benefit by being replaced by a related lineitems type table.

[Søren Dyhr]

I tend to agree with this last point made, and especially now when you're able to disguise the record ID in a valuelist.

Please observe that users of previous versions can't use this feature.

--sd

Pling.zip