Thanks Brian, i would rather have the phase text to be displayed in a single field rather than 6 separate fields.
Original Message:
Sent: 03-15-2023 11:14
From: Brian Seymour
Subject: Formula Text Field
Hey Phelan, the Case function is used when checking against a single field. Since you are checking conditions in many fields you'll want to use the If function as Michael suggested.
I'd also consider breaking the logic up into more smaller steps, as it may be easier to debug and maintain. Perhaps you could have a Formula Checkbox for each Phase?
// Phase 1If ( [nonDHS Program - Manual] = "" and [Program – HTH] = "" and [RRH Match] = 0, true, // Default to unchecked false)
Repeat for the each Phase substituting (not IsNull, IsNull, and, or, etc.) where appropriate. Then, your Phase Formula Text may be more manageable and your reports may be a bit nicer too if you include the Phase checkboxes in sequential order so you can visually scan progress. Anyway, if you take this approach, you'd end up with something like:
// PhaseIf ( [Phase 6] = true, "Phase Six", [Phase 5] = true, "Phase Five", [Phase 4] = true, "Phase Four", [Phase 3] = true, "Phase Three", [Phase 2] = true, "Phase Two", [Phase 1] = true, "Phase One", // Default to empty string "")
------------------------------
Brian Seymour
Original Message:
Sent: 03-14-2023 10:50
From: Phelan Sanders
Subject: Formula Text Field
I'd like to create one formula text field, not sure how to set up the conditions, believe it's a case condition. Below are the actual field names including dashes "-" all other fields not listed date are text fields unless noted. Will appreciate the help
Display the text "Phase 1"
All fields are NULL
Program – HTH (Text field)
RRH Match (Numeric Lookup)
nonDHS Program – Manual
Display the text "Phase 2"
At least one of the following fields is NOT NULL
Date of Match to Program - HTH
nonDHS Date of Match to Program - MANUAL
AND
All of the following fields are NULL
Date Provider Changed - HTH
nonDHS Date Provider Assigned – MANUAL
Display the text "Phase 3"
At least one of the following fields is NOT NULL
Date Provider Changed – HTH
Most Recent Program Entry Date - RRH
nonDHS Date Provider Assigned - MANUAL is NOT NULL
All of the following fields are NULL
Intake Date - RRH
None of the following fields equal 'Approved'
Application Status - HTH
nonDHS Application Status - MANUAL
Display the text "Phase 4"
Application Status – HTH = 'Approved' OR Intake Date - RRH is not null OR 'Application Status – HTH' = 'Approved'.
All of the following fields are NULL
Date Unit Viewed - HTH
Inspection Scheduled – RRH (date)
nonDHS Date Unit Approved - MANUAL
Display the text "Phase 5"
At least one of the following fields is NOT NULL
Date Unit Viewed - HTH
Inspection Scheduled - RRH
nonDHS Date Unit Approved - MANUAL
All of the following fields are NULL
Lease-up Date - HTH
Recent Lease Start Date - RRH
nonDHS Lease-up Date - MANUAL
Display the text "Phase 6"
At least one of the following fields is NOT NULL
Lease-up Date - HTH
Recent Lease Start Date – RRH
nonDHS Lease-up Date - MANUAL
------------------------------
Phelan Sanders
------------------------------