Relation to a Relation?

[AeroGuy]

Hey All,

I'm sortof new to FileMaker so please bear wtih me... is it possible to define a Relation to another Relation? If so, how?

For example:

1.InvoiceList-file is a table that holds certain ID#s (Invoice Numbers) and their respective CompanyNames.

2.ContactList-file is a DB that holds all the CompanyNames and their respective information(addresses, etc.)

3.InvoiceInfo-file is a DB that basically holds a report(s) that should read in information from both files 1 and 2.

In InvoiceInfo, I can choose a certain ID# using a value-list that reads-in all the ID#s from InvoiceList. Once I choose the ID#, I have it so that the respective CompanyName from InvoiceList is read-in as well. My problem, however, is that I would like the company's address to automatically read-in from ContactList once the CompanyName shows up from InvoiceList.

Thus, I already have one relationship that reads in CompanyName based on ID# (from InvoiceList); can I have a relationship that reads in CompanyAddress based on CompanyName (which was already read-in from elsewhere)?

If this question has already been answered somewhere, please be kind enough to direct me there.

Thanks,

AeroGuy

[Ugo DI LUCA]

What strangely seems to be missing here is a Company_ID.

The Company_Name would come to the Invoice File through a relationship Invoice:Company_ID::Company:Company_ID.

The Company_ID would be entered by lookup through RelationshipLineItem:InvoiceN

[AeroGuy]

Ok...

I solved the problem by just combing the two files (InvoiceInfo and InvoiceList). There was really no point to having the ID#s and CompanyNames in one database, then to have the reports that use that info in other database... thus, I'm able to bring in all the Company Information into my report with one simple relationship. Thanks for those suggestions, though.

Now, I would like to employ something similar to what you just said to another database/file (using a lookup), but I keep getting a certain error when I try to define/setup my lookup (this is very similar to the previous problem)... maybe someone can point out what I'm doing wrong...

I have files:

1. JobFile-- that contains JobNumbers and respective CompanyName

2. ContactFile-- that contains CompanyName and respective info (ContactInfo)

3. LabFile-- that is a report.

In LabFile, I choose a JobNumber from from ContactFile using a value-list. After choosing the JobNumber, a relationship (LabJobNumber =:: ContactJobNumber, related file: JobFile) reads CompanyName into LabFile from JobFile.

Now, I need to use a LOOKUP to read in the ContactInfo that goes along with the CompanyName into LabFile as well (this ContactInfo, of course, is found in ContactFile). This is what I've been doing: From LabFile, I make a relationship (LabContactInfo =:: ContactInfo, related file: ContactFile), then, I go into Define Fields and double-click on LabContactInfo, click on "Looked-up value" and enter a window called "Lookup for Field LabContactInfo." In this window, I change the relationship to the one I ctreated, then go down the list and choose the field ContactInfo. Finally, I click the "OK" button and get the ERROR: "This lookup uses the field "LabContactInfo" in its definition. Choose another field."

What does this error mean? What should I do?

~me

[kennedy]

AeroGuy said:

If this question has already been answered somewhere, please be kind enough to direct me there.

About 10 threads down from yours... this one answers your question, I think.

But do not ignore the point made by Ugo... Companies should have IDs... and you should link up DBs using the IDs, not the Names.

HTH.