Skip to main content
Answer

How to Relate a Duplicate Data Source Table to get extra information on a Single GI

  • April 16, 2025
  • 4 replies
  • 68 views
B
Semi-Pro II
Forum|alt.badge.img+1

I have been given a request to add an extra field to a “Production Order Material Summary” GI

Currently the GI will display a summary of all the materials that were used in a Production Order.

 

But my coworker wants, on that same GI, to also have a link to that Production Order number that the Material was created from.

 

For example, we blend powder through a production order, then that powder is used as material for a Packaging Production Order. And they would like to have a link back to the creation of the material. 

 

I attached my current GI, I have seen duplicate Data Sources before, but I have never used them myself. I assume I would need a second AMMTran to pull the creation transaction. But I have no idea how to relate it to the material instead of the Production Order that the material was consumed in.

 

Any Help would be much appreciated.

 

Best answer by bodiec

Update:

 

I did something that seems a little insane to me,

 

I was purely messing around because I could not get this to show the production order that produced the material.

 

And in our system, every item that is produced, gets a lot serial number that is just the Production Order Number with an added counter behind it. So PR00001-001, then the next move transaction gets PR00001-002, and so on.

 

I had an idea that I could relate the two tables using that number, because the LotSerialNbr contains the ProdOrdID, and it actually worked!

 

Not sure if this would be helpful for anyone else, unless they use the same lotserial assignment system that we do, but thought I’d but my solution in here.

 

 

 

1 Attachments
darylbowman

4 replies

bwhite49
Captain II
Forum|alt.badge.img+10
  • bwhite49
  • Captain II
  • April 16, 2025

I’m assuming the powder is lot tracked? In this case you can use the lot number of the component item to link back to the original production order through the AMProdItemSplit table

Chris Hackett
B
B
Semi-Pro II
Forum|alt.badge.img+1
  • bodiec
  • Author
  • Semi-Pro II
  • April 21, 2025

Thanks ​@bwhite49 ,  yes everything is lot tracked, I did dd the AMProdItemSplit table but I am struggling with the relation to the parent table.

 

I have tried INItemLotSerial , and then I realized that somehow AMProdMatl wasn’t joined at all? 

 

So I added AMMTranSplit to AMProdMatl, then AMProdMatl to AMProdItemSplit, 

All with the data field links of only LotSerialNbr,  But the ProdOrdNbr is always blank.

 

I am definitely missing something and I feel like it is in the Data Field Links?

 

 

1 Attachments
Chris Hackett
B
Semi-Pro II
Forum|alt.badge.img+1
  • bodiec
  • Author
  • Semi-Pro II
  • Answer
  • April 28, 2025

Update:

 

I did something that seems a little insane to me,

 

I was purely messing around because I could not get this to show the production order that produced the material.

 

And in our system, every item that is produced, gets a lot serial number that is just the Production Order Number with an added counter behind it. So PR00001-001, then the next move transaction gets PR00001-002, and so on.

 

I had an idea that I could relate the two tables using that number, because the LotSerialNbr contains the ProdOrdID, and it actually worked!

 

Not sure if this would be helpful for anyone else, unless they use the same lotserial assignment system that we do, but thought I’d but my solution in here.

 

 

 

Chris Hackett
bwhite49
Chris Hackett
Community Manager
Forum|alt.badge.img
  • Chris Hackett
  • Acumatica Community Manager
  • May 2, 2025

Thank you for sharing your solution with the community ​@bodiec!

Chris ~ Let us know how we're doing: https://forms.office.com/r/xmJUdZ1QE5 - A member helped you, please *Like* their post. A member provided an answer to your question, please mark it as *Best Answer*.
Terms & ConditionsCookie settingsAccessibility statement
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