Tuesday, April 20, 2010

Calculated Fields in SmartList Builder for Reminders

One of the most underutilized features in SmartList Builder is the Calculated Fields functionality.  This allows the user to create new fields on the fly based on other fields and calculations as if they were part of the database which can be the basis of reminders, data dumps to excel, etc.

A particularly useful attribute of this is for date fields.  Anniversary dates, Birthdates, Expiration Dates can all be customized to meet various needs. 

In this example, we are using the Licenses and Certifications Table from a SmartList Builder Item and creating a Reminder that will always let the user know of any Licesnses or Certifications that are expiring in the next 30 days.

Rolling Dates: Licenses and Certifications Rolling 30 day period

1. Open SmartList Builder by going to Tools>>SmartList Builder>>SmartList Builder
2. Create Licenses and Certification SL Builder Item based on below table:

3. After Saving this choose the Calculations Button on the main SmartList Builder Screen and Click the + sign from the main Calculated Fields Screen.

4. Create the Calculated Field below by expanding the TW_UPR_MSTR_License_Cert table and then double clicking the Expiration Date field highlighted below.  In the Calculation Box on the left hand side, manually type in "-30" (minus 30) after the date field as show below.  Make sure you type in an appropriate field name and the Field Type will be Date.

5.  This has created a brand new field called "Expiring Minus 30" that can now be used like any other SmartList field - and the result will be the Expiration Date minus 30 Calendar Days.  So if the Expiration Date is Feb 15th 2010, the Expiring Minus 30 field will return Jan 16th 2010.

6. Save and set security as normal along with setting default fields for both the License and Certification Table and the new Calculated field as with any SmartList Builder Object.

7. Create a Favorite and save as a Reminder with the below criteria:

8.  This will result in a reminder that has a rolling 30 day period.  Any expiration dates in that 30 day window will return as a reminder shown below.