Maybe I should rename this blog “ServiceNow’s Missing Documentation” because that seems to be the common theme around here.
ServiceNow has a dictionary field type called “Function”, which it describes like this:
Create function fields and scripts in the Now Platform to perform common database transformations and calculations.
Regular fields store a value in the database. Instead of storing data, a function field displays the results of a database query. Function fields do not have an associated database column. Instead, function fields generate a value based on simple computations of other fields and constants. They can be used like any other fields in the system: in forms, lists, query conditions, reports, data visualizations, and so on.
I’ve got some problems with this, namely that they say “Function fields do not have an associated database column.” That may be true, technically, but from the perspective of an end user, there’s no difference when you’re looking at the column in a list view. There’s a dictionary entry with a ‘Type’ value, a column label, etc. There’s even the ability to sum, average, min, and max this column in the list view! But I digress….
The real reason for this post is to document a hidden capability. I have twice now had the request from a customer to show the duration of a record since a certain date/time. The specific example is to have the duration of time since the record was created (I’ll assume, for demonstration purposes, that the Created and Opened dates are the same and the terms are interchangeable). The ‘Duration’ and ‘Business Duration’ fields on the Task table are only calculated when the task is closed. That’s not going to work, as the tasks this customer cares about ones that are still open.
My first thought after both requests from the customer was “Function field to the rescue!” I happily create a new dictionary entry on the task table, set the ‘Type’ value to “Duration”, and check the ‘Function field’ checkbox. I then fire up Product Docs and search for “function field”. Oddly enough, the right page comes up first in the list! First time for everything.
The docs provide a handful of operations that can be used in this format:
glidefunction:operation(field1, field2)While looking through the docs, you’ll find the datediff() operation. Joy of joys! This takes two date/time fields from the record as input and compares the difference between them. The output is a duration value. Cool! I’ll just plug in the opened_at for one parameter and then… wait… what would I use for the other parameter? My heart breaks — you need a date/time field to contain the current date/time in order to do the comparison!
Hmm.. what to do… Let’s try “gs.nowDateTime()”… Nope. “0 seconds” appears in the new column. How about “javascript:gs.nowDateTime()”? Negative. “javascript:new GlideDateTime().getDisplayValue()”. LOLno.
Community turns up fruitless. The ServiceNow blogging world just regurgitates the Product Docs. I’m questioning every life decision at this point.
I guess we’re backtracking and telling the client “no”.
Randomly, grasping for any chance that something might work, I start throwing things at the wall to see if anything sticks.
Eureka! I did it! I figured out the secret incantation to allow this to work!
now()This beautiful, unknown, undocumented gem allows the datediff function to work exactly as I hoped!
glidefunction:datediff(now(),sys_created_on)Note, the “now()” input must be first in this particular comparison or else you’ll get… something that’s not right.
If you wanted to see how far in the future something is, such as the Planned Start Date on a change request, you would put “start_date” first and “now()” input second. The first input must be chronologically later than the second input to get a good result.
Now I’m off to Community to reply to all the old posts I can find.
tl;dr:
To use the current date/time in a Function field, set the ‘Type’ to “Duration” and use “now()” in the datediff() operation to represent the current date and time. The later date/time should be first and the earlier should be second.