Quick Base Discussions

Expand all | Collapse all

Numeric Value for Multi-Select Text Field Items

  • 1.  Numeric Value for Multi-Select Text Field Items

    Posted 01-21-2018 23:32
    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. 


  • 2.  RE: Numeric Value for Multi-Select Text Field Items

    Posted 01-22-2018 01:13
    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


  • 3.  RE: Numeric Value for Multi-Select Text Field Items

    Posted 01-22-2018 02:07
    I was trying to reduce the number of fields by combining multiple checks into one field, scan compliance and file compliance.