+6I created two subqueries. One totals the sales booked by order date and the other totals the invoices billed by doc date. When I add the two subqueries to the main query and try to filter on a date range I get the object reference error.
Booked Query
Invoiced Query
Both are grouping by date, the formula is makes the amount negative if a CM.
Conditions
Main left joins invoiced query to booked query by date.
Without the conditions the data is returned
+16Do you have the conditions on the main or on the subs?
+6yes, for bookings, exclude order type behavior of TR and QT and added OrderDate not empty
Yes, for invoiced, released and docdate not empty
For main, only the date range conditions.
+16Can I see your joins?
+6Even when I just had the booking subquery in the main query it blew up on the date conditions. If I don’t use the date conditions I can add filters like this month, this quarter, YTD using the start and end dates,
+16I’m afraid I don’t have any ideas 🤔
+6Thanks for trying.
+1Hi
Although I have tried and I am getting the same error. It is probably a bug; I am not sure though. Ideally, the names of the columns should match but even after that I am getting the same error. When I checked the trace, it is giving NullReference error, so I have even tried with setting condition for empty Date. But same error.
I would suggest creating a new SQL View and use that, rather than stacking multiple GIs on top of each other. That will be much easier to manage in future.
Hopefully that helps!
+6I appreciate your efforts. I will enter a support case to see if I get a response other than fixed in xxx version, Client is on 24R2 and has to upgrade soon.
+1I appreciate your efforts. I will enter a support case to see if I get a response other than fixed in xxx version, Client is on 24R2 and has to upgrade soon.
Yeah, that will be helpful. I have tried above scenario in my 25R2 demo instance, and it is same behavior. Haven’t tried in 26R1 though.
+6Same behavior in 26R1 (before service pack 1 which I did not test). Acumatica L2 was able to reproduce the error and is investigating.
+6Acumatica has sent the issue to engineering and provided a workaround,
CALInvoiced.docDate Is Greater Than or Equal To =IsNull( [DateStart], CDate( '01/01/1900' ) )
And
CALInvoiced.docDate Is Less Than or Equal To =IsNull( [DateEnd], CDate( '01/01/2100' ) )
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.