Discussions

 View Only
  • 1.  Formula Text Field

    Posted 03-14-2023 10:50

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


  • 2.  RE: Formula Text Field

    Posted 03-14-2023 15:49

    This should get you started on the syntax. It is the first two if statements. Keep adding on with the following in mind:

    1. QB evaluates if from top to bottom. As soon as it hits a true statement, it stops and outputs the result. So be sure by the time you get to your last statement, that none of the prior statements could have been true.
    2. Use ="" to evaluate if a text field is blank, use isnull or not isnull to evaluate numeric and date fields.
    3. Use the or operator for spots where you say 'at least one of the fields is...'
    4. Group your conditions in parentheses. See the higlighted parentheses below.

    If(

      [Program - HTH] = "" and isnull([RRH Match]) and [nonDHS Program]="", "Phase 1",

    ( not is null([Date of Match to Program - HTH]) or not isnull([nonDHS Date of Match to Program - Manual])) and isnull([Date Prover Changed - HTH]) and isnull(nonDHS Date Provider Assigned - MANUAL]), "Phase 2",

    .....

    )



    ------------------------------
    Michael Tamoush
    ------------------------------



  • 3.  RE: Formula Text Field

    Posted 03-15-2023 11:15

    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 1
    If (
        [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:

    // Phase
    If (
        [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
    ------------------------------



  • 4.  RE: Formula Text Field

    Posted 03-16-2023 13:30

    Thanks Brian, i would rather have the phase text to be displayed in a single field rather than 6 separate fields.



    ------------------------------
    Phelan Sanders
    ------------------------------