Multiple field relationships

[VARJohn]

Thank you in advance for any help I may receive on this !

I am brand new to Filemaker, as a friend had a base done in 5.5( We both immediately upgraded to 6.0)and he asked me to work on it for him, as I have a lot of RBASE and Approach experience.

This works completely different than I am use to, and I would like to run this past the forum to get your take on what I need to do (yes I spent much time looking in archives unsuccessfully).

Orignally we had single flat file (7500 records), with 5 sets of duplicated fields for 5 contacts. I exported and concated all of these to a secondary file called CONTACTS which has 37500 records(with a single key field), then eliminated blanks down to 20000 records.

Problem: A lot of duplicate data found in CompName in both bases. So I re-exported with both CompName and City in both bases.

So now I have two databases: COMPANY and CONTACTS which have two matching fields CompName and City which are in both bases. I would like to have BOTH FIELDS in the RELATIONSHIP, so that only if both the CompName and City match, from both bases, the CONTACTS would appear in the portal.

Can this be done ?

In Approach this is a no brainer, it's done in a JOIN TABLE which is completely graphic.

Sincerely,
John Martoccio
John@fastad.com

 

[kertusa]

Hi John,

If I understand your situation, I think the appropriate thing to do is use a calculated field as the foreign key:

case(
not isempty(CompName) and
not isempty(Contact),
Compname & Contact
)


This will return a concatenation of the two fields you would like to use as a key. I would recommend you also develop a strategy for matching on just one or the other field as well, perhaps by using a primary key of just CONTACT or just CompName and displaying those records in a seperate portal. That would give the user visibility on things that may include duplicates or have a typo in one of the match fields.

Give that a shot, let the forum know if it doesn't work out for you.

Good luck.

 

[VARJohn]

If I understand you correctly, using the method of "calculated field on foriegn key" will not show duplicates ? That won't work for me !

The idea is that for every COMPANY in the primary base, all records in the secondary base (CONTACTS) would appear in the portal, but using 2 not 1 keyed-relationship field.

This is really basic in every other database I have worked with (dbase, rbase, approach), but does not seem so in this one !

Thanks,
John Martoccio
John@fastad.com

 

[itsisnofun]

VARJohn

Don't know if this is what you are looking for. Create a new calc field in each databse joining those two fields and then use that field as your portal looup

Mike

 

[VARJohn]

I realize to those that use this program a lot, this is a
stupid question, but here it goes anyway:

What is the exact syntax for the formula for combining two fields together as one calculated field ?

I created a calculated field of ID = Company: & City: (in both Company and Contacts)
Then I defined from Company to Contacts with the relationship of : ID = ID

Although I could see the same information in both bases independently, they didn't pull information in the portal.

What am I doing wrong ?

-john

 

[itsisnofun]

VARJohn

The formula for that would be company & city (or exact field names) with the output to be text.

When you setup your portal up did you use the relationship you created from company to contacts in your company file?

Then add the fields you want from contacts in the portal window. To do this when you drag a field from the tool bar the first field on the top lets you know which database you what the field from. Choose the relationship you created and then it will show you fields from that database.

Mike

 

[VARJohn]

Thank you so much !

I did not realize that even though I use the the same name relationship name, it severed the connection to the portal, thereby requiring re-assignment of all the fields to work !

I had the method figured out from day one, it was the portals behavior that threw me ! I also discovered that not only do the fields have to be assigned, but the portal window itself has to have the link added back too.

Again, thank you all for your help.

John Martoccio