Instead of nested portals use...?

[Rothko]

Hello

I know nested portals don't work, but 'nested portal' best describes what I want to do. (please move this post somewhere more appropriate if necessary)

I've got database which tracks Primary Materials [color:yellow](table 1), Composite Groups made of Primary Materials [color:orange](table 2), and the finished product made up from Composite Groups [color:green](table 3).

I'd like to see on one page exactly what a finished product is made up from. Hierarchically like this:

[color:green]Product

[color:orange]-Group1

[color:yellow]--Material1

--Material2

--Material5

[color:orange]-Group2

[color:yellow]--Material3

--Material5

Some Primary Materials will be used in several Composite Groups; some Composite Groups will appear in several Products, etc. Attached a little diagram that describes the three tables. All 3 tables are related many-to-many.

I'm wondering if a report/portal combination can get me there... Any suggestions appreciated!

[Søren Dyhr]

Read this:

http://jonathanstark.com/recursive_data_structures.php

--sd

[Rothko]

Thanks Søren!

That's a hard one to digest. I'll read it when I'm more awake.

Christian

[comment]

I don't see anything recursive in your structure. I believe all you need is two calculation fields, one in the Groups table, and another one in the Products table. The first calculation is roughly =

GroupName & Substitute ( ¶ & List ( Materials::MaterialName ) ; ¶ ; "¶-" )

The second calculation does the same thing, but uses the first calculation instead =

ProductName & Substitute ( ¶ & List ( Groups::cFirstCalc ) ; ¶ ; "¶-" )

Add styling as appropriate.

[Rothko]

Thanks for that, it works perfectly. However, it returns only text. (In fact it returns exactly what I used earlier to illustrate the desired structure )

If I wanted it to look and work like a portal, with the ability to include buttons, fields, etc - would that have to be recursive?

Thanks.

[comment]

Not sure what you mean by "look and work like a portal". You could display the results in a repeating calculation field instead of a single text block, and have a button for each repetition - but there would be no scrolling.

The only way to show Groups and Materials in the same portal is to put them in the same table, and that would be the beginning of recursion. However, I would advise against restructuring a database to fit some interface requirement.

Another option available to you is to place a portal to Materials on a layout of Product. This would initially look like this:

Group1....Material1

Group1....Material2

Group1....Material5

Group2....Material3

Group2....Material5

Then you would use a calculation to display the group conditionally:

Group1....Material1

..........Material2

..........Material5

Group2....Material3

..........Material5

I think I have posted a demo showing this, but I cannot find it at the moment.

[Rothko]

Not sure what you mean by "look and work like a portal".

Well I was thinking something where I can move fields, buttons and graphics around freely. Like I could if it were a line in a portal.

The only way to show Groups and Materials in the same portal is to put them in the same table, and that would be the beginning of recursion. However, I would advise against restructuring a database to fit some interface requirement.

Hmm. It's not just to make something look good, to display some kind of 'shopping list' in the end, with a complete breakdown of groups and materials (and their statuses, amounts, suppliers etc) for each product is the whole point of the database.

On the other hand it might be good to see an example of what you just suggested. Right now I'll have a good look at recursiveness.

[comment]

See here:

http://www.fmforums.com/forum/showtopic.php?tid/189981/post/266405/#266405

[Rothko]

Thanks for that. That looks like it might do the trick. I'm not sure though only 2 tables will be enough. For example, some of the Base Materials might appear in different groups.

I also still think it needs to look like a regular report, so that it will print OK. Actually, it is only for printing that I really need to be able to view info from all 3 tables.

I've made a quick and dirty mockup in Photoshop that hopefully explains a little better what I'm trying to make.

Edited November 21, 2007 by Guest

[comment]

I also still think it needs to look like a regular report, so that it will print OK. Actually, it is only for printing that I really need to be able to view info from all 3 tables.

Well, then why not simply make a "regular report"? A report from Materials, with sub-summaries by Group and by Product could look almost exactly like your mockup.

some of the Base Materials might appear in different groups.

Then indeed you will need to add a join table between Materials and Groups, and base the report on it, instead of on Materials.

[Rothko]

I feel stupider the longer I try to work it out. I've made a report, but it behaves strangely and I'm not sure why.

OK, maybe someone has the patience to go back to the start with me. This is my relationships graph:

MaterialsTable -jointable1 - GroupsTable - jointable2 - ProductTable

It's a many-to-many relationship between MaterialsTable and GroupsTable, and again many-to-many between GroupsTable and ProductTable. I'm allowing the creation and deletion of records in the jointables.

Correct so far?

I've based the report on jointable1, but it behaves oddly.

The report it set up like this: A columnar report with grouped data, showing these fields: 1.Product Name, Group Number, Material Name. It also organizes by exactly these fields, so that I get this structure:

Product

-Group1

--Material1

--Material2

--Material5

This works too.

There is an extra field in linktable2 that describes where in the finished product the group goes; this field never updates in the report; I don't even know where it gets its values from.

I'm wondering whether I should go back and down the recursive road. That's basically using just one table with all the data in it, and conditional table occurrences depending on what type of record it is, right? How would that have to be done with my kind of many-to-many & many-to-many relationships?

There is a graph at the bottom of

http://jonathanstark.com/recursive_data_structures.php that almost describes my situation, except mine's simpler, because mine would always stick to the hierarchy. In Jonathan's solution a 'base material' can become part of a 'product' straight away; in mine it would form a group with just one component. The shame is, the example file given on that site doesn't really work. (try entering another car/product, it'll never show up as a component's 'parent'.) Also, it's way over my head, and I still think my situation isn't that complex.

Thanks guys, much appreciated!

Christian

[comment]

This is getting more and more complex each time you describe it. If you have two join tables, you can produce a report (i.e. a "regular" Filemaker report) for one Product at a time. The report would be based on the join between Materials and Groups, with a sub-summary by Group.

It's not possible to produce a straightforward report for multiple products, because there aren't enough records in the GroupMaterials join table to generate the number of body instances required for such report. So some recursive action is required here, such as:

• Loop between between Products, generating a separate report for each;

or

• Find the records in GroupMaterials for each Product in turn, and import them into a temp table;

or

• Make a report based on the Product table, using a calculated text field, as discussed above;

You could also try reporting from the ProductGroups join table, with a sliding portal to the GroupMaterials join table.

The question whether all should be in the same table is a separate one, IMHO.

[Rothko]

You could also try reporting from the ProductGroups join table, with a sliding portal to the GroupMaterials join table.

This did the trick! Thanks a million! Heroically solved.

C.

[comment]

Well, it has its limitations, as I'm sure you'll discover - but it's cheap to implement and a whole lot better than nothing.

[Ocean West]

how about this...

http://www.filemakermagazine.com/videos/infinite-hierarchies-creating-a-folder-tree.html