How to Create GI with SubQuery

Question

I have requirement to for GI of following Query -

Select * from Mtable M, OTable O where M.OrderId=O.OrderId and O.OpId in (Select Min(OpId) from OTable OA where OA.OrderId=O.OrderId)

Answer 1

GIs are unable to do subqueries, however you can create a GI from a SQL View:

1. Create your view in SQL Server management Studio
2. Create a customization project in Acumatica
3. Create a DAC from your SQL View as follows
4. Add your CREATE VIEW SQL Script into the project as a sql script (don't forget to add a DROP VIEW Statement in the top)
5. You can now publish this project anywhere and use the SQL View DAC inside of a generic inquiry