21R1: GI Data Field Relations do not allow you to join fields from other tables in the GI

  • 7 June 2021
  • 2 replies

Userlevel 3

In a GI, I need to query the WarehouseDetails settings for Material items included in MFG BOMs.

Normally I would join AMBomMatl to INItemSite ON AMBomMatl.InventoryID = INItemSite.InventoryID AND AMBomMatl.SiteID = INItemSite.SiteID.

In this case however, the client has not populated AMBomMatl.SiteID, so I thought I could add AMBomItem to the joins and use AMBomItem.SiteID = INItemSite.SiteID instead.

In SQL, the query would look like this:

SELECT * from AMBomItem
    ON AMBomItem.BOMID = AMBomOper.BOMID and AMBomItem.RevisionID = AMBomOper.RevisionID
    ON AMBomMatl.BOMID = AMBomOper.BOMID AND AMBomMatl.RevisionID = AMBomOper.RevisionID
    AND AMBomMatl.OperationID = AMBomOper.OperationID
    ON AMBomMatl.InventoryID = INItemSite.InventoryID
    AND AMBomItem.SiteID = INItemSite.SiteID

Prior to 21R1, the GI Relations allow me to join any field from any of the tables included in the GI, but in 21.106.0024 , I cannot join fields from AMBomItem to INItemSite.


I attached the GI XML.

Could this be related to pre-defined settings in the DAC Relations?

Thanks in advance for taking a look.

 PS: I can populate AMBOMMatl.SiteID in order to solve the data problem, but I really would like to know the reason why a given DAC is not available for joins in a GI.


Best answer by lauraj46 16 June 2021, 20:08

View original

2 replies

Userlevel 3

Thanks @lauraj46 !

Userlevel 7
Badge +8

Hi @gpineda64 ,

Although the field is not in the dropdown list, you should still be able to reference it using a formula, like this:


I tested on Build 21.107.0023 and that seemed to work.



About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved