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!

[Jason73]

The "spend_item" is in reference to the parent record, which I've updated to try both current and current.sys_id.  The other should be a date, so I've added the GlideDateTime() function.  Still no dice however...

Can you confirm the GlideRecord('monthlySpend') is the right way to reference the "monthlySpend" table within the scoped application...

There is no record being created... Looking at the dev logs, I don't see anything meaningful.

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

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

})(current, previous);

The "JavaScript Log" shows the following when I turn on the "JavaScript Log and Field Watcher"

16:35:05 (296)x_mahe2_bnf_operatingexpenses.dodirty form focus
16:35:23 (063)x_mahe2_bnf_operatingexpenses_list.dorunning inline scripts, count: 0
16:35:23 (064)x_mahe2_bnf_operatingexpenses_list.dorunBeforeRender
16:35:23 (066)x_mahe2_bnf_operatingexpenses_list.dorunAfterAllLoaded, functions: 17
16:35:23 (095)x_mahe2_bnf_operatingexpenses_list.dofireAllChangeHandlers start
16:35:23 (096)x_mahe2_bnf_operatingexpenses_list.dofireAllChangeHandlers end
16:35:23 (096)x_mahe2_bnf_operatingexpenses_list.dolate load functions: 5
16:35:23 (098)x_mahe2_bnf_operatingexpenses_list.doClearing event handlers reference cache
16:35:23 (098)x_mahe2_bnf_operatingexpenses_list.do[00:00:00.032] runAfterAllLoaded finished
16:35:23 (177)x_mahe2_bnf_operatingexpenses_list.doafter page loaded starting
16:35:23 (182)x_mahe2_bnf_operatingexpenses_list.do[00:00:00.004] GwtListEditor onTableLoad, tables examined: 1
16:35:23 (184)x_mahe2_bnf_operatingexpenses_list.do[00:00:00.006] after page loaded complete, functions called: 6
16:35:23 (309)x_mahe2_bnf_operatingexpenses_list.do[00:00:00.014] Reclone headers
16:35:26 (896)x_mahe2_bnf_operatingexpenses_list.do[00:00:00.003] Reclone headers

[Adam Stout · Accepted solution]

Can you verify the name of the parent table and the shadow/child table?

What do the system logs show (System Logs -> System Logs -> All)?

[Jason73]

I had no idea those logs were there, this is awesome.  They should have this for scoped applications in a log like the "JavaScript Log" window.

Parent table is: operatingExpenses

Shadow table is: monthlySpend

I just added a new record to the parent table and  here is what I got:

[Jason73]

Actually, I figured it out... the table name should have been x_mahe2_bnf_monthlyspend, which is the full table name, not just the scoped application table name.  Moving forward 🙂