Discussions

Expand all | Collapse all

Creating a report of "what's missing".

  • 1.  Creating a report of "what's missing".

    Posted 13 days ago
    Howdy,
    I am a relatively new builder. I've built some fairly simple apps.  I am working on one now for our new student employee on boarding process. We have several documents that we are required to collect for the personnel file. They do not all come in at the same time.  I have fields where we can note when each item has been received. Currently, they are date fields, but perhaps would be better as checkboxes? I also have a field that identifies the hiring manager for each new employee.  What I need to do is to write a report that shows what documents are still missing.  I'm not quite sure how to write that filter so that for any new employee record it shows what documents have NOT yet been collected (ie: either no date or not checked - ideally all on the same line in the report IE I don't want two lines for one employee if they are missing both an application and a signed position description. I want one line that shows both of these things missing.
    The report should be similar to below - 
    REPORT OF MISSING ITEMS
    Last Name, First Name UIN hiring manager application signed PD CBC form dual Employment training cert
    Bunny, Buggs 12345678 Daffy Duck X X

    Thanks in advance for your help and suggestions!

    ------------------------------
    Andrea Johannes
    ------------------------------


  • 2.  RE: Creating a report of "what's missing".

    Posted 13 days ago
    I would build a single-formula-text field that lists the items which are missing (based on your Date fields) - I would not convert your date fields to checkboxes for this type of purpose.  You can make this a formula-rich-text field and use colorization to make it stand out; but as you are new to Quick Base, the format below is something you can build-from.
    //First we define the output text if an item is missing
    var text application=If(IsNull([Application]),"Application","");
    var text signedPD=If(IsNull([Signed PD]),"Signed PD","");
    var text cbcForm=If(IsNull([CBC Form]),"CBC Form","");
    var text dualEmployment=If(IsNull([Dual Employment]),"Dual Employment","");
    var text trainingCert=If(IsNull([Training Cert]),"Training Cert","");
    
    //Next we define if there are missing items
    var bool missingItems=$application <>"" or $signedPD <>"" or $cbcForm <>"" or $dualEmployment <>"" or $trainingCert <>"";
    
    //Next we define an optional text value to describe what the items are that will list
    var text prefix=If($missingItems=true,"Missing Items: ","");
    
    //Now we list all the "missing" items in a simple-list formula
    List("\n",
    $missing,
    $application,
    $signedPD,
    $cbcForm,
    $dualEmployment,
    $trainingCert
    )​

    Then add this formula-text field to your report with the Hiring Manager as a separate column against your list of employees.



    ------------------------------
    Laura Thacker (IDS)
    laura@intelligentdbs.com
    (626) 771 0454
    ------------------------------



  • 3.  RE: Creating a report of "what's missing".

    Posted 12 days ago
    Thank you!  I will try this - it probably would be easier for my users to see a list in one column per employee than the way I had it in excel.  


    ------------------------------
    Andrea Johannes
    ------------------------------



  • 4.  RE: Creating a report of "what's missing".

    Posted 12 days ago
    Andrea,
    In my opinion it is easier for users to read 1-column of data than to scan their eyes across multiple columns to get the same information.  If you do, however, prefer the checkbox-system; then @Babi Panjikar's solution is equally valid.  I always try to display more in less columns so that users are not forced to scroll excessively.  You should be able to copy and paste that formula and then just correct the field names.


    ------------------------------
    Laura Thacker (IDS)
    laura@intelligentdbs.com
    (626) 771 0454
    ------------------------------



  • 5.  RE: Creating a report of "what's missing".

    Posted 12 days ago
    Laura's solution is great if you want the missing status in single column. However, to your need, you can create formula text field or formula checkbox (if you want to see as Checked) for each of the item header and then write check formulas.

    ------------------------------
    Babi Panjikar
    ------------------------------