In order to allow me to focus on Consulting (click here) and Courses (click here), I continue to review all posts here in the Forums, but, in general, I'm only personally responding to posts that I mark as #AcumaticaTnT (click here). For Questions (click here), others may respond, or you can post over at Community.Acumatica.com (click here).
Join table with filter on a constant (Generic Inquiries)
The goal is to have a generic inquiry to identify sales orders in the shipping status, that at some point had the backordered status.
From what I have found I could do a join to the Audit History we have of the sales order page.
This is how it looks in SQL:
SELECT top 30 so.OrderNbr, so.OrderQty, so.OpenOrderQty, so.OrderDate, ah.ModifiedFields, ah.ChangeDate, so.ShipDate, so.Status
FROM SOOrder so
INNER JOIN AuditHistory ah ON 'SO' + so.OrderNbr = ah.CombinedKey
and ModifiedFields = 'StatusB'
WHERE so.CompanyID = 2
and so.ShipDate is not null
and so.Status = 'N'
I'm not sure how to make the part in Blue work in a GI.
See the screenshot below for my attempt on making this in the join tab.
I have also tried adding in the conditions (last two lines in screenshot) but this doesn't work since the join is a one to many join and I can't control which line in the audit history table it joins on without my and ModifiedFields = 'StatusB'.
You need to prefix the string with an equals so it's treated as a formula.
Thank you Royce!