I am trying to write a GI to get the customer’s sold since information like the below screen.
You can find my share in the below post:
My solution almost works fine, but I find it difficult to get the “last sale qty” and “last sale price” by item using GI.
The last sales date can be got because the default aggregation is MAX, the max date is exactly the last date.
However, the max(orderqty) is not the last order qty, and also the max(unit price) is not the last unit price.
so currently, I am using the AVG unit price and Sum Order qty as a workaround, and it can be accepted by user.
But still, I am eager to learn how can I get the data using a single GI?
Best answer by Gabriel Michaud
I’ve been faced with this problem more than once. It is easy enough to represent in pure SQL, but from a Generic Inquiry you have a few limitations to work around. I discovered a solution last year which involves doing a self-join (SOLine LEFT JOIN SOLine SOLinePrev), then only retaining the most recent row.
Your relations will look like that:
In the conditions tab, you need to add a condition so that only rows where nothing matches in SOLinePrev are returned (SOLinePrev.OrderNbr Is Empty does the job). You can also filter by order type if you only care about specific order types, like SO:
The sample inquiry is attached.