Data Rows - Sort

btobiczyk · April 26, 2019, 3:11pm

For the data rows - sort module, if you apply multiple criteria, how does it handle them?

I assumed it would sort in order but that is not what I am seeing.

I set it up to organize a set of rows as so…

What I receive is the following data…

<root>
	<data>
		<gldetail>
			<RECORDNO><![CDATA[6647-101173---accrual]]></RECORDNO>
			<LINE_NO><![CDATA[1]]></LINE_NO>
			<ACCOUNTNO><![CDATA[1202]]></ACCOUNTNO>
			<LOCATIONID><![CDATA[100]]></LOCATIONID>
			<DEPARTMENTID><![CDATA[D400]]></DEPARTMENTID>
			<PROJECTID><![CDATA[1044]]></PROJECTID>
			<CUSTOMERID><![CDATA[10028]]></CUSTOMERID>
			<AMOUNT><![CDATA[11]]></AMOUNT>
		</gldetail>
		<gldetail>
			<RECORDNO><![CDATA[6677-101273---accrual]]></RECORDNO>
			<LINE_NO><![CDATA[1]]></LINE_NO>
			<ACCOUNTNO><![CDATA[1202]]></ACCOUNTNO>
			<LOCATIONID><![CDATA[100]]></LOCATIONID>
			<DEPARTMENTID><![CDATA[D400]]></DEPARTMENTID>
			<PROJECTID><![CDATA[1044]]></PROJECTID>
			<CUSTOMERID><![CDATA[10028]]></CUSTOMERID>
			<AMOUNT><![CDATA[-11]]></AMOUNT>
		</gldetail>
		<gldetail>
			<RECORDNO><![CDATA[6647-101174---accrual]]></RECORDNO>
			<LINE_NO><![CDATA[2]]></LINE_NO>
			<ACCOUNTNO><![CDATA[1204]]></ACCOUNTNO>
			<LOCATIONID><![CDATA[100]]></LOCATIONID>
			<DEPARTMENTID><![CDATA[D400]]></DEPARTMENTID>
			<PROJECTID><![CDATA[1044]]></PROJECTID>
			<CUSTOMERID><![CDATA[10028]]></CUSTOMERID>
			<AMOUNT><![CDATA[22]]></AMOUNT>
		</gldetail>
		<gldetail>
			<RECORDNO><![CDATA[6661-101224-4352--accrual]]></RECORDNO>
			<LINE_NO><![CDATA[2]]></LINE_NO>
			<ACCOUNTNO><![CDATA[1204]]></ACCOUNTNO>
			<LOCATIONID><![CDATA[100]]></LOCATIONID>
			<DEPARTMENTID><![CDATA[D400]]></DEPARTMENTID>
			<PROJECTID><![CDATA[1044]]></PROJECTID>
			<CUSTOMERID><![CDATA[10028]]></CUSTOMERID>
			<AMOUNT><![CDATA[500]]></AMOUNT>
		</gldetail>
		<gldetail>
			<RECORDNO><![CDATA[6661-101224-4353--accrual]]></RECORDNO>
			<LINE_NO><![CDATA[2]]></LINE_NO>
			<ACCOUNTNO><![CDATA[1204]]></ACCOUNTNO>
			<LOCATIONID><![CDATA[100]]></LOCATIONID>
			<DEPARTMENTID><![CDATA[D400]]></DEPARTMENTID>
			<PROJECTID><![CDATA[1044]]></PROJECTID>
			<CUSTOMERID><![CDATA[10028]]></CUSTOMERID>
			<AMOUNT><![CDATA[250]]></AMOUNT>
		</gldetail>
		<gldetail>
			<RECORDNO><![CDATA[6677-101274---accrual]]></RECORDNO>
			<LINE_NO><![CDATA[2]]></LINE_NO>
			<ACCOUNTNO><![CDATA[1204]]></ACCOUNTNO>
			<LOCATIONID><![CDATA[100]]></LOCATIONID>
			<DEPARTMENTID><![CDATA[D400]]></DEPARTMENTID>
			<PROJECTID><![CDATA[1044]]></PROJECTID>
			<CUSTOMERID><![CDATA[10028]]></CUSTOMERID>
			<AMOUNT><![CDATA[-772]]></AMOUNT>
		</gldetail>
		<gldetail>
			<RECORDNO><![CDATA[6646-101170---accrual]]></RECORDNO>
			<LINE_NO><![CDATA[4]]></LINE_NO>
			<ACCOUNTNO><![CDATA[1206]]></ACCOUNTNO>
			<LOCATIONID><![CDATA[100]]></LOCATIONID>
			<DEPARTMENTID/>
			<PROJECTID><![CDATA[1044]]></PROJECTID>
			<CUSTOMERID><![CDATA[10028]]></CUSTOMERID>
			<AMOUNT><![CDATA[250]]></AMOUNT>
		</gldetail>
		<gldetail>
			<RECORDNO><![CDATA[6647-101175---accrual]]></RECORDNO>
			<LINE_NO><![CDATA[3]]></LINE_NO>
			<ACCOUNTNO><![CDATA[1206]]></ACCOUNTNO>
			<LOCATIONID><![CDATA[100]]></LOCATIONID>
			<DEPARTMENTID><![CDATA[D400]]></DEPARTMENTID>
			<PROJECTID><![CDATA[1044]]></PROJECTID>
			<CUSTOMERID><![CDATA[10028]]></CUSTOMERID>
			<AMOUNT><![CDATA[33]]></AMOUNT>
		</gldetail>
		<gldetail>
			<RECORDNO><![CDATA[6677-101276---accrual]]></RECORDNO>
			<LINE_NO><![CDATA[4]]></LINE_NO>
			<ACCOUNTNO><![CDATA[1206]]></ACCOUNTNO>
			<LOCATIONID><![CDATA[100]]></LOCATIONID>
			<DEPARTMENTID/>
			<PROJECTID><![CDATA[1044]]></PROJECTID>
			<CUSTOMERID><![CDATA[10028]]></CUSTOMERID>
			<AMOUNT><![CDATA[-250]]></AMOUNT>
		</gldetail>
		<gldetail>
			<RECORDNO><![CDATA[6677-101277---accrual]]></RECORDNO>
			<LINE_NO><![CDATA[5]]></LINE_NO>
			<ACCOUNTNO><![CDATA[1206]]></ACCOUNTNO>
			<LOCATIONID><![CDATA[100]]></LOCATIONID>
			<DEPARTMENTID><![CDATA[D400]]></DEPARTMENTID>
			<PROJECTID><![CDATA[1044]]></PROJECTID>
			<CUSTOMERID><![CDATA[10028]]></CUSTOMERID>
			<AMOUNT><![CDATA[-33]]></AMOUNT>
		</gldetail>
	</data>
</root>

if you look at the last few you can see it alternates with departmentid having a value and not. I would expect those to be sorted so there are the ones with no value grouped then the ones with a value following in ascending order.

I am not sure if it is an issue of not handling a null value or if I set something up incorrectly.

Thanks

robert · April 26, 2019, 3:53pm

I can confirm the issue.

If I try to sort these it fails:

<rss>
	<channel>
		<item>D400</item>
		<item></item>
		<item>D100</item>
		<item></item>
		<item>D200</item>
	</channel>
</rss>

But if I sort this it works:

<rss>
	<channel>
		<item>400</item>
		<item></item>
		<item>100</item>
		<item></item>
		<item>200</item>
	</channel>
</rss>

Apparently it doesn’t like to sort string values when some of them are empty.

I will try to fix. Would you want your system patched with a fix as soon as available?

btobiczyk · April 26, 2019, 4:41pm

Yes please. using that for an assembly im trying to finish this weekend

robert · April 26, 2019, 5:15pm

I have patched the Data Rows - Sort module with the fix.

The problem only occurred in the assembly editor, not in automations.

Reload your assembly editor to get the patch.

btobiczyk · April 26, 2019, 5:20pm

great. will do. thanks

btobiczyk · May 29, 2019, 5:29pm

Hi Robert, looks like a follow up to this…

I currently am sorting by employeeId on a record and that worked fine.
I am now trying to add an additional sort to after employeeid it would sort on date as so

It appears to not be working (at least on the assembly editor side, I have not tested on the server). It will still sort the employee but ignores the date.

Can you check this out? the date I am using is in the mm/dd/yyyy format.

Thanks

robert · May 29, 2019, 7:20pm

The Sort module cannot sort by date/time values:

What you can do is to use the DateTime - Text to Epoch module to first convert the date time strings into a numeric epoch millis format, then sort the rows on those numbers:

btobiczyk · May 29, 2019, 7:38pm

ok. gave that a try but have a new issue.

Using the same entrydate field as before, it is creating a subnode of <as_epoch>. great.

each instance of entrydate has a different date but the as_epoch field all have the same value. as if it takes the first instance and applies that value to all the following ignoring the new date value.

Any thoughts?

robert · May 29, 2019, 7:46pm

Would need a test case to reproduce, this one works ok for me:

The problem may be with your input format, check here for documentation: http://momentjs.com/docs/#/parsing/string-format/

btobiczyk · May 29, 2019, 8:04pm

Here is the data is question. the last node in each sodocumententry (BILLED_TIMESHEETENTRY.ENTRYDATE) is what im trying to convert/sort

robert · May 29, 2019, 8:14pm

That data works ok for me:

<BILLED_TIMESHEETENTRY.ENTRYDATE><![CDATA[04/23/2019]]>
    <as_epoch><![CDATA[1555977600000]]></as_epoch>
</BILLED_TIMESHEETENTRY.ENTRYDATE>

<BILLED_TIMESHEETENTRY.ENTRYDATE><![CDATA[04/26/2019]]>
    <as_epoch><![CDATA[1556236800000]]></as_epoch>
</BILLED_TIMESHEETENTRY.ENTRYDATE>

<BILLED_TIMESHEETENTRY.ENTRYDATE><![CDATA[04/24/2019]]>
    <as_epoch><![CDATA[1556064000000]]></as_epoch>
</BILLED_TIMESHEETENTRY.ENTRYDATE>

<BILLED_TIMESHEETENTRY.ENTRYDATE><![CDATA[04/24/2019]]>
    <as_epoch><![CDATA[1556064000000]]></as_epoch>
</BILLED_TIMESHEETENTRY.ENTRYDATE>

Hopefully that isn’t real customer data you posted…

btobiczyk · May 29, 2019, 8:17pm

for every row, regardless of date, I get

<as_epoch><![CDATA[1559088240000]]></as_epoch>

no, it is sandbox data, but i will remove it incase their sandbox holds real information.

I will work with mine more and see if I can figure out what is going on.

Thanks