Solved

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

  • 7 June 2021
  • 2 replies
  • 190 views

Userlevel 3
Badge

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
JOIN AMBomOper
    ON AMBomItem.BOMID = AMBomOper.BOMID and AMBomItem.RevisionID = AMBomOper.RevisionID
JOIN AMBomMatl
    ON AMBomMatl.BOMID = AMBomOper.BOMID AND AMBomMatl.RevisionID = AMBomOper.RevisionID
    AND AMBomMatl.OperationID = AMBomOper.OperationID
LEFT JOIN INItemSite
    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.

icon

Best answer by lauraj46 16 June 2021, 20:08

View original

2 replies

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.

Laura

Userlevel 3
Badge

Thanks @lauraj46 !

Reply


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