What is wrong with my conditional formula?

Drew_Nemer · ‎May 12, 2023

IF(AND(PHASE="Cancelled",NOT(Notes)),

"Input Re-Exam cancellation reason into the Notes field.",

IF(PHASE="No Request Sent",

"Input Re-Exam Packet sent date into the 1st Request field. Take the Due date from the Due field and put on letter."&"\n"&"\n"&"OR update Finalization status to "&"\""&"Cancelled"&"\""&" if the Re-Exam will no longer be occuring due to deceased HOH or concurring relocation. Explain cancellation reason in Notes field.",

IF(PHASE="1st Request",

IF(AND(IS_After({Due.},TODAY()) NOT({In-Office Date}),{In-Office?}=0), "1st Request is due on "&{Due Expressed}&". Which is "&{Time until Due Date}&" days from today."&"\n"&"\n"&"If you already received the packet, please do either of the following:"&"\n"&"A) Update the Finalization field to "&"\""&"Tenant Docs Rcvd/ONLY Waiting on 3rd Party"&"\""& "if you received all the documents and fully completed packet from Participant and you are ONLY waiting on 3rd Party verifications or a CORI/SORI."&"\n"&"B) Update the Finalization field to "&"\""&"Ready"&"\""&" if you received ABSOLUTELY everything you need from the participant and are ready to run the action in Tenmast and send out Rent Share letters."&"\n"&

"C) If this is a case where the Re-Exam needs to be cancelled, update Finalization status field to "&"\""&"Cancelled"&"\""&" and input reason in Notes field."&"\n"&

"D) If your rental assistance program uses DocMgt and the participant desires to come into the office for assistance with using the Portal, input the In-Office Date and check off In-Office? field."&"\n"&

"E) If you already received the packet back, but it is incomplete or you are missing verification from the Participant, then prepare to send out a 2nd Request in the form of a Document Request. Input what SPECIFICALLY is missing in the Pending Docs/Verifications field. THEN, input the sent date of the Document Request into the 2nd Request field. The Due Date generated will be the due date for these missing items.")

&

IF(Is_same({Due.},TODAY()),

"1st Request is due today. If you have already received a packet from the participant, please do one of the following:"

&"\n"

&

"A) Update the Finalization field to "&"\""&"Tenant Docs Rcvd/ONLY Waiting on 3rd Party"&"\""&" if you received all the documents and fully completed packet from Participant and you are ONLY waiting on 3rd Party verifications or a CORI/SORI."&"\n"&"B) Update the Finalization field to "&"\""&"Ready"&"\""&" if you received ABSOLUTELY everything you need from the participant and are ready to run the action in Tenmast and send out Rent Share letters."&"\n"&

"C) If this is a case where the Re-Exam needs to be cancelled, update Finalization status field to "&"\""&"Cancelled"&"\""&" and input reason in Notes field."&"\n"&

"D) If you have NOT received anything back from the participant by the end of the workday, prepare to send out a 2nd Request on following business day."&"\n"&

"E) If you received the packet back, but it is incomplete or you are missing verification from the Participant, then prepare to send out a 2nd Request in the form of a Document Request. List everything that is missing and needed in the Pending Docs/Verifications field. THEN, input the sent date of the Document Request into the 2nd Request field. The Due Date generated will be the due date for these missing items.")

)))

why is it still not accepting it? what is going wrong? I have done similar formulas before that have worked, but i don't know what is going wrong this time.

Ben_Young1 · ‎May 12, 2023

Hey @Drew_Nemer!

This is a wild formula.
I haven't tested this yet, but try this:

IF(
  AND(
    {PHASE} = "Cancelled",
    NOT({Notes})
  ),
  "Input Re-Exam cancellation reason into the Notes field.",
  IF(
    {PHASE} = "No Request Sent",
    'Input Re-Exam Packet sent date into the 1st Request field. Take the Due date from the Due field and put on letter.\n\nOR update Finalization status to "Cancelled" if the Re-Exam will no longer be occuring due to deceased HOH or concurring relocation. Explain cancellation reason in Notes field.',
    IF(
      {PHASE} = "1st Request",
      IF(
        AND(
          {Due.} > TODAY(),
          NOT({In-Office Date}),
          {In-Office?} = 0
        ),
        "1st Request is due on "
        & {Due Expressed} & 
        ". Which is "
        & {Time until Due Date} & 
        ' days from today.\n\nIf you already received the packet, please do either of the following:\n A) Update the Finalization field to "Tenant Docs Rcvd/ONLY Waiting on 3rd Party" if you received all the documents and fully completed packet from Participant and you are ONLY waiting on 3rd Party verifications or a CORI/SORI.\nB) Update the Finalization field to "Ready" if you received ABSOLUTELY everything you need from the participant and are ready to run the action in Tenmast and send out Rent Share letters.\nC)If this is a case where the Re-Exam needs to be cancelled, update Finalization status field to "Cancelled" and input reason in Notes field.\nD) If your rental assistance program uses DocMgt and the participant desires to come into the office for assistance with using the Portal, input the In-Office Date and check off In-Office? field.\nE) If you already received the packet back, but it is incomplete or you are missing verification from the Participant, then prepare to send out a 2nd Request in the form of a Document Request. Input what SPECIFICALLY is missing in the Pending Docs/Verifications field. THEN, input the sent date of the Document Request into the 2nd Request field. The Due Date generated will be the due date for these missing items.'
      & 
      IF(
        {Due.} = TODAY(),
        '1st Request is due today. If you have already received a packet from the participant, please do one of the following:\n\n
        A) Update the Finalization field to "Tenant Docs Rcvd/ONLY Waiting on 3rd Party" if you received all the documents and fully completed packet from Participant and you are ONLY waiting on 3rd Party verifications or a CORI/SORI.\nB) Update the Finalization field to "Ready"" if you received ABSOLUTELY everything you need from the participant and are ready to run the action in Tenmast and send out Rent Share letters.\nC) If this is a case where the Re-Exam needs to be cancelled, update Finalization status field to "Cancelled" and input reason in Notes field.\nD) If you have NOT received anything back from the participant by the end of the workday, prepare to send out a 2nd Request on following business day.\nE) If you received the packet back, but it is incomplete or you are missing verification from the Participant, then prepare to send out a 2nd Request in the form of a Document Request. List everything that is missing and needed in the Pending Docs/Verifications field. THEN, input the sent date of the Document Request into the 2nd Request field. The Due Date generated will be the due date for these missing items.'
      )
    )
  )
)

Let me know if that works.
When I get a chance, I'll test it against some actual fields.
If I beat you to it, I'll update the formula and call out the edit for you.