Stuck in loop: relationship-valuelist-relationship

[rnd]

I'm new to filemaker but I worked MS Access and bDase before. I'm a little stuck on the conversion from one program to the other. In Access you have a query tool.

I guess in filemaker the query tool is replaced by the valuelist.

My problem:

I have a table "COMPANIES" and another ""INTERVENTIONS"

In the table "COMPANIES" I have a field "company" and a field "relation" (relation is a valuelist wich could hold many values (dealer, client, relationtype3, relationtype4, and so on)

In the table "INTERVENTIONS" I made a field "dealer".

In access I would create a query that shows only the records from "INTERVENTIONS":"company" where the "relation" of the company is dealer and use that query in "INTERVENTIONS" in a valuelist "dealer".

One would only see the companies who are dealers in that valuelist.

In a valuelist I cannot use "relation" field as a second criteria for the "dealer" valuelist.

So actually I would like to have "company" and "relation" from the "COMPANIES" table as criteria for the valuelist "dealer". Since the name of the "dealer" is the first reference to the table "COMPANIES" I can't create a relationship between the two tables.

I think I'm stuck on the conversion from one application to another, but tell me if I need to get back to my learning books.

RND

[QuinTech]

Hi, RND. You're now seeing one of the things FM sorely lacked in versions 6 and below, which is now available in 7: Multi-keyed relationships. In order to do what you want in version 5, you have to create a calculated field in each file that concatenates the keys you want to use, and then relate those fields. That is, if you want a relationship based on city and state, you need to create a calc field (make sure it is a Text result) which is equal to [City] & "|" & [state] (the pipe character is commonly used for this, but it could be a different character, although a space is not recommended). Create a similar field in your foreign file, and relate these two.

It works, but it's a total drag, and it's one of the reasons why so many FM developers are so excited about FM7.

HTH.

Jerry

[QuinTech]

Oh, also, the value list is something COMPLETELY different. It would only be used in a relationship in very advanced situations.

[rnd]

You saved my mind.

It my mental construction almost collapsed under the pressure of the problem

[rnd]

I see I didn't edit my Filemaker version. It's set to Filemaker 5 but I use filemaker 7. I've changed it now. Sorry for that.

You said it was possible in filemaker 7 with multi-keyed relationships?

I could only find descriptions of this.

Could you give an example of an multi-keyed relationship?

[-Queue-]

A multiple predicate relationship is one where more than one criteria is used to determine matching records. In the relationship dialog, you select, for example, fieldA = FieldA, Add it, then select fieldB = FieldB, and Add it. Now the only related records are those where fieldA matches the related FieldA and fieldB matches the related FieldB. A real-world example: to display all uninvoiced transactions for a job in a job record's portal, create a relationship where ProjectNo = ProjectNo and constant <> InvoiceFlag, where constant is a calculation (or auto-enter) field in the main file equal to 1, and InvoiceFlag is a number field in the transaction file that's set to 1 when a transaction is invoiced.