Formula discriminates between outputs

[Kelsey-]

Hi all - I'm having a very strange issue with a Formula field. I've got a Status field (single select) that informs a Formula field. i.e. if the Status field says ABC, then make the Formula field say XYZ. It's working perfectly except for two outputs. Now here's where it gets weird: after extensive testing, the issue seems to be with the output string, not the input. 

• If the Status is Booking Received, the Formula field should return "Booking received, waiting for pickup confirmation" but it returns a blank field. I also can't get it to return any results when I change the formula to return my name, the word Test, or gibberish lettering. 
• But when I type one of the other results that is working for another status, it works! Meaning if I type "Decline" in to the formula in place of "Booking received, waiting for pickup" it returns Declined. So weird, right?? Why will it only return certain other existing outputs?
• The same thing is happening for the "On hold" result. It will not return the words "On hold" or any random string I test it with, but it will return one of the other existing statuses.

IF(

    OR(

        {Latest CSF Status} = "Submitted - container cleared",

        {Latest CSF Status} = "Closed - lost possession of container"

    ),

    "Shipment & CSF complete",

    IF(

        OR(

            Status = "(SH) Post-load sent",

            Status = "(SHI) Invoice pulled",

            Status = "(SHIPPED) Docs sent"

        ),

        "Container loaded - waiting for CSF",

        IF(

            Status = "Tentative",

            "Waiting for booking & pickup confirmation",

            IF(

                Status = "Booking received",

                "Booking received, waiting for pickup confirmation",

                IF(

                    Status = "Pickup confirmed",

                    "Booking received & pickup confirmed",

                    IF(

                        OR(

                            Status = "Declined",

                            Status = "Released",

                            Status = "Cancelled"

                        ),

                        "Declined",

                        IF(

                            Status = "Hold",

                            "On Hold",

                            ""

                        )

                    )

                )

            )

        )

    )

)

Thanks for any help!

Kelsey

[TheTimeSavingCo]

Perhaps you could try using "&" to concatenate the IFs instead of nesting them, e.g.

IF(
    OR(
        {Latest CSF Status} = "Submitted - container cleared",
        {Latest CSF Status} = "Closed - lost possession of container"
    ),
    "Shipment & CSF complete"
) &

IF(
    OR(
        Status = "(SH) Post-load sent",
        Status = "(SHI) Invoice pulled",
        Status = "(SHIPPED) Docs sent"
    ),
    "Container loaded - waiting for CSF"
) etc etc

That would simplify the formula and would surface any logic issues quickly

If that doesn’t resolve the issue, could you share a copy of your base without any records in it? This would allow others to troubleshoot more effectively, as the fields and options from scratch would be quite challenging

[kuovonne]

Have you checked the configuration of the single-select field? Could there be a hidden space at the beginning or end of the choice name?

I also suggest using a SWITCH() function.

SWITCH( {Latest CSF Status},
  "Submitted - container cleared", "Shipment & CSF complete",
  "Closed - lost possession of container", "Shipment & CSF complete",
SWITCH( {Status},
  "(SH) Post-load sent", "Container loaded - waiting for CSF",
  "(SHI) Invoice pulled", "Container loaded - waiting for CSF",
  "(SHIPPED) Docs sent", "Container loaded - waiting for CSF"
  "Tentative", "Waiting for booking & pickup confirmation",
  "Booking received", "Booking received, waiting for pickup confirmation",
  "Pickup confirmed", "Booking received & pickup confirmed",
  "Declined", "Declined",
  "Released", "Declined",
  "Cancelled", "Declined",
  "Hold", "On Hold"
))

 

[TheTimeSavingCo]

Oooh, I never thought of putting another SWITCH inside the default like that, that's neat!  Thanks for that!

[kuovonne]

---

@TheTimeSavingCo wrote:

Oooh, I never thought of putting another SWITCH inside the default like that, that's neat!  Thanks for that!

---

You're welcome. I format the nested SWITCH() the same way I format nested IF() functions. In these cases, I find less indenting to be easier to read.

[Kelsey-]

Thank you for your input! I tried both alternative formulas and unfortunately neither one returns different results than the original formula (and I've also checked the Status field for extra spaces, etc). Here's a simplified version of the problem using the SWITCH formula:

• This formula returns no results: 
  • SWITCH( {Status},
    "Booking received", "Booking received, waiting for pickup confirmation"
    )

• While this formula works perfectly, but it's useless because it's successfully returning the wrong result:
  • SWITCH( {Status},
    "Booking received", "Booking received & pickup confirmed"
    )

It's as if there's a list of acceptable outputs somewhere, and two of my outputs ("Booking received, waiting for pickup confirmation" and "On hold") do not meet the requirements for some reason. But I cannot figure out why.

Is there a way I can share a copy of the base without having to make you editors? 

Thanks for your attention on this! I'm stumped. 

[kuovonne]

Do you have your formula field set up to format the result as a single-select? If so, you must manually add the different possible values that the formula can output to the configuration of the field on the formatting tab.

It is possible to provide a read-only copy of the base, but that would not provide access to the configuration of the field. 

A better alternative is to post screenshots of the field configurations and the sample data.

[TheTimeSavingCo]

re: Is there a way I can share a copy of the base without having to make you editors? 

@Kelsey-   Yeap check out this link: https://support.airtable.com/docs/creating-airtable-base-share-links#creating-configuring-base-share...

And you would need to toggle on the "Allow viewers to copy data out of the base" option

Please only do this with a duplicated copy of your base with no production data in it though! 

---
re: It is possible to provide a read-only copy of the base, but that would not provide access to the configuration of the field.

@kuovonne  Hmm I think this works for field configurations, but not for automations.  Here's a public shared base link, and once we copy that base to our own workspace we have access to the field configs: https://airtable.com/appOYfSvD3zaCSjy2/shrfKDwbePrq3wMVT

For automations we need to actually be collaborators though I believe

[kuovonne]

Ah yes, if the external user can make a copy of the base with read-only access, the user would have access to the formulas in the copy. 

If Kelsey goes this route, I recommend having enough sample data to reproduce/demonstrate the issue, even if actual production data is removed. However, I'd like to hear if the formula field is formatted to output the result as a single-select first. I think that is the most likely case given what has been posted so far.

[Kelsey-]

Okay does this link work? I scrubbed the data. The issue is in the Shipments table - you'll see the relevant fields in the Sandbox view. The issue is occurring in the Next Status Action field. I've also created a TEST Formula Field to play around in - that field will get deleted from my base once I figure this problem out. And yes @kuovonne, the formula field is formatted to return as a single-select field. Thanks for taking a look! 

https://airtable.com/appVv8xANNwLYqc7c/shrI7l9K8FYJhz3NW