Forum Discussion

StaceySexton's avatar
StaceySexton
Qrew Member
2 years ago

Multi-Select Formula Field help

I have a Multi-Select field [Badge:Patient Type] that is calculated through a relationship in a separate table. The resulting field could contain one or more of 30 different bubbles (terms).

I need a formula that could look at that field and translate each term to a point value or number and add those numbers up. Example:

[Badge:Patient Type] field contains the value ED; Clinic; OP
[Specialty Points] field would contain a formula that said IF [Badge:Patient Type] contains "ED" then 3 PLUS if if contains Clinic, then 3 points, PLUS if it contains OP 1 point, etc.
RESULT would equal 7

Is this possible or am I thinking of this all wrong.

This is the formula I have so far:
Sum(If( Contains( [Badge: Patient Type], "Inpatient"), 5)+
If( Contains( [Badge: Patient Type], "PF-Hospitalist"), 2)+
If( Contains( [Badge: Patient Type], "PF-Surgery"), 2)+
If( Contains( [Badge: Patient Type], "PF-Anesthesia"), 2)+
If( Contains( [Badge: Patient Type], "OPS"), 3)+
If( Contains( [Badge: Patient Type], "GI"), 1)+
If( Contains( [Badge: Patient Type], "Cath"), 1)+
If( Contains( [Badge: Patient Type], "Pain"), 1)

If the underlined Patient types were found in the multi select field, then the answer would be 9




------------------------------
Stacey Sexton
------------------------------

2 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    2 years ago
    You are on track but need to be sure that you are dealing with a text field type and also take care to not have components of the formula which calculate to null as that will cause the whole result to be null.

    var text PT = ToText([Badge: Patient Type]);  // use a formula variable here for tidiness

    IF(Contains($PT, "Inpatient"), 5,0) // note that the "else" is zero so as not to be null
    +
    IF(Contains($PT, "PF-Hospitalist"), 2,0)
    +
    IF(Contains($PT, "PF-Surgery"), 2,0)
    +

    etc....
    etc ....

    +
    IF(Contains($PT, "Pain"), 2,0)


    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
  • PrashantMaheshw's avatar
    PrashantMaheshw
    Qrew Captain
    2 years ago
    Stacey,

    What if we stored these scores again the Patient Type itself in a table , then when you are using relationships , you can do a sum of Score directly?

    ------------------------------
    Prashant Maheshwari
    ------------------------------