I’m trying to create a GI that brings in the Labor, Material, Move, ect. Transactions into line with their respective Production Orders. However, When I link AMMTRan to the sheet, it results in a cross join for every entry. All of the Material transactions are repeated under material, material under labor, ect. I can’t figure out what’s causing this bad join? Any idea where this issue may be originating?
Solved
Linking AMMBatch and AMMTran in GI seems to always result in a cross join.
Best answer by WillH
From a very quick look.
Troubleshooting advice:
- Set a TOP of a small number of records, to make sure database performance is good during testing. (I recommend 3 or 10, for example)
- Use a Condition to isolate just 1 order that has as few lines as possible.
- Since you’re fairly sure the problem is your join to AMMTran, I would recommend adding all fields from AMMTran to the GI so you can look at what is different between the 2 records you’re being returned.
I can’t see anything obviously wrong using my sample data, but I’m not familiar with your exact implementation, or what you think is duplicating.
General Notes, unlikely to be the problem cause:
Some of your DACs are missing the Type joins
AMProdItem joins AMProdEvnt as a LEFT
AMProdEvnt joins AMBatch as an INNER
You may need to check this join, I would generally recommend either:
- Only use INNER joins
- Only use LEFT joins
- Use INNERS until you use a LEFT, then use all LEFT joins
Generally speaking using LEFTs before INNERs doesn’t behave as people expect.
+4From a very quick look.
Troubleshooting advice:
- Set a TOP of a small number of records, to make sure database performance is good during testing. (I recommend 3 or 10, for example)
- Use a Condition to isolate just 1 order that has as few lines as possible.
- Since you’re fairly sure the problem is your join to AMMTran, I would recommend adding all fields from AMMTran to the GI so you can look at what is different between the 2 records you’re being returned.
I can’t see anything obviously wrong using my sample data, but I’m not familiar with your exact implementation, or what you think is duplicating.
General Notes, unlikely to be the problem cause:
Some of your DACs are missing the Type joins
AMProdItem joins AMProdEvnt as a LEFT
AMProdEvnt joins AMBatch as an INNER
You may need to check this join, I would generally recommend either:
- Only use INNER joins
- Only use LEFT joins
- Use INNERS until you use a LEFT, then use all LEFT joins
Generally speaking using LEFTs before INNERs doesn’t behave as people expect.
Login to the community
No account yet? Create an account
Social Login
Login with your Acumatica accountEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.