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<o:p></o:p>

    <o:p> </o:p>

    <o:p> </o:p>

    Display the text "Phase 1"<o:p></o:p>

    All fields are NULL <o:p></o:p>

    Program – HTH  (Text field)<o:p></o:p>

    RRH Match (Numeric Lookup)<o:p></o:p>

    nonDHS Program – Manual<o:p></o:p>

    <o:p> </o:p>

    <o:p> </o:p>

    <o:p> </o:p>

    <o:p> </o:p>

    Display the text "Phase 2"<o:p></o:p>

    At least one of the following fields is NOT NULL<o:p></o:p>

    Date of Match to Program - HTH<o:p></o:p>

    nonDHS Date of Match to Program - MANUAL<o:p></o:p>

    AND<o:p></o:p>

    All of the following fields are NULL <o:p></o:p>

    Date Provider Changed - HTH<o:p></o:p>

    nonDHS Date Provider Assigned MANUAL <o:p></o:p>

    <o:p> </o:p>

    <o:p> </o:p>

    <o:p> </o:p>

    Display the text "Phase 3"<o:p></o:p>

    At least one of the following fields is NOT NULL <o:p></o:p>

    Date Provider Changed – HTH<o:p></o:p>

    Most Recent Program Entry Date - RRH<o:p></o:p>

    nonDHS Date Provider Assigned - MANUAL is NOT NULL <o:p></o:p>

    All of the following fields are NULL <o:p></o:p>

    Intake Date - RRH<o:p></o:p>

    None of the following fields equal 'Approved' <o:p></o:p>

    Application Status - HTH<o:p></o:p>

    nonDHS Application Status - MANUAL<o:p></o:p>

    <o:p> </o:p>

    <o:p> </o:p>

    Display the text "Phase 4"<o:p></o:p>

    Application Status HTH = 'Approved' OR Intake Date - RRH is not null OR 'Application Status HTH' = 'Approved' <o:p></o:p>

    All of the following fields are NULL <o:p></o:p>

    Date Unit Viewed - HTH<o:p></o:p>

    Inspection Scheduled – RRH (date)<o:p></o:p>

    nonDHS Date Unit Approved - MANUAL<o:p></o:p>

    <o:p> </o:p>

    Display the text "Phase 5"<o:p></o:p>

    At least one of the following fields is NOT NULL  <o:p></o:p>

    Date Unit Viewed - HTH<o:p></o:p>

    Inspection Scheduled - RRH<o:p></o:p>

    nonDHS Date Unit Approved - MANUAL<o:p></o:p>

    All of the following fields are NULL <o:p></o:p>

    Lease-up Date - HTH<o:p></o:p>

    Recent Lease Start Date - RRH<o:p></o:p>

    nonDHS Lease-up Date - MANUAL<o:p></o:p>

    <o:p> </o:p>

    Display the text "Phase 6"<o:p></o:p>

    At least one of the following fields is NOT NULL <o:p></o:p>

    Lease-up Date - HTH<o:p></o:p>

    Recent Lease Start Date – RRH<o:p></o:p>

    nonDHS Lease-up Date - MANUAL<o:p></o:p>

    <o:p> </o:p>



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