Skip to main content

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

 

 

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

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

 

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

 


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

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


@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.

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. 

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.

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. 


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-links-in-airtable

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

 

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-links-in-airtable

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

 

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.

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

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


Thanks for the link! 

Yeap, the formula is set to output:               Booking received, waiting for pickup confirmation
And the formatting option is set to output: Booking received - waiting for pickup confirmation

Because the formula output doesn't match the formatting option, it just defaults to blank.  Once you update all your formatting options to make sure they match your formula outputs it should work fine!

Omg. I feel. So dumb. How did I miss that?? I was completely overthinking it. 

Thank you for pointing out the obvious! My forehead hurts from facepalming so hard. Let's never speak of this again lol

Reply


Terms & Conditions