Jump to content

Stringing together multiple if statements?

This topic is 5960 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

Well, try something like this (result is number):o

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.

Link to comment
Share on other sites

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.



Link to comment
Share on other sites

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:

Link to comment
Share on other sites

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... :o



_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)


Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 5960 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.