Today I decided to add some more functionality to the company database and create a consistent out of office reply function for all of us (mainly as some either lack contact details or someone else to contact, which is important in most cases and this also speeds up the process).

It’s basically just a calculated text field with a lookup fields and about 5 entry fields feeding in. You simply enter the core details:

• From (auto filled from account name but can be changed if, for instance, a secretary is updating)
• Period away till date
• Dropdown of people in the company to add as a contact for urgent matters (optional and added in as a full name followed by contact details)

I setup the Out of office fields in another table related to the Contacts table. To add in the ‘people to contact text’ I made the drop down displaying the names but the field just returns the contact’s ID from the database. The Out of office table is then related to the Contacts table via these ID fields and then a text field within the Out of office table is used as a lookup to return the full contacts details from a calculation field in the contacts table.

You end up with a comprehensive Out of office text. On top of that I added 2 buttons – copy to clipboard and email me. Email me does a lookup of the account name and send the user an email containing the text (with optional note) using the SmartPill PHP plugin. (The idea of this was so that if you want to set it up on your phone at a later date you can copy and paste it from that email).

Whilst doing this I setup a nice little custom function to do standard excel/windows style date formatting (dd-mm-yy / dd-mmm-yyyy) for the period till field rather than hardcoding the date format.

/*

Name:
DateFormating

Parameters:
theDate, format

Example:
DateFormating( get(currentdate); dd-mmm-yy) – returns 25-Dec-08

*/

Let(dater = GetAsDate(theDate);

Substitute(format;

[“yyyy”; Year (dater)];
[“yy”; Right (“0” & Year (dater);2)];
[“mmmm”; MonthName (dater)];
[“mmm”; Left (MonthName (dater);3)];
[“mm”; Right (“0” & Month (dater);2)];
[“dddd”; DayName (dater)];
[“ddd”; Left (DayName (dater);3)];
[“dd”; Right (“0” & Day (dater);2)]

))