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

Question

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.

Page 1 / 1

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

Thanks @lauraj46 !

Reply

lauraj46 · Accepted Answer

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 onBuild 21.107.0023 and that seemed to work.Laura

gpineda64 · Answer

Thanks @lauraj46!

Reply

Sign up

Social Login

Login to the community

Social Login

Scanning file for viruses.

This file cannot be downloaded