Multi-Select Formula Field help

Question

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

MarkShnierYou · Answer

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

Sign up

Login to the community