Question

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

  • 7 June 2021
  • 1 reply
  • 23 views

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.


1 reply

Userlevel 2
Badge

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

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 — 2020  Acumatica, Inc. All rights reserved