Questions Modified GL report is duplicating lines - can you help?
We have modified a report - called "Transactions for Account" in MYOB Advanced - to join some additional tables in order to display the Business Account associated with the originating transaction, in the report. For some reason now, each transaction line is replicating multiple times in the report rather than just displaying once. Being a novice with the report designer I'd really appreciate some advice or help with what to look for to try and resolve this.
This is how the report looks when it is output
If you can post the .rpx file, it'd be super helpful. In lieu of that, I'd like to see a screen shot of your relationships table and the relation ship for each join. It's almost guaranteed that it lies in join conditions. I often have issues where I didn't properly analyze a one-to-many relationship and overlooked part of a unique key for the joins. However, you have a table with cross joins. Are you 100% certain that's the join you need? a Cross join is massive as it's multiplicative.
If Table A =1,2,3 and Table B=X,Y,Z then A Cross B is:
As you can see this will balloon quickly. I have very few cases where a cross join is required. I almost exclusively use Left and Inner joins. Not sure of your goal here with this report, so you might very well need a cross join, but that join is easily one of the hardest for me to work with, especially in larger data sets.
I agree with @michael-hansen, seeing the .rpx file would be helpful.
One thing I notice in your screenshot is the INRegister -> INTran join. That will cause rows to duplicate for inventory documents with 2 rows, triplicate for inventory documents with 3 rows, etc.
See if you can do a GLTran -> INTran join so you can bypass INRegister.
Thanks for the replies to date and apologies for being so slow to reply. Tim I have tried the redirect the join as you suggested but I'm getting the same result. This site is not allowing me to upload the .rps file - is there somewhere I could email it to so you could take a look please? Thanks!
Ah, ha, that's because I'm tricky and I intentionally don't allow .rps files to be uploaded 🙂
The reason is that what we really need is the .rpx file. For more on that, click here.
Try uploading the .rpx file. It should allow you.
i just encountered the same problem but with different tables can you help me too