Join table, or something else to list many related in portal

[cursulak]

Here is something I'm trying to do which I've found out is beyond my ability to figure out today.

I am creating a database to hold government acts and regulations for quick lookup. I have attached a copy of the joins. I added the extra ID's being carried through because a duplicate number problem across regs and acts and this solved it and kept them easier to track. However...

What I want to do is have only layout with a portal in it that lists the acts and regs by number so that I could just click on it and it would should show below the portal.

For example

Regs are by type;section;subsection;claus;subclaus.

1 (1) (a) (i) for example

of course you can have all sorts of depths depending but the max is the tables I've setout. I'm currently trying to do this for only 4 layers deep (section;subsection;claus;subclaus).

I want the portal to show something like this:

1

2 (1)

2 (1) (a)

2 (1) ( (i)

3 (1)

4 (1) (a) (i)

4 (1) (a) (ii)

etc.

What I'm not clear on here is either how to setup the join table to show the 4 different layers 'individually' like that (I can get 2 to work no problem) or another method of approach.

Can any of the great ones tell me how to do this, or at least point me in the right direction?

Thanks.

[comment]

I'd suggest you put everything that has text into the same table.

[cursulak]

I have done that in the past and here's why I'm trying to get away from it.

Each section can have a "fairly large" text block with it such that

2 (1) (a) (i)

there will be a large block at 2, another subsection 1 and another at claus a...etc.

When this is all in one flat table it makes it more difficult to compile reports layered properly with the sections combined and fit it on a 13 page limit output.

With this method I could use the body section and have an almost limitless report length.

13 pages sounds like a lot of room until lawyers get involved... and then you find out it's not very much space at all unless you want the font at 6 pt ;-)

[comment]

I am afraid you may have missed my point: I didn't say put everything in the same record - I suggested putting all levels in the same table, as separate records. Such table wouldn't be flat, because there would be an internal hierarchy between the records, and each element would fit in the body section when printing.

You don't HAVE to do this - in fact, there are many good reasons why you shouldn't. But a portal cannot show records from multiple tables.

---

BTW, I realize that Christmas is near - but surely you mean "clause"?

Edited December 17, 2009 by Guest

[cursulak]

Yeah, that has come up several times, we're laughing over the different possibilities for a sub-clause ;-)

I've been fiddling with calc's of different sorts to generate a list of what's in the various tables. and then drop that calc field in a portal to display the listings and make them GRR.

I can get a cascading portal to do it for me, but not in a single listing...

i was hoping that's all.