Requesting Assistance with Formula for Status Field

[cipherhunter]

Requesting assistance with the following formula I am unable to get to function properly.

IF(AND({Proof of Service (Differential - Proof of Service+(x)Days)}<=20, {Document Received}=BLANK(),"Pending")

(IF(AND({Proof of Service (Differential - Proof of Service+(x)Days)}>20, {Proof of Service (Differential - Proof of Service+(x)Days)}<50, {Document Received}=BLANK(),"Delinquent"),

(IF(AND({Proof of Service (Differential - Proof of Service+(x)Days)}>50, {Proof of Service (Differential - Proof of Service+(x)Days)}<60, {Document Received}=BLANK(),"Submit Complaint to Review Board"))))))

Objective:

If (Differential - Proof of Service+(x)Days)}<=20 and State="Florida" and {Document Received} attachment field is empty, fill the formula field with "Pending Document"

If (Differential - Proof of Service+(x)Days)>20 but <50 and State="Florida" and {Documents Received} attachment field is empty, fill the formula field with "Delinquent Document"

If (Differential - Proof of Service+(x)Days)>50 but <60 and State="Florida" and {Documents Received} attachment field is empty, fill the formula field with "Submit Complaint to Review Board"

If (Differential - Proof of Service+(x)Days)>60 and State="Florida" and {Documents Received} attachment field is empty, fill the formula field with "Document Failure"

If {Document Received} attachment field has attachment = "Complete"

Any assistance is greatly appreciated with getting this formula to work that will produce a Status Field.

[Ben_Young1 · Accepted solution]

Hey @cipherhunter! 

Don't have time to test this right now, but give this a shot and lemme know if this works for you:

IF(
    AND(
        {State} = "Florida",
        NOT({Document Received}),
        {Proof of Service (Differential - Proof of Service+(x)Days)}
    ),
    IF(
        {Proof of Service (Differential - Proof of Service+(x)Days)} <= 20,
        "Pending Document",
        IF(
            AND(
                {Proof of Service (Differential - Proof of Service+(x)Days)} > 20,
                {Proof of Service (Differential - Proof of Service+(x)Days)} < 50
            ),
            "Delinquent Document",
            IF(
                AND(
                    {Proof of Service (Differential - Proof of Service+(x)Days)} > 50,
                    {Proof of Service (Differential - Proof of Service+(x)Days)} < 60
                ),
                "Submit Complaint to Review Board",
                IF(
                    {Proof of Service (Differential - Proof of Service+(x)Days)} < 60,
                    "Document Failure"
                )
            )
        )
    ),
    IF(
        {Docuemnt Received},
        "Complete"
    )
)

[cipherhunter]

Thank you for your response.  (Document Received) is an attachment field monitoring if a document has been provided if an attachment is present status is complete.  I tried the formula above, and did not work.  Does that (Document Received) being an attachment field alter the formula?  I really appreciate your help.

[Ben_Young1]

I’m currently away from a computer, so I can’t test it out myself, so any additional information you can provide about the error would be helpful!

Regarding the field type: no, in this specific scenario, the attachment field type shouldn’t have an effect on whether the formula works or not. The formula simply checks to see if the attachment field returns any data, regardless of the underlying data type.

I would recommend checking the formula syntax to make sure I didn’t forget a comma or misplace a parentheses somewhere. 

[cipherhunter]

IF(
    AND(
        {State} = "Florida",
        NOT({Document Received Formula} = 'Yes'),
        {Proof of Service (Differential - Proof of Service+(x)Days)}
    ),
    IF(
        {Proof of Service (Differential - Proof of Service+(x)Days)} <= 20,
        "Pending Document",
        IF(
            AND(
                {Proof of Service (Differential - Proof of Service+(x)Days)} > 20,
                {Proof of Service (Differential - Proof of Service+(x)Days)} < 50
            ),
            "Delinquent Document",
            IF(
                AND(
                    {Proof of Service (Differential - Proof of Service+(x)Days)} > 50,
                    {Proof of Service (Differential - Proof of Service+(x)Days)} < 60
                ),
                "Submit Complaint to Review Board",
                IF(
                    {Proof of Service (Differential - Proof of Service+(x)Days)} < 60,
                    "Document Failure"
                )
            )
        )
    ),
    IF(
        {Docuemnt Received Formula},
        "Complete"
    )
)

I added a {Docuemnt Received Formula} that provides a yes or no on whether the Document Received field has a document in its attachment field.  If the attachment field has an attachment(s) status should change to Complete.  Still not able to get the formula to work.  Any Assistance is greatly appreciated.​

[Ben_Young1]

Is {Proof of Service (Differential - Proof of Service+(x)Days)} a single field, or are you trying to reference multiple fields here?

If you're trying to reference multiple fields here, then that would cause an error.
Again, any additional information that we could use to troubleshoot this for you would be greatly appreciated.

[cipherhunter]

It is one field that produces a number value showing the number of days since the proof of delivery date.

[cipherhunter]

Thank you, Ben; I got it to work.