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.
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.
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.