[Integrity] - Report Computed Fields: Challenge #4

[AnitaD]

Does anyone know if I can determine a HistoricFieldValue (of State, for example) as of 00:00:00 on a specific day? I want to flag a record as being "Open" or "Not Open" as of the start of the 1st day of the current month. So, I am testing if the State was "Closed" or "Canceled"; if either, they're flagged with 0. Otherwise, they're flagged with 1. In the following example, the IDs are the State values (10=Closed; 41=Canceled). The problem is that the following expression will have different results, based on the time of day that the report is run. Thanks.

[matt_giltaji]

Hi Anita,

Using now() will always return the time when the report runs or when the calculated field displays.

Instead, you may need to construct the timestamp for the 1st of the month and pass that to historicFieldValue.

There is a function available which seems like it should handle this: "timestamp()".

timestamp() will accept a string in a particular format (such as "MMM d, yyyy HH:mm:ss") and turn that into a timestamp.

So what you would want to do is use now() and the getYear/getMonth type of functions to fill in the MMM and yyyy values in the string, but hardcode d as 1 (for first of the month) and HH:mm:ss as 00:00:00.

If this gets too complicated for a computed field, you could also make a new trigger which runs at midnight on the 1st of the month, checks if the item is open or closed, then records the 0 or 1 in a field.

Using a monthly trigger does have 1 gap, which is that if the item was created after the 1st of the current month, the field would be blank. You might set up a default value for the field or a trigger to auto-set that value when an item is created to solve that gap.

Hope that helps,

Matt

[AnitaD]

Hi Matt,

I should have mentioned that I'd tried the Timestamp function much like you describe - it doesn't work. Here's the concatenation I created for my test "Timestamp Today 00:00 am" calculated field. Adding the Timestamp function results in the Expression error: "The Timestamp() function takes a single argument which must be a string in the format java.text.SimpleDateFormat@3d36b248."

thanks for your help.

[matt_giltaji]

Hi Anita,

Can you output the final string you are passing to timestamp? maybe something in the format is off.

[AnitaD]

Yes, I can output it... I changed it to "00:00 AM" by the way.

[matt_giltaji]

No, I mean what is the output?

Does it fit the allowed schemes?

MMM d, yyyy hh:mm a

MMM d, yyyy HH:mm

MMM d, yyyy HH:mm:ss