Forum Discussion

NealMathews's avatar
NealMathews
Qrew Trainee
8 years ago

Numeric Value for Multi-Select Text Field Items

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. 
Ask about Quickbase functionality
Formulas and functions
  • AviSikenpore1's avatar
    AviSikenpore1
    Qrew Trainee
    8 years ago
    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
  • NealMathews's avatar
    NealMathews
    Qrew Trainee
    8 years ago
    I was trying to reduce the number of fields by combining multiple checks into one field, scan compliance and file compliance.