Help with nested if statement in formula

Hi and thanks for reading

I have a document to track where we pitch projects to two different entities. I have a field called PitchStatus that I’d like to update based on whether or not a value has been entered in one or two fields.

What I’m going for in playing English is:
If Pitch_ID is blank and Pitch_Oxy is blank, PitchStatus = “Pitch to Both”
If Pitch_ID is blank and Pitch_Oxy is not blank, PitchStatus=“Pitch to ID”
If Pitch_ID is not blank and Pitch_Oxy is blank, PitchStatus=“Pitch to Oxygen”
else PitchStatus = “Pitched”

I’ve been following along with the premium control flow course, and came up with this, but I’ve definitely messed something up:

if ID_Pitch = “”
if Oxy_Pitch=""
TestPitchStatus = “Pitch to both”
elseif ID_Pitch = “” and Oxy_Pitch <> “”
TestPitchStatus = “Pitch to ID”
elseif ID_Pitch <> “” and Oxy_Pitch = “”
TestPitchStatus = “Pitch to Oxygen”
else
TestPitchStatus = “Pitched”
endif

Thank you for having a look, and have a great weekend!

Best,
Biagio

These should be combined with an and operator like you have the others:

if Oxy_Pitch="" and ID_Pitch = “”
    TestPitchStatus = “Pitch to both”
elseif ID_Pitch = “” and Oxy_Pitch <> “”
    TestPitchStatus = “Pitch to ID”
elseif ID_Pitch <> “” and Oxy_Pitch = “”
    TestPitchStatus = “Pitch to Oxygen”
else
    TestPitchStatus = “Pitched”
endif
1 Like

If you want to apply your check not only to the active record, but to all your records, you can do this via the Fields > Morph … menu or you use this code that does the same:

Field PitchStatus
Formulafill 
    ?(emptycell(Pitch_ID) and length(Pitch_Oxy)>0,"Pitch to ID",
        ?(emptycell(Pitch_Oxy) and length(Pitch_ID)>0,"Pitch to Oxygen",
            ?(emptycell(Pitch_ID) and emptycell(Pitch_Oxy),"Pitch to both","Pitched")))
1 Like

Thanks you @gary. This seems to be what I’m looking for. However, when I enter it as follows (I made sure to change the quotes to the plain style):

if Oxy_Pitch="" and ID_Pitch = “”
TestPitchStatus = “Pitch to both”
elseif ID_Pitch = “” and Oxy_Pitch <> “”
TestPitchStatus = “Pitch to ID”
elseif ID_Pitch <> “” and Oxy_Pitch = “”
TestPitchStatus = “Pitch to Oxygen”
else
TestPitchStatus = “Pitched”
endif

The error I get is:
Execute code contains syntax error: ELSEIF does not have a matching IF statement

I’ve tried popping in a few IF’s, but haven’t gotten it to work yet. Thanks the guidance, and TIA for any ideas.

Best,
Biagio

Thanks, @KJM - looks like that worked perfectly. Now, silly newbie question: does that only work as a procedure, or can I put it somewhere in the field’s settings so every time the fields are updated it updates itself as well?

Thanks again,
Biagio

I made a new database with only your three fields and then made a procedure with the same code without any unwanted smart quotes:

if Oxy_Pitch="" and ID_Pitch = ""
    TestPitchStatus = "Pitch to both"
elseif ID_Pitch = "" and Oxy_Pitch <> ""
    TestPitchStatus = "Pitch to ID"
elseif ID_Pitch <> "" and Oxy_Pitch = ""
    TestPitchStatus = "Pitch to Oxygen"
else
    TestPitchStatus = "Pitched"
endif

It ran perfectly without any errors so you must have more involved here than just this code.

1 Like

To make it interactive, In the code pane of the fields Pitch_ID and Pitch_Oxy you would put:

PitchStatus = ?(emptycell(Pitch_ID) and length(Pitch_Oxy)>0,“Pitch to ID”, ?(emptycell(Pitch_Oxy) and length(Pitch_ID)>0,“Pitch to Oxygen”, ?(emptycell(Pitch_ID) and emptycell(Pitch_Oxy),“Pitch to both”,“Pitched”)))

In these instances, I usually use a case statement such as :

Case Oxy_Pitch="" and ID_Pitch = “”
PitchStatus = “Pitch to both”
Case ID_Pitch = “” and Oxy_Pitch <> “”
PitchStatus = “Pitch to ID”
Case ID_Pitch <> “” and Oxy_Pitch = “”
PitchStatus = “Pitch to Oxygen”
DefaultCase
PitchStatus = “Pitched”
EndCase

When you want to let this process automatically update your PitchStatus field when you enter values in Pitch_ID and/or in Pitch_Oxy, then enter similar code in (the properties of) those two fields:

PitchStatus = ?(emptycell(Pitch_ID) and length(Pitch_Oxy)>0,"Pitch to ID",
    ?(emptycell(Pitch_Oxy) and length(Pitch_ID)>0,"Pitch to Oxygen",
        ?(emptycell(Pitch_ID) and emptycell(Pitch_Oxy),"Pitch to both","Pitched")))

Thanks everyone! I was able to get each of these suggestions working in a brand new database (as @gary pointed out, I must’ve done something weird in the original DB.) Thanks for the help, and for the varied approaches, I learned a lot.

Thanks @gary, @KJM, @JeffK for sending me into the weekend on a high note :grinning:

Best,
Biagio

Even though @JokeAndBiagio has gotten everything working, I’d like to chime in late with a couple of additional points.

First of all, a reminder that if you already have a working named procedure, you can make it run automatically when data is entered by calling it in the Code panel. In other words you don’t have to put the code itself into the Code panel, you can just call it. Suppose your procedure is called UpdatePitch. In the Code panel, just put

call UpdatePitch

This is especially useful if you need to use this code for more than one field. Not only will it save typing, but if you ever have to make a change or correction, you will only have to do it in once place. If the code is lengthly, it is also easier to edit it in a procedure3 window rather than in the somewhat cramped Code panel.

My second suggestion would be to avoid using the case statement in new code. It is not as flexible as the if and elseif statements, so I always use those (the main advantage is that they can be nested if needed, while the case statement cannot). Of course for compatibility the case statement wasn’t removed when the elseif statement was added, and it never will be, so you can still use it. But there is no reason to, elseif is more flexible so I would definitely point anyone writing new code in that direction. The current help pages explain this in detail.

1 Like

Thanks @admin - I had just been pasting the code, then tweaking it in three different places. Going back to fix this in a few other places as well, thanks for the great tip.

This is only my 2nd Panorama X database, and I’ve got some tricky stuff coming up, so I’m sure I’ll be back soon :wink:

Have a great weekend!
Biagio