https://www.servicenow.com/community/developer-forum/querying-sys-history-line-tables/m-p/2025637#M682563 <P>This function is amazing, thanks for&nbsp;sharing the info!</P> Tue, 10 Jul 2018 23:32:48 GMT davidsizu 2018-07-10T23:32:48Z https://www.servicenow.com/community/developer-forum/querying-sys-history-line-tables/m-p/2025630#M682556 <P>I have a requirement to query against the sys_history_line table and retrieve some information.</P><P>I had a request to display the "fulfilment" date on a Portal record.</P><P>On checking a couple of records, I can query the sys_history_set and then the sys_history_line</P><P>I soon realised this was not great for the portal so I have added a new field to the sc_request and sc_req_item tables.</P><P></P><P>However, now I am being asked to back populate the data</P><P>i have had a look at pulling from sys_audit and from the sys_history_line tables.</P><P>the later are better for my needs.</P><P></P><P>I am however finding that when I run a Background Script to get some data I am offten getting a lot of null information for the field I am after.</P><PRE __default_attr="java" __jive_macro_name="code" class="jive_macro_code _jivemacro_uid_14474260753756213 jive_text_macro" data-renderedposition="218_8_1192_160" jivemacro_uid="_14474260753756213"><P>Script: REQ0015788 : b76ef6ec6f1782402a3e9accbb3ee4ac : 2015-10-21 10:03:36</P><P>*** Script: REQ0015776 : 43b86b0d6f6346007115ca727d3ee404 : 2015-11-04 12:40:15</P><P>*** Script: REQ0015763 : 6d2d55df6f5f4200b872129e5d3ee424 : 2015-10-29 16:01:48</P><P>*** Script: REQ0015756 : 22db3cd76f5f4200b872129e5d3ee43b : 2015-11-05 10:49:29</P><P>*** Script: REQ0015750 : d05479956f970200b872129e5d3ee47a : 2015-11-05 10:49:30</P><P>*** Script: REQ0015749 : 421179d16f970200b872129e5d3ee4c9 : 2015-11-05 10:49:32</P><P>*** Script: REQ0015744 : c2d7c15d6f570200b872129e5d3ee439 : 2015-10-29 16:24:22</P><P>*** Script: REQ0015737 : cc16a0496f930200b872129e5d3ee435 : null</P><P>*** Script: REQ0015605 : f35b82dc6f5f42402a3e9accbb3ee4eb : null</P><P>*** Script: REQ0015600 : 74f84a5c6f5f42402a3e9accbb3ee492 : null</P></PRE><P></P><P>If I now go into the record REQ0015744 and come straight back out and run the Backgroun Script again, I now see the information</P><PRE __default_attr="java" __jive_macro_name="code" class="jive_macro_code _jivemacro_uid_14474261260913226 jive_text_macro" data-renderedposition="420_8_1192_144" jivemacro_uid="_14474261260913226"><P>*** Script: REQ0015788 : b76ef6ec6f1782402a3e9accbb3ee4ac : 2015-10-21 10:03:36</P><P>*** Script: REQ0015776 : 43b86b0d6f6346007115ca727d3ee404 : 2015-11-04 12:40:15</P><P>*** Script: REQ0015763 : 6d2d55df6f5f4200b872129e5d3ee424 : 2015-10-29 16:01:48</P><P>*** Script: REQ0015756 : 22db3cd76f5f4200b872129e5d3ee43b : 2015-11-05 10:49:29</P><P>*** Script: REQ0015750 : d05479956f970200b872129e5d3ee47a : 2015-11-05 10:49:30</P><P>*** Script: REQ0015749 : 421179d16f970200b872129e5d3ee4c9 : 2015-11-05 10:49:32</P><P>*** Script: REQ0015744 : c2d7c15d6f570200b872129e5d3ee439 : 2015-10-29 16:24:22</P><P>*** Script: REQ0015737 : cc16a0496f930200b872129e5d3ee435 : null</P><P>*** Script: REQ0015605 : f35b82dc6f5f42402a3e9accbb3ee4eb : null</P></PRE><P></P><P>arrgh Is there any way I can make this work - it will be a background task run once for the sc_request and sc_req_item tables</P><P>It is as if I need to open and inspect each record or somehow force the history tables to work</P><P></P><P>I did try a query against the sys_audit table and it was seemingly having a similar issue</P><P></P><P>any ideas would be gratefully received</P> Fri, 13 Nov 2015 15:58:58 GMT https://www.servicenow.com/community/developer-forum/querying-sys-history-line-tables/m-p/2025630#M682556 poyntzj 2015-11-13T15:58:58Z https://www.servicenow.com/community/developer-forum/querying-sys-history-line-tables/m-p/2025631#M682557 <P>Hi Julian, </P><BR /><P></P><BR /><P>Based on the data you shared it appears that you're not getting null for <SPAN style="color: #000000; font-family: Consolas, 'Courier New', Courier, mono, serif; font-size: 12px;">REQ0015744</SPAN> on none of the background scripts. In fact, they appear to have the same time.</P><BR /><P></P><BR /><P>Would you mind rephrasing your question so that we can have a better understanding of the problem you're facing?</P><BR /><P></P><BR /><P>Thanks,</P><BR /><P>Berny</P><BR /> Sun, 15 Nov 2015 03:49:08 GMT https://www.servicenow.com/community/developer-forum/querying-sys-history-line-tables/m-p/2025631#M682557 bernyalvarado 2015-11-15T03:49:08Z https://www.servicenow.com/community/developer-forum/querying-sys-history-line-tables/m-p/2025632#M682558 <P>Hi Berny</P><BR /><P>If I run a query that will take the sys_id of the REQ record, look at the <STRONG>sys_history_set</STRONG>. &nbsp; I get the relevant information i need and then try to query the <STRONG>sys_history_line</STRONG> table to return the latest <STRONG>close_code</STRONG> entry</P><BR /><P>I need to populate a field on the REQ table</P><BR /><P>When I run the query I often find lots of null entries</P><BR /><P>If I go into the record, at the bottom we see the Activity states - including the one I am searching for</P><BR /><P>If I now got back and run my query again, I now get to see the data from the sys_history_line.</P><BR /><P></P><BR /><P>It is as if I need to force the line to load to do this.</P><BR /><P></P><BR /><P>I did try against the <STRONG>sys_audit</STRONG> table and got a similar problem</P><BR /> Sun, 15 Nov 2015 19:33:18 GMT https://www.servicenow.com/community/developer-forum/querying-sys-history-line-tables/m-p/2025632#M682558 poyntzj 2015-11-15T19:33:18Z https://www.servicenow.com/community/developer-forum/querying-sys-history-line-tables/m-p/2025633#M682559 <P>Hi Julian, I now understand what you're looking for. Indeed it's odd how records get added to the audit / history tables. The good news is that there is a work around. </P><BR /><P></P><BR /><P>On your case you can create an <STRONG>Insert / Update </STRONG><STRONG>OnAfter</STRONG> business rule in the <STRONG>change_request </STRONG>table and have a script like the following:</P><BR /><P></P><BR /><PRE __jive_macro_name="quote" class="jive_macro_quote jive_text_macro" data-renderedposition="123_8_1041_41"><P>new GlideHistorySet("change_request", current.sys_id).generate();</P><BR /></PRE><P></P><BR /><P>That will make the trick <SPAN __jive_emoticon_name="happy" __jive_macro_name="emoticon" class="jive_emote jive_macro" data-renderedposition="195_157_16_16" src="https://www.servicenow.com/8.0.1.35b65d4/images/emoticons/happy.png"></SPAN></P><BR /><P></P><BR /><P>Thanks,</P><BR /><P>Berny</P><BR /> Mon, 16 Nov 2015 02:25:17 GMT https://www.servicenow.com/community/developer-forum/querying-sys-history-line-tables/m-p/2025633#M682559 bernyalvarado 2015-11-16T02:25:17Z https://www.servicenow.com/community/developer-forum/querying-sys-history-line-tables/m-p/2025634#M682560 <P>I shall have a look at this a little later this week</P><BR /><P></P><BR /><P>Cheers</P><BR /> Mon, 16 Nov 2015 09:10:25 GMT https://www.servicenow.com/community/developer-forum/querying-sys-history-line-tables/m-p/2025634#M682560 poyntzj 2015-11-16T09:10:25Z https://www.servicenow.com/community/developer-forum/querying-sys-history-line-tables/m-p/2025635#M682561 <P>Sounds good Julian! <SPAN __jive_emoticon_name="happy" __jive_macro_name="emoticon" class="jive_emote jive_macro" data-renderedposition="8_141_16_16" src="https://www.servicenow.com/8.0.1.35b65d4/images/emoticons/happy.png"></SPAN> </P><BR /><P></P><BR /><P>The business rule is really straight forward (since it's just one line of code). Please let me know if you run into any issues or if you have any further questions.</P><BR /><P></P><BR /><P>Thanks,</P><BR /><P>Berny</P><BR /> Tue, 17 Nov 2015 06:16:00 GMT https://www.servicenow.com/community/developer-forum/querying-sys-history-line-tables/m-p/2025635#M682561 bernyalvarado 2015-11-17T06:16:00Z https://www.servicenow.com/community/developer-forum/querying-sys-history-line-tables/m-p/2025636#M682562 <P>Tried and it on a set of test records it has worked as intended.</P><BR /><P></P><BR /><P>Cheers</P><BR /> Wed, 18 Nov 2015 09:18:05 GMT https://www.servicenow.com/community/developer-forum/querying-sys-history-line-tables/m-p/2025636#M682562 poyntzj 2015-11-18T09:18:05Z https://www.servicenow.com/community/developer-forum/querying-sys-history-line-tables/m-p/2025637#M682563 <P>This function is amazing, thanks for&nbsp;sharing the info!</P> Tue, 10 Jul 2018 23:32:48 GMT https://www.servicenow.com/community/developer-forum/querying-sys-history-line-tables/m-p/2025637#M682563 davidsizu 2018-07-10T23:32:48Z