Relationships with Multiple Fields

[BlackWolf]

I have been working with FileMaker redesigning a database for my work, and I have run into a problem time and time again. When working with other databases (such as MySQL), I often want to run a query something like this: [color:"blue"]"SELECT * FROM jobs WHERE job_number='1' AND client_id='7'". I have tried to get this [color:"red"]AND part into my FM databases, but I'm having trouble.

Is there a way to relate two files using more than one related field? For example, I had a file called "materials" which has a radio button with the options "Duty Free" and "Non Duty Free". It would be nice to be able to pull up a report that lists all items which are "Duty Free", all the "Non Duty Free" items, and display totals for each with the $ amount of duty and the final total of everything. But I couldn't find a way to make a relationship that would show the items which had the correct job number [color:"red"]AND duty/non duty.

The best solution I have found so far is to separate everthing into different files. So now I have two files instead for the Duty and Non Duty items.

Does anyone know of a better way? Thanks!

[kennedy]

Create a calculation field that combines the two fields... you could concatenate them... or negate the job number if duty free... or however.

Then make the relationship on that calculation.

[Anatoli]

RE: So now I have two files instead for the Duty and Non Duty items

How those db's differ? Isn't that only attribute?

[BlackWolf]

The only real difference is that the Duty items have a 15% markup on the total. Other than that, they are the same.

The idea of combining fields with a calculation is interesting... but it feels like a workaround. A lot of things have felt that way when I have been reading this forum. There is a way to do most everything, but it just might not be easy or straightforward.

[Ugo DI LUCA]

Workaround

It's the natural way to answer your first "litteral" querry :

"SELECT * FROM jobs WHERE job_number='1' AND client_id='7'"

job number = 1 and client id =7 ----> 1&"-"&7

It surely isn't a workaround. You could turn around the problem for century else way.

Yur Duty/Not Duty issue could be solved within the same file, using a Multikey, for example

job N

[Anatoli]

BlackWolf said:

The only real difference is that the Duty items have a 15% markup on the total. Other than that, they are the same.

The idea of combining fields with a calculation is interesting... but it feels like a workaround. A lot of things have felt that way when I have been reading this forum. There is a way to do most everything, but it just might not be easy or straightforward.

That is still job for single database. Just mark items and base calculation on that.