Showing records from 4 tables in 1 portal + extra

[tom.]

Hi everyone.

I have stuck with a problem on displaying data in a portal from 4 different tables. This is sample scenario: I have 4 tables (cars) 1.merc, 2.toyota, 3.honda, 4.bmw. I want to group them in "folders". It means you can take specific model and assign to the appropriate folder (another table).

It shouldn't be difficult, but I have stuck with it. Moreover, I should be able to add and delete cars to folders over the network. Apparently Replace function is not a good option for network database.

Any suggestion very much appreciated.

I attached sample file. It's just a quick guide to give an idea.

category.fp7.zip

[T-Square]

Tom--

I'll confess not to have looked at your file, but...

Why do you have separate tables for different brands of car? In my mind, they could all be in one table, with fields for Brand, Model, Year, etc. Brand-specific views can be built using relationships.

David

[tom.]

Yes, I know this may cause confusion. It's may be not logical in FileMaker development logic.

But requirement of database was to separate different categories (like car makers) to different tables because:

1. All fields in one table are not identical with fields in other table. As most fields are the same, some tables have other fields that are not used in others. So, they are not used in the layout. Cars are just examples, it may be better compared to this sample table: fruits, meat and dairy products.

x. table - field, field, etc...

1. fruits - description, weight, sugar content ,

2. meat - description, weight, fat content , proteins content

3. dairy products - description, quantity, fat content

2. This was just easier to read by database users. I could have done portal with all calculation and summaries, but it was quicker, faster and everything was in order. There are many other conditional relationships (that are not included in the example) that drives other data to this these tables under special conditions that are not the same to each other.

Hopefully, it clarify a scenario. Thank you for a response.

[T-Square]

Well, speaking theoretically...

One solution might be to create a Cars super class that holds the common data for all cars, and then have a Special Attributes table that contains fields for Attribute and Value. The attributes would be defined by a third table that allows you to define what attributes exist. The Special Attributes table is then displayed through a single portal listing related attributes... So you have a Subaru Car, and add a special attribute of EngineType value Wankel Rotary (my example sucks, but that's the general idea...)

A simpler idea would be to include all the various fields for the different subclasses in a single table designation without displaying them on every layout. This is simpler but ultimately less flexible, and would really only work if you're not talking about hundreds of fields...

Without an example of exactly what you're doing, it's hard to do much more than think theoretically...

David

[Twd70]

Tom (my namesake!)

as you put it

x - table - field

food -food - description, weight, sugar content, fat content, quantity (so all fields)

1. then create different layouts based on the same table, hiding the fields you dont want.

2. you would need a 'create car screen' which only has name and model on. so that when a user adds a new car they choose a model, clicking continue will take the user to the right model layout.

3. in the relationships view, create a copy of the cars table for each of the different models. this will allow you to filter which model you want to see in the portal.

i cant really explain the final step untill i understand what you want in the 'folders'

e.g.

i have a 'status' layout, which shows the status of an action the user needs to do, sorted into tabs.

so there are 5 different portals, each filtering a different status.

portal A show all those marked as 'to quote'

portal B shows all those 'quoted'

c = 'sold'

etc etc

T-square is definitely pointing yo in the right direction