Need help with IF formula

[Geoffrey_Badner]

I run a photo studio and need help with a formula that I use to keep track of the status of documents clients are to fill out and return to me as part of their booking.

In my table I have…

4 single select columns:

• Contract
• Credit Card
• COI
• COVID

Each has the following options:

• Not Sent
• Sent
• Received
• Not Needed

I want to create a “Document Status” formula column that returns 0 if any combination of the single selects are set to “Not Sent” or “Sent”. If none of those options are selected, the formula should return 1. From there I’ll create a filtered view of my table to only show me bookings with a document status of 0.

In other words, only show me bookings who have not been sent or have not returned their documents.

I had all of this working, but recently added the “not needed” option and it’s screwed everything up.

Hope that makes sense. Thanks!!

[augmented]

Hi. You could use something like this, if you meant that if any of the single selects is ‘Sent’ or ‘Not Sent’, then 0. If you meant that all of them had to be either of the two, then change the OR to AND.

IF(OR(FIND(‘Sent’,{Contract})>0,FIND(‘Sent’,{Credit Card})>0,FIND(‘Sent’,{COI})>0,FIND(‘Sent’,{COVID})>0),0,1)

I think it should work. If not, let me know.

[Traci_Franssen]

Geoffrey_Badner:

In other words, only show me bookings who have not been sent or have not returned their documents.

You could also achieve this by using or filters for the View.

[kuovonne]

There are many ways to accomplish what you want. Both of the options already presented look like solid solutions. Here is another possible solution.

I recommend that you use whatever solution you thing will be easiest to maintain and adapt if and when your needs change.

IF(
  OR(
    {Contract} = "Not Sent",
    {Contract} = "Sent",
    {Credit Card} = "Not Sent",
    {Credit Card} = "Sent",
    {COI} = "Not Sent",
    {COI} = "Sent",
    {COVID} = "Not Sent",
    {COVID} = "Sent"
  ),
  0,
  1
)