Self-Join/Multi Table Occ. for One Table/Portals

[HonkyCat]

I need help! Or tell me I'm making this too difficult and there is an easier way!! It's driving me crazy!

I have two tabels (Company and Contacts). The goal that I'm trying to achieve is to be in the Company table with a portal showing the different contacts for that particular company. We have 4 main types of contacts for each company... Reporting, Billing, Shipping, and Purchasing. We can have multiple contacts for each category. I want to have a layout where Company information is the main table in the layout, and a portal showing the contacts. I want to have a tab labeled "Report Contacts", another tab labeled "Billing Contacts", another tab labeled "Shipping Contacts", and a 4th tab labeled "Purchasing Contacts".

When the user clicks on one of the tabs (lets say "Reports") then it shows all of the Reporting contacts we have for that particular customer. Then if they click on "Shipping Contacts" tab, it takes them to an identical layout that instead shows all of the Shipping contacts we have for that particular customer.

I have a field in Contacts called "ContactType". The user would choose from a pull down list the type of contact it is, (shipping, billing, reporting, or purchasing). Then I have 4 calculation fields in the Contacts table. The first is called "ReportContact"; it's indexed, from Contacts = If(ContactType = "Reporting"; "Reporting"; " "). The 3 other calculation fields are exactly the same as this one, but replace, "shipping", "billing", and "purchasing" respectively in each. I hope that makes sense.

Then I made a self-join relationship to each of the 4. 1st relationship is from Contacts (Table Occurance) to Contacts 2 (TO) using the Company ID and ContactType as the Key Fields and Company ID and ReportContact as foreign keys. Again, made another self-join relationship...this time from Contacts 2 (TO) to Contacts 3 (TO). Company ID & Contact Type as key fields and Company ID & ShippingContact as foreign keys. I did this 2 more times. So basically I have a total of 5 Contacts table occurances on the graph.

I then put a portal on the first layout (to show the Reports contacts), I used the Contacts 2 (TO) and the fields from that same table occurance for the fields. Then on a second layout I used Contacts 3 (TO) to show the Shipping Contacts. Then a thrid layout I put a portal showing Contacts 4 table occurance for the Billing Contacts and fields from that table occurance. And so on for Purchasing Contacts on a fourth layout.

The Reports Contacts works perfectly, shows all the right data etc., but none of the other portals display ANY information at all, even though the fields are marked correctly as a shipping contact, or purchasing contact, etc..

What am I doing wrong??? Please help! Thanks!

Kristine [color:"red"] [color:"red"]

[CobaltSky]

Hello Kristine,

Yes there is an easier way. Several in fact.

First of all though, I assume from the way you've framed your question that you are talking here about FileMaker 7, but the side-bar currently states that you're working with FileMaker 6?! I guess you're trying to throw us off the scent?!

As to your problem, you really don't need multiple layouts or multiple relationships (or table occurrences) to achieve what you've described. That's true in either v6 or v7, though the method for achieving it differs between versions. You also don't need all those calculation fields in the contacts table.

What I suggest you do is create a field called 'ContactFilter' in the Company table, then define the relationship between the Company and Contacts TOs as:

[color:"#333333"]CompanyID = CompanyID

AND

ContactFilter = ContactType

Then set up a single layout with a portal based on the Contacts TO and place tab buttons on it that are attached to the Set Field[ ] command, with each button configured to place one of the contact type names into the ContactFilter field, viz:

Set Field [ ContactFilter; "Reporting" ]

When the user clicks on a tab button, the result will be that the portal updates to show the contacts (if any) of the corresponding type.

[HonkyCat]

Hi Ray,

You're right about me working in FM 7, I was just testing you. Old habbits die hard.

Thank you so much for your response. I'm not currently at work so I can't do what you suggested right now. But judging from what you wrote, I guess I was REALLY taking the long way around!! I'll let you know how it works tomorrow, but I just wanted to say thanks in advance! I love this forum!!

Kristine

[HonkyCat]

Hey Ray,

Just wanted to tell you I did what you said and it works perfect!! Thanks for taking the time to help me! I really appreciate it!!

Kristine