Creating a report of "what's missing".

Question

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

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

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

Sign up

Login to the community