Unusual Query and File Export Behavior

[unclejieves]

I am querying Product2 records which can be hierarchical i.e. Product2 record may have a parent which is a lookup to another product2 record. Not all Product2 records have a parent.

When I query all records( 6k+), which would be inclusive of records with and without a parent, the parent lookup I specify in the query, tk_base_product__r.Name, only shows as tk_base_product__r in the column heading. The results set also does not sort properly when sorting on the parent lookup. Even when i specify DESC it will show records w/o a parent first.

Even more strange; When I export to CSV it shows the data structure in the relationship field instead of the value of the field i'm specifying.

However, if I only query a small dataset the column heading and values are as expected in the query and resulting file. Sorting on the parent field still doesn't work though.

SOQLx version is 3.4
MacOs 11.1 w/M1 chip

[superfell]

On your first point about ordering, you need to use NULLS LAST to have the NULLs ordered to the bottom of the results. see https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_orderby.htm#!

On the column headers issue, the headers are calculated from the data seen in the rows of the query result, if there's no name field populated in those rows then it won't appear as a header. There's no API to have sfdc describe what the columns of the query are, so the only ways to determine the columns is from looking at the actual data returned, or by parsing the soql query itself

[unclejieves]

Thank you for the info on NULLS LAST. Embarrassed I'd never used or figured that out until today. With the large data set, there are rows populated with a value in the __r.Name column. Of the 6k records only 600 of them don't have a value there. When it does not resolve the header correctly the data that shows up in the .csv for those rows is the JSON structure you see in the second screen shot. Since it does resolve the header and populate the date in the column as expected with a result set of 4, I'm guessing it only resolves those headers based on what is returned in the UI and not the entire data set that can be exported?

…

On Thu, Feb 11, 2021 at 11:20 AM Simon Fell ***@***.***> wrote: On your first point about ordering, you need to use NULLS LAST to have the NULLs ordered to the bottom of the results. see https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_orderby.htm# ! On the column headers issue, the headers are calculated from the data seen in the rows of the query result, if there's no name field populated in those rows then it won't appear as a header. There's no API to have sfdc describe what the columns of the query are, so the only ways to determine the columns is from looking at the actual data returned, or by parsing the soql query itself — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#104 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AGYKGICEAMH6YC4AMNOYDJLS6QGXPANCNFSM4XO27YRQ> .

[superfell]

It's resolved from the first set of rows returned by the query API. So all the results you see after clicking Query, but not any of the rows that appear when you subsequently click Query More. (or use the export all rows option in the save dialog) Using NULLS LAST should work as a work around. I'll have to look into having it recheck the column headers after a query more.

[unclejieves]

Thank you for the guidance. The recheck would be a nice feature!

…

On Thu, Feb 11, 2021 at 1:21 PM Simon Fell ***@***.***> wrote: It's resolved from the first set of rows returned by the query API. So all the results you see after clicking Query, but not any of the rows that appear when you subsequently click Query More. (or use the export all rows option in the save dialog) Using NULLS LAST should work as a work around. I'll have to look into having it recheck the column headers after a query more. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#104 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AGYKGIGQJM5RBP5K6SUVKXTS6QU4LANCNFSM4XO27YRQ> .