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?
Answer
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.
+3From 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.
~Will H (MYOB)
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.