Solved

Requesting Assistance with Formula for Status Field

Forum|Forum|2 years ago
June 11, 2023
7 replies
41 views

cipherhunter
Participating Frequently

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.

Best answer by Ben_Young1

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

+22

Ben_Young1
Brainy
Answer
Forum|Forum|2 years ago
June 11, 2023

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
Author
Participating Frequently
Forum|Forum|2 years ago
June 11, 2023

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

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.

+22

Ben_Young1
Brainy
Forum|Forum|2 years ago
June 11, 2023

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
Author
Participating Frequently
Forum|Forum|2 years ago
June 11, 2023

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.

+22

Ben_Young1
Brainy
Forum|Forum|2 years ago
June 11, 2023

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.

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
Author
Participating Frequently
Forum|Forum|2 years ago
June 11, 2023

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

cipherhunter
Author
Participating Frequently
Forum|Forum|2 years ago
June 11, 2023

Thank you, Ben; I got it to work.

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded