I feel like I know nothing about SQL even after spending 10 plus years using it on other systems when it comes to trying to use Generic Inquiries to create reports. Which I have to do because Acumatica does not have the ability to write SQL directly against the database and produce results (This is a massive feature that I can’t believe is missing and hope 2025R2 addresses it).
I need to create a Generic Inquiry that looks at Customer records takes a parameter for CreatedDateTime FROM and CreatedDateTime TO.
All I need to do is look at Customers whose CreatedDateTime field falls between those two parameters and run a simply COUNT function to see how many customers were created between those two dates.
This seems literally impossible to do via Generic Inquiry.
In SQL all I would write is
SELECT COUNT(*)
FROM Customers
WHERE CreatedDateTime>=1/1/2014 AND CreatedDateTime<=1/31/2014
Though if you look at the database you would actually need to query BAccount table to get this CreatedDateTime and then add TYPE to the where Clause.
REGARDLESS, how to do you write a simple Generic Inquiry to COUNT the number of customer records with a created date between 2 date parameters.
I should only get a single row response but Acumatica wants a GROUP BY defined even though for this it shouldn’t need it. Very confused on the logic of Generic Inquiries especially because if you go to the TRACE and see the SQL generated it includes TONS of fields and groups and order by logic that I didn’t include in my generic inquiry.
