Union Report

[bcooney]

I'm facing an entire redesign, and need a push in the right direction.

I have a report that pulls in child records from two related tables by simply setting two different fields to value lists. I think this type of report is called a union report.

Well, I've run into the layout size restriction. The value lists have grown to the point that they will get cut off.

I need to redesign so that I have a traditional report based on the child table.

How would you design it? Picture three tables, two are children of the third. I can run a script to create the fourth "report" table. Any ideas or am I being too academic and you'd prefer specifics to offer advice?

[Ender]

Yeah, give us some specifics and it will be easier to think through. :)

[bcooney]

Ender, thank you for offering help. I know that I just need a "report" table that results in the child records I need and a script that creates it. Also, will need to import existing data and continue to support "the old way." Isn't it a bummer to rebuild something that is 98% effective, but for that 2% needs a new design? Painted myself into the proverbial corner. So, pls just offer condolences and stay tuned.

[Ender]

Uh...My condolences!?

I still don't see what it is specifically that's requiring your Union table, but I'll believe you. I've got the same situation in a project I'm working on right now. No matter which way I tried to related things, I kept coming up with, "You can't get there from here!" And it's only for a specific report. So for me it requires a Union table, with data generated on the fly.

I have a cool way to generate the records using a list of the values in a field and a repeating calc that puts each list item into a separate repetition, then the Import pulls from that, splitting the reps into separate records (yes, repeating fields do have their uses). Finally a Replace to set the other key for the join. Alright, so it's kinda confusing and a lot of work, but it's working!

[bcooney]

It's the funkiest report I've ever put together. Even getting the 98% working version completed was quite an achievement (thanks to all those guys who wrote list custom functions!) I think that I will get there, but not in any straightforward way. Thanks for you support, and I'll let you know how it goes--rewrite starts tomorrow!