Relationship Question

[BKWill]

Hello my name is Will, and I've been using File Maker for almost a year now. I recently upgraded from 5.5 to version 7. I love it. The new graphical interface for Relationships is great. However I'm stuck trying to decide the best way to structure a tracking DB I've been working on:

Problem - 1.) I need to create a database that lets me collect Contact Information,2.) track Indivulal Items from our clients that are sent to the Contacts by displaying the date it was sent, 3.) it needs to be show multiple items and dates, 4.) It needs to show items #2 & #3 for each client.

Filemaker 5 Set Up: In the old version I have the main contact information layout, and a tracking layout. The Contact name is merged into each record in the Tracking Layout to identify the tracking info with the contact. I have then created a host of fields for each client - ( Fields inclucde: CD Sent, CD Sent Date, LP Sent, LP Sent Date, Comments, ect..) The issue is that I have to add and define 16 feilds in the database every time I get a new client, a process that is long, tedious, and lacks functionality.

---------------

Filmaker 7 Solution Basic Structure is the following:

(4 Tables - Please See Attached Image)

Contacts

Tracking

Tracking Items

Clients

My question is this:

What is the best way to get to display the Tracking Information for Each Client for Each Contact, and what are the relationships I would need to create?

You can see what my intial approach was, but I don't think it will work they way that I want it to.

Please advise,

Thanks,

Will

example.pdf

[Søren Dyhr]

To me does it look like a many2many structure, but it puzzels me quite why you have both tracking and tracking items. I would make Tracking Items the join table, and have Contacts an Clients on both sides of the join. But do I guess right that Clients not are a subset of Contacts?? You didn't attach an image???

If every thing is as my guessing goes will portal in the two "outer" tables show sorted by date the tracking.

--sd

[BKWill]

Sorry about that, I added the attachment. I think I'm following what you are saying, please take a look and let me know if you have any different thoughts. Thanks.

[Fenton]

I would agree with S

[BKWill]

Okay, I don't see how that set up will work without a fourth table, please correct me here. For any given contact I need to be able to go to the Tracking Layout and see a Master List of the current clients, and next to each client it should indicate if that contact was sent any promotional items and at what time. It needs to be able to represnt up 6 promotion items each with there own unqiue shipping date.

Wouldn't I have to combine the clients and there items into a 4th table, and connect that table to the contact?

[BKWill]

I'm sorry for not being clear, I hope this helps. I'm a company that gets hired by clients (Bands & Record Labels). My job is to send there music out to a list of contacts (who I call Tastemakers), The music comes in as many as 6 different forms, and at sometimes multiple forms are sent to the same tastemaker at multiple times. I need to be able to look at a tastemaker and see what they have been sent from each client and when. I also need to be able to record the tastemakers response to report back to the client.

My old method relied on me creating new fields for every new client to go into a tracking layout. After two years, it's getting complicated to manage. I'm trying to find a way to only have to add a new client to a table and still have the corresponding tracking fields, without having to duplicate them.

I hope this helps, I really appreciate everyone's support and comments.

[transpower]

I think you need these tables: Client, Tastemaker, Music, Client_Tastemaker_Music (a join table) (which could be displayed in a portal in a layout in the other tables). By the way, -Queue- is a musician and may already have such a database created.

[-Queue-]

Negative, we have no clients or need for such a system, we simply throw CDs to people at concerts.

[Søren Dyhr]

I think you need these tables: Client, Tastemaker, Music, Client_Tastemaker_Music (a join table) (which could be displayed in a portal in a layout in the other tables

Well ... I think Transpower is right here. Try to redraw your relations according to it - and remember that you don't have to use calc'fields to pull data a relation away any more...

--sd

[BKWill]

Thanks so much Soren and everyone else, I'm heading in to work now and will try your suggestions and report back later. Thanks.

Will

[BKWill]

I

UpDate.pdf

[Fenton]

It seems to me that rather than a "chain" of TOs you need more of a "star" pattern.* You have 3 main entities, Clients, TasteMakers and Music Products. Then you have a Tracking table (or Tracking and Tracking Line Items) which is a multi-join, a flat file where the combination of all 3 entities comes together, for a single "transaction" (happens at a date & time).

All reports would be done there. You can easily sort and summarize by Client, then by Date to get a report like the above. Those 2 fields would be in 2 leading Subsummary Parts I would think, with the items in the Body.

Whether or not you create separate Tracking and Tracking Line Items tables would depend on what you want to see in a portal in Clients. You can either just have line items. Or, with separate tables, you can show just dates, then select a date to see the items for that date. Or you can show the items, but only of the latest date.

If you want to specify what a group of transactions is, then you need a separate Tracking table. Say you send a few items on one day, and a few more the next day, all for the same client, to the same Taskmaker; you might want to consider that as 1 record in Tracking, with the 1st and last dates, and keep the items separately in a line items table.

*Unless a Taskmaker is "assigned" to a Client, in which case a chain makes more sense.

[BKWill]

Fenton, how does this look? Is this what you were suggesting? (Please see attached)

Fenton Said:" All reports would be done there. You can easily sort and summarize by Client, then by Date to get a report like the above. Those 2 fields would be in 2 leading Subsummary Parts I would think, with the items in the Body."

Can you brake this down for me a bit more, I think I get it, but I'm still getting use to how FM7 works. All of the mailings will always happen at the same time for each client to all of the Tastemakers.

I want to be able see exactly the layout I listed above. This would be accomplished by creating a portal for clients and then inside of that portal create a new one for music?

Can you have portals inside of portals?

Update1.2.pdf