# Requesting Assistance with Formula for Status Field

Topic Labels: Formulas
Solved
1625 7
cancel
Showing results for
Did you mean:
4 - Data Explorer

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.

1 Solution

Accepted Solutions
11 - Venus

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",
{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(
"Complete"
)
)``````
7 Replies 7
11 - Venus

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",
{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(
"Complete"
)
)``````
4 - Data Explorer

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.

11 - Venus

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.

4 - Data Explorer

``````IF(
AND(
{State} = "Florida",
{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(
"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.​``````

11 - Venus

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.

4 - Data Explorer

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

4 - Data Explorer

Thank you, Ben; I got it to work.