Recipes - Aggregate SOQL Datatable has an error

[solo-1234]

Hi!

I am going through all the recipes again in preparation for testing my fix for #99. The Aggregate SOQL Datatable tab throws an error upon load:

fetchTableCache error
Field must be grouped or aggregated: RecordTypeId Class.DataTableService.getSObjectsWithAllowedFields: line 546, column 1 Class.DataTableService.getSObjectDataFromQueryString: line 263, column 1 Class.DataTableService.getSObjectData: line 242, column 1 Class.DataTableService.getTableCache: line 226, column 1

(This is in a sandbox with a fresh install of core + recipes)

[tsalb]

FWIW you should not use a sandbox to test recipes (they are meant for scratch orgs). However, that doesn't mean there isn't a bug for your specific sandbox. What I want to do now is determine where the source of error is coming from.

BTW, I cannot repro with a fresh scratch org and master branch, which should be the same as the latest version.

Do me a favor on this one and create a scratch org and install 1.8.0 with the 1.8.0 recipe packageId per the wiki - perhaps I have the packageIds wrong (or didnt republish the versions).

These data points will help save me time!

[solo-1234]

I just set up my first ever scratch org and installed the packages using the wiki instructions and cannot replicate the issue. How do I figure out which versions I have? I am looking at Installed Packages in setup and there are none on the list.

TY

[tsalb]

@solo-1234 if you pushed from master branch, there is no version.

You would create a new scratch org and use the install procedure (instead of push) to see if you can replicate the issue.

Based on your comment, it sounds like I might need to push out a 1.8.1 but I would like some confirmation:

1. Spin new Scratch
2. Use package install
3. Confirm issue (would imply packaged code !== master)

[solo-1234]

Got it - sure! Thanks for bearing with me...

[tsalb]

Not a problem! Happy to mentor and get extra help any way I can ;)

[solo-1234]

Do I use the sandbox or prod install links for a scratch org?

ETA: I got it, just copied the part after salesforce.com

[solo-1234]

Okay in my scratch org with the packages in the wiki I do not see this error. How would I go about debugging it further in my sandbox?

[solo-1234]

Based on the error message I wonder if it doesn't like the fact that I have record types on account opportunity. I added GROUP BY RecordTypeId to both SOQLs on the page and that got rid of the errors in my sandbox.

I then copied the updated SOQLs to the scratch orgs, but it didn't like them - I get two errors:
Invalid SOQL String Invalid field: 'RecordTypeId'
Invalid SOQL String No such column 'RecordTypeId' on entity 'Account'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names.

Here are the SOQL strings:
SELECT COUNT(Id) FROM Account GROUP BY RecordTypeId
SELECT SUM(ExpectedRevenue) Max_Revenue, FISCAL_YEAR(CloseDate) Close_Date_Year, FISCAL_MONTH(CloseDate) Close_Date_Month, RecordTypeId FROM Opportunity GROUP BY RecordTypeId, CloseDate LIMIT 500

Not sure what the solution would be for these recipes to work in orgs with record types and in orgs without...

[solo-1234]

The original SOQL for opps is SELECT SUM(ExpectedRevenue) Max_Revenue, FISCAL_YEAR(CloseDate) Close_Date_Year, FISCAL_MONTH(CloseDate) Close_Date_Month FROM Opportunity GROUP BY CloseDate
It works fine in my scratch org.

Added an Opp Record Type (and a Sales Process which record types require) and now I get the error:
Field must be grouped or aggregated: RecordTypeId Class.DataTableService.getSObjectsWithAllowedFields: line 546, column 1 Class.DataTableService.getSObjectDataFromQueryString: line 263, column 1 Class.DataTableService.getSObjectData: line 242, column 1 Class.DataTableService.getTableCache: line 226, column 1

Modifying the SOQL to this gets rid of the error:
SELECT RecordTypeId, SUM(ExpectedRevenue) Max_Revenue, FISCAL_YEAR(CloseDate) Close_Date_Year, FISCAL_MONTH(CloseDate) Close_Date_Month, RecordTypeId FROM Opportunity GROUP BY RecordTypeId,CloseDate LIMIT 500
But that SOQL threw an error in an org without record types.

Repro steps are to take a new scratch org, create a new sales process (with any opp stages you want), and then create an opp record type. Then navigate to the Aggregate SOQL Datatable tab.

Is that enough? I don't know how to create a package to contain the sales process + record type but am open to learning.

[solo-1234]

To summarize what was discussed, the source of this issue is that when an org has record types on the object, soqldatatable will automatically add RecordTypeId to the list of queried fields (for reasons related mostly to inline editing and picklists). With aggregate soql, if that field is included, it also needs to be added to GROUP BY.

Short term solution is to only rely on recipes working in scratch orgs (since sandboxes can vary wildly).

Longer term solution would be to adjust that feature so that the RecordTypeId field is not added to aggregate queries (which shouldn't support inline editing anyways) for the rare use cases for aggregate soql (not urgent since admins can manually add GROUP BY RecordTypeId to get it working).

@tsalb let me know please if I missed anything!

[tsalb]

Sounds about right except no long term fix. Not worth the investment unless I get a PR.

I’ll take the action item from here to update wiki to be more explicit about where recipes are supposed to be used.

[tsalb]

Closed by #108