Making a Database within a database

[picnichouse]

Hi,

I've been using filemaker for over a year with great success, but there is something I find myself wanting to do more and more often. I've never been able to imagine how to implement this request so have worked around it, but I thought I'd ask you pros -- maybe I'm overlooking a basic feature. I'll explain by using the db I'm working on now as an example.

I'm making a list of all the brands and varieties of tea available in Chicago supermarkets. There are attributes which all teas of a brand share, such as the store where it's found, the packaging type, and then there are attributes unique to the variety of tea, such as its name and the package weight. What I've been doing thus far is using a database with fields for each attribute and adding a new record for each variety.

Since there are over 50 tea varieties put out by some brands, this is a tedious and inconcise way to display the information, in my opinion. What would be clearer for me is something like this: You add a record for each tea brand in the store, and with that record are subrecords for each variety of tea. You can have one of these subrecords or add as many as you like. Is something like this possible?

Thanks in advance,

Andrew

[Mark Thiessen]

I think what you are looking for is a self-join. A portal in the current record will show related records from the same table ("database" for pre fmp7). Here is a better explaination from Filemaker Help.

About self-joining relationships

A self-join is a relationship in which both match fields are defined in the same table. Define a self-join to create relationships between records in the same table. Use a self-join in a portal on a layout of the current table to display a subset of data that's in the current table, such as all the employees of each manager.

When you define a self-joining relationship in the relationships graph, FileMaker

[Vaughan]

Not necessarily a self-join, a plain old relational db would suffice.

In this case I'd have a table for "Brands" and another related table for "Varieties". You could even go further and have more sub-tables for package sizes for instance. but the two tables will probably be enough.

[picnichouse]

Okay, so I created another table for the tea varieties... I guess I don't understand the process of linking them. Can anyone point me towards a tutorial on the subject? I'm not finding the manual or online help very useful...

Andrew

[picnichouse]

Okay, so I guess I'm confused about how I relate the two databases, the actual procedure. Can anyone point me in the direction of a tutorial on this sort of thing?

Thanks..

Andrew

[faada]

I just created something similar, this is how I did it, I'll use Tea Brand and Tea Type instead of my file names.

I set up one table for with a Brand Code and Description

a second table for Type

On both tables I have a field 'Brand' which I have used to establish a relationship between the 2 tables.

That is the basic type of relationship that you will need for what you want to do, you will than need to learn a bit about using portals, related records etc to display the data how you want to do it.

I'm not an expert, so there may be better ways, but I will tell you a bit about my setup:

On the brand layout I have BrandCode and Description, you may just need Brand, also on the layout is a portal which shows Type, Colour etc etc. (all the fields which record information about each type). I have buttons which bring up the Type layout in a new window and create new types for each brand in there, these are then displayed as a list in the portal of each Brand record.

Hope my description is not too muddled and is able to give you a basic idea of what can be done.

[picnichouse]

I think I found a better way to illustrate what I'm trying to do...

Look at the Task Management template that ships with FM7. See how you can enter an unlimited number of tasks at the bottom? I don't understand how that was done... I'd like to do that with restaurants.

Can anyone explain?

Andrew