If statement help

1

I’m trying to write a column formula for a column called “Case Status” where the Case Status will say “Scheduled” ONLY if the “Test Date” column for that row has been filled out (is not blank) AND if the case status has not already been assigned. I tried a few different If statements but I keep getting error messages. Can anyone help?

Case status

2

@Joanna_Lamb : would you mind sharing what you already tried and/or the error you get ? :blush:

1 Like
3

I honestly don’t really remember. I used some examples of things others had posted in previous posts and tried replacing it with my own column names but I was really just shooting in the dark as I’m not great with the syntax of the formulas and knowing what order the elements need to be place. I’ve very much a novice with this type of language.

4

No worries @Joanna_Lamb :blush: , we’re all here to learn :wink: .

Re-reading your question though, what do you mean by this :point_down: :innocent:

In other words, how are your case status assigned in your table ? :blush:

6

I mean that I want it only to do this if there is nothing else already written in that column - i.e. the cell is blank.

For example, if the status is “Complete” I don’t want this to override that. Does that make sense?

Thanks so much for your help!!

7

Hi @Joanna_Lamb , yes it makes sense :blush: but it doesn’t work this way :blush: .

The If() formula you’re trying to write will define what appears in the whole field (from the first row to the last) and that field will not be manually editable anymore.

You can’t mix manual entry (the potential “Complete” status already in there) and add on the top a formula to fill out the blanks in the same column :confused: .

There might be work-around though :blush: .
(But I’m not really in front of my computer right now, so it’s a bit hard for me to go further at this moment :confused: )

8

No worries, I really appreciate you taking the time to help. I thought maybe there was a way to filter it out but I hear what you’re saying. Thanks again for all your help

9

No problem @Joanna_Lamb :blush: ! We’re also all here to help each others :wink: .

That would create a “circular reference”… which is probably one of the error you got :blush: .
To work around this, you just need a way to “externalise” that “Complete” status you already have so it can be used in a formula without creating a “circular reference” :blush: .

I think, given your screenshot, the easiest way would be to convert that field into a Select List (Coda will automatically retrieve the various values already in there, keep them as Selectable items and leave them as they are), then add another field to actually display either Scheduled (if the Date is not blank) or Complete (if Case Status is Complete) and use that new field to group your table :blush: .
This would require you to manually mark each case as Complete in the future.

You could, of course, leave your original Case Status as a Text field :wink: (but you would need to type Complete where and when needed :blush: ). You could also use a checkbox to mark your cases as Complete …

To illustrate, here is a quick sample :blush: .

The first tables use a nested If() (Cases Status - 1 & Cases Status - 2)

If(
  thisRow.[Case Status (Original)].Contains("Complete"),
  "Complete",
  If(
    thisRow.[Test Date].IsNotBlank(),
    "Scheduled",
    ""
  )
)

Which kind of says :

  • If the value in the field [Case Status (Original)] is equal/contains "Complete" then “write” Complete

Otherwise

  • if the value in this row [Test Date] is not blank, then “write” Scheduled

Otherwise (i.e.: in any other cases)

  • “write” Blank ("")

You can get to that same result by using a SwitchIf() which works similarly to If() except you don’t need to precise an If false/Otherwise in the formula, only the value the formula should return if the condition is true :blush:.

I used it in the last tables :blush: (Cases Status - 3 & Cases Status - 4)

SwitchIf(
  thisRow.[Case Status (Original)].Contains("Complete"),
  "Complete",
  thisRow.[Test Date].IsNotBlank(),
  "Scheduled" 
)

But everything here depends on the future of your actual doc (How big it might get, it’s purpose, etc…). These are just potential leads :innocent:

10

Thanks I will play around with this!

1 Like
11

Don’t hesitate to get back at this post, if you need more info or some precision :wink: !