We have two different GI’s, one called AR-OE Export which is legacy data from an old ERP, and the other one called Ar-Invoice History. Both of the GI’s have been set up the same way as far as naming the fields are concerned. What we would like to do is combine both GI’s to form one dataset and have parameters to choose year and month and turn the results into a pivot table. We’ve looked at the WYATT.ERP method and it doesn’t seem feasible in our case to pursue that line of thinking.
Answer
Joining two different GI's together
Best answer by lauraj46
Hi
From the 2024 R1 release notes:
In previous versions of Acumatica ERP, only data access classes (DACs) could be specified as data sources for a generic inquiry. The technical specialist (that is, the user developing the generic inquiry) then joined the added DACs one by one through key fields to construct a query. Some inquiries could have multiple tables joined into very complex queries.
Acumatica ERP 2024 R1 simplifies the process of constructing a query. Now a technical specialist can specify generic inquiries as data sources for a generic inquiry. That is, they can create multiple simple generic inquiries and reuse them as data sources for a generic inquiry. The specialist builds a complex request by combining the simple inquiries into one.
+8HI
Are you on version 2024 R1? In this release, Acumatica has added the ability reference other GIs. I haven’t tried this, but it seems like you could use this capability in conjunction with the WYATT.ERP method to accomplish this result.
Hope this helps!
Laura
Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
+8Hi
From the 2024 R1 release notes:
In previous versions of Acumatica ERP, only data access classes (DACs) could be specified as data sources for a generic inquiry. The technical specialist (that is, the user developing the generic inquiry) then joined the added DACs one by one through key fields to construct a query. Some inquiries could have multiple tables joined into very complex queries.
Acumatica ERP 2024 R1 simplifies the process of constructing a query. Now a technical specialist can specify generic inquiries as data sources for a generic inquiry. That is, they can create multiple simple generic inquiries and reuse them as data sources for a generic inquiry. The specialist builds a complex request by combining the simple inquiries into one.
Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
+1Is it just me or is anyone else finding issues with joining multiple GI’s as Data Sources? Seems very finicky. I have not been successful thus far. I get ‘Invalid column name’ that references columns not in either of my GI’s. They almost appear to be key fields. So it seems there may be something to do with an unwritten rule that when joining two GI’s you must include all key fields?? Anyone know?
I also receive the same message when using GI as data source. Unsure how to get around the error, any insights would be helpful.
Screen shots showing the error and how the GI was set up.
+1Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.