Complex lookup/rollup scenario need help with

[Light_Iron]

Ok so I’ll try to get this all straight. Obviously the goal is limit the number of times records have to be linked, I’m hoping someone can offer a different perspective.

The Background:
Everyday we send out an email record from our table REPORT. Report is basically the combination of the BREAKS table, which can have 1-4 every day. BREAKS is composed of mags from the MAGS table, each break usually has between 1-10 mags. Each Mag has NOTES from the NOTES table. So thus far its been going great, you make a note, use a lookup to tag the MAG table, adds note to each mags, then the Mags is tagged to the Break table where all the mags with the all notes get rolled up, which in turn the breaks are all tagged to a single day and at the end of the day all that data rolls up into a single email report which is in HTML and uses lookup/rollup tables from all the above to fill in proper data.

The Problem:
Mags started sharing the same notes, issues that occurred on both mags, we can tag both mags but then the problem is because the notes rolls up to the mag table, a single note with multiple mags tagged results in multiple notes in the final report, so if there was Notes #20, #21 and #22, but #21 was noted on 2x mags, then the final report shows #20 #21 #22 #21. Very annoying. I can use an array join unique rollup to get rid of the duplicate but then the notes go #20, #22, #21. Again very annoying. Our NOTE table and our BREAK table are not directly linked; but they are linked through MAGS, ie MAGS is linked to the NOTE table and MAGs is linked to the BREAK table. We were using this to connect the NOTES indirectly to the BREAK table, however since the issue is multiple mags have to be tagged in a single note which then messes up the BREAK table, is there a way to use MAGS to link BREAKS directly to NOTES? Without having to add another look up table? We already have to enter so many records to join tables it would be nice if we could just use an existing link. Or is there a good way anyone can think of to remedy this problem?

Any thoughts would be deeply appreciated. Apologizes if anything is not clear, I know its a lot to take in.

[Jeremy_Oglesby]

So the main issue here is just the ordering of of the NOTES records when they are rolled-up in a daily REPORT?

[Light_Iron]

Thats one way to look at it, if I use a array join unique to get rid of the duplicate, then they are out of order and if I could re-order them that would solve the problem. The other alternative is to figure out a way to get rid of the duplicate by basically connecting NOTES directly to REPORT, instead of going through MAGS where the NOTES get duplicated if on multiple mags, which of course I can do easily with a lookup column, but then its yet another thing I have to do, and there is already so many columns that must be selected, many only so one thing can happen and I’m trying to figure out a way around that. Its kind of frustrating because REPORTS and MAGS are connected, then I can use a lookup table to pull the REPORTS page into NOTES, kind of nice to know which reports are on which notes, but I wish that that also connected them, so I could use that link directly back to REPORTS. I know, I know, its a pipe dream, but one can dream can’t they?