Creating a report of "what's missing".

Question

Howdy,I am a relatively new builder. I've built some fairly simple apps.&nbsp; 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.&nbsp; 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.&nbsp; What I need to do is to write a report that shows what documents are still missing.&nbsp; 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 -&nbsp;

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

laurathacker · Answer

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.&nbsp; 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","");

//Next we define if there are missing items
var bool missingItems=$application &lt;&gt;"" or $signedPD &lt;&gt;"" or $cbcForm &lt;&gt;"" or $dualEmployment &lt;&gt;"" or $trainingCert &lt;&gt;"";

//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("
",
$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------------------------------

babipanjikar · Answer

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

andreajohannes · Answer

Thank you!&nbsp; 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.&nbsp;&nbsp;------------------------------Andrea Johannes------------------------------

laurathacker · Answer

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.&nbsp; If you do, however, prefer the checkbox-system; then @Babi Panjikar's solution is equally valid.&nbsp; I always try to display more in less columns so that users are not forced to scroll excessively.&nbsp; 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------------------------------

Forum Discussion

Creating a report of "what's missing".

Related Content

AI Smart Builder for Creating Apps and Streamlining Pipelines

create pipeline to create new records based on current record

Add button to create record in different app

Create a QR to outside Website or App

Trying to Create a Attendance Checklist

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