How do I create a summary report using time-based (months) as columns

[Jason73]

I'm trying to create a budget view for maintenance on items.  Below is an example of the spreadsheet that I currently use to do this.

The goal would be to create this:

However, it seems I can't create a time-based report given ServiceNow is looking for time-based fields, rather than time-based columns.  Any thoughts or direction on how I can do this?

Thanks!

[Adam Stout]

I appreciate the focus on the user experience. This is similar to the weekly time card structure as well.

Here are a couple of ideas to bridge the gap:

1) Move the monthly forecasts as a separate table that references the parent.  Include this child table as an embedded list (or standard related list). Add a UI action or on Insert BR that seeds all the monthly records.

2) Leave everything as-is on the form.  Create a shadow table that is a reference to this record with the month and amount as fields.  When this record is updated, sync up the child records.  For your reports, you can then report on the shadow table, but the UX remains unchanged.

I prefer option 1 since there is no duplication of data and no chance for it to get off.  It also means there is only one table to report on.  That being said, I have used option 2 in the past since this is just a reporting requirement. 

[Jason73]

Thanks Adam so far for the help.  I've decided to go down path #2 so as to maintain the user experience.  To do this, I think adding a BR to insert / update / delete records when the "parent record" is created / modified / deleted is how I'm thinking to achieve this.  I'll still have a reference field from the shadow record to the main table to maintain the appropriate relationship.

Where I'm a little lost is exactly how to update the appropriate fields within another table inside the same scoped application.  I'm seeing references to using a REST API to make this happen, which "feels" wrong.

[Adam Stout]

I don't see why you would use the REST API.  To be clear, the BR should be on the parent "Information" record not on the shadow table.  The shadow table should be read-only.

With that, you should be able to use a normal GlideRecord call in the BR (or in a script include) to manage the child/shadow records.

[Jason73]

So I'm not having any luck creating the record in the shadow table.  Here is what I have:

(function executeRule(current, previous /*null when async*/) {

	// January record
	var shadowTable = new GlideRecord('monthlySpend');
	shadowTable.initialize(); 
	shadowTable.spend_item = current.number;
	shadowTable.walk_bucket = current.walk_bucket;
	shadowTable.month = '2018-01-01';
	shadowTable.forecast = current.january_forecast;
	shadowTable.actual = current.january_actual;
	shadowTable.budget = current.u_price_1;
	shadowTable.insert();

})(current, previous);

I would just add 11 more of these, making 12 in total, one for each month.  Right now, nothing happens when I create the a record in the parent table.  This BR is set to execute "after" and on insert.

[Adam Stout]

Is spend item a reference to the "parent" record?  You don't want the number (Which is just a string), you want the sys_id.  Try current.sys_id (or even just current) instead of current.number.  Is month a string or a date?  You may want to make that new GlideDateTime('2018-01-01');

Is anything getting inserted?  What do the system logs say?