I have a calculation field ("Action") in which I need to look at two other fields ("ID" and ID2") and determine if they are both blank (return "ADD") or 'not blank and match' (return "UPDATE") or only one field is blank (return "UPDATE")  ... the CASE function is not working properly ... the calculation is not discerning between two fields being blank and two fields being non-blank and matching ...  is there another function I should be using?  This seems to be a fairly basic query .. ?

Thanks in advance!

L.

Why don't you post the Case function syntax that you have?

 

Sounds like the Case() is perfect for this.

the CASE function is not working properly ... the calculation is not discerning between two fields being blank and two fields being non-blank and matching ...

 

Case() is working just fine – don't forget that you need to spell out exactly what you need, and if both fields are blank, they match alright …

Let ( [
  one = ID ;
  two = ID2 ;
  cnt = Count ( one ; two )
  ] ;
  Case (
    not cnt ; "ADD" ; // 'both blank'
    one = two or cnt = 1 ; "UPDATE" // 'not blank and match' (return "UPDATE") or only one field is blank (return "UPDATE") 
  )
)

For the calculation to work, you need to uncheck the 'Do not evaluate if all referenced fields are empty' option at the bottom of the Edit Calculation dialog.

 

both blank (return "ADD")

'not blank and match' (return "UPDATE")

only one field is blank (return "UPDATE")

 

Are you sure about 2 and 3 both returning the same result?

 

This seems to be a fairly basic query .. ?

 

Yes, but you are a 'Beginner', and thus forgiven …

Thanks eos:)

 

I unchecked the box that I had overlooked and it still didn't work ... here is the query (time to bear my soul:) )  :

 

Case((IsEmpty(accounts:ID) = IsEmpty(accounts 2:ID));"ADD";(not IsEmpty(accounts:ID) = not IsEmpty(accounts 2:ID));"GOOD")

 

 

Thanks!

L.

I unchecked the box that I had overlooked and it still didn't work ... 

 

I meant you need to uncheck that box for MY calculation to work. Did you try that?

 

 (time to bear my soul:) ) 

 

http://en.wikipedia.org/wiki/Wikipedia:Lists_of_common_misspellings/Homophones

eos, it's early for me adn you are cruel:)  Correct, but cruel!

L.

Thanks All!  Eos, your script worked perfectly and allowed me to customize it further!

Have a grate day ( yeah, I know),

L.

<p><strong>Automatic message</strong><br />
<br />
This topic has been moved from &quot;User Group Central - Sponsored by FMPug.com&quot; to &quot;Calculation Engine (Define Fields)&quot;.</p>
<p>&nbsp;</p>
<p>The&nbsp;User Group Central - Sponsored by FMPug.com is restricted to announcements from the User Group, not for asking question on How-To accomplish something.</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>

 

This wonderful look happened by the software. I reported this to Admin.

The tests in plain English are (I'm also not sure why the last two have the same result, but I'll leave it as the three tests):

• If ID is blank and ID2 is blank, then return "ADD"
• If ID is not blank and ID2 is not blank and ID = ID2, then return "UPDATE"
• If ID is blank or ID2 is blank, then return "UPDATE"

which gives logic tests of:

• (IsEmpty(accounts:ID)) and (IsEmpty(accounts:ID2)); "ADD"
• (not(IsEmpty(accounts:ID))) and (not(IsEmpty(accounts:ID2))) and (accounts:ID = accounts:ID2); "UPDATE"
• (IsEmpty(accounts:ID)) or (IsEmpty(accounts:ID2)); "UPDATE"

So the final Case statement would be:

Case(
     (IsEmpty(accounts:ID)) and (IsEmpty(accounts:ID2)); "ADD";
     (not(IsEmpty(accounts:ID))) and (not(IsEmpty(accounts:ID2))) and (accounts:ID = accounts:ID2); "UPDATE";
     (IsEmpty(accounts:ID)) or (IsEmpty(accounts:ID2)); "UPDATE"
    )

Not as elegant as eos's answer, but hopefully easier to see what's going on.

I think the problem with your calculation is due to using the "=" symbols instead of the logical "and" and "not" functions.

For example, your first test said:

IsEmpty(accounts:ID) = IsEmpty(accounts 2:ID)

So:

• if both fields are empty, then you get "True = True", which is true, so the Case staement returns "ADD"
• if both fields are NOT empty, then you get "False" = "False", which is true, so the Case statement returns "ADD"
• if only one field is empty, then you get "False" = "True" or "True" = "False", so the Case staement moves on to the next test.

The second result there is wrong for what you're trying to achieve and would give "incorrect" results.

Thanks Harry, that does translate it well to the FMP process ... I can also see from your example that I was not using proper syntax with respect to the 'and' & 'or' operators ... again, very helpful!

L.