Numeric Value for Multi-Select Text Field Items

  • 0
  • 1
  • Question
  • Updated 10 months ago
  • In Progress
I have a Multi-Select Text Field with different options, I'm trying to assign each of those different options a numeric value in a Formula-Numeric Field. 

For For Example I have a multi-select text field called E1. 
With Different Options: 
FILE-Compliant
FILE-NOT Compliant
FILE-N/A
SCAN-Compliant
SCAN-NOT Compliant
SCAN-N/A


I'm trying to find the percentage of compliance for the items in field E1. 
I was going to essential create 3 different formula numeric fields. 

Formula Numeric Field 1=(Counts up all items selected in E1, except those with "N/A")
Formula Numeric Field 2=(Sums all of the "Values" that were assigned to the options in E1) 
IE: FILE-Compliant=1, FILE-NOT Compliant=0, FILE-N/A=null

Then Formula Numeric Field 3=(This field would be FNF2/FNF1) Giving me a % of compliance for Field E1. 
Photo of Neal

Neal

  • 100 Points 100 badge 2x thumb
  • frustrated.

Posted 10 months ago

  • 0
  • 1
Photo of Avi Sikenpore

Avi Sikenpore

  • 532 Points 500 badge 2x thumb
Neal,
Why are you using a multi select field. Is it possible for a file to be compliant and not compliant at the same time ?

You want to use Quickbase to error proof the data entry. 

Here is what I would suggest :  Create two multple choice fields  - File Compliance, Scan Compliance. Provide 3 choices - Compliant, Not Compliant, NA. Then use a 3rd field using a case statement such as : 


formula numeric field - Percent Compliant

var num maxScore = case([File Compliance],
"NA",0,1) + 
case([Scan Compliance], 
"NA",0,1) ;

var num actualScore = case([File Compliance], 
"Compliant",1,0) +  
case([Scan Compliance], 
"Compliant",1,0)  ; 


$actualScore / $maxScore
Photo of Neal

Neal

  • 100 Points 100 badge 2x thumb
I was trying to reduce the number of fields by combining multiple checks into one field, scan compliance and file compliance.