Need help on multi-criteria selfjoin

[omega]

Hi All,

I am trying to create a self-join relationship for a table and am not sure of how to define it.

The fields in the table to be used for the join are period number, fiscal year, department head code and account number. I want the join to select all those records whose department head code = 1 and account number != 144006.

So I create two calculation fields

cField1 = period number & fiscal year & Department Head Code & account number.

Now I dont know how to define the second calculation field.

A part of it will be

cField2 = period number & fiscal year & 1 & what should be here.

If I omit the account number from the selection criteria, then I am able to fetch records for all the other conditions. Then I am looping through every record and omiting the record if its account number is 144006.

But I am sure there must be a way to handle this condition in the calculation field.

Any ideas is greatly appreciated.

Thanks

[omega]

Can anyone please share some ideas on how to implement this. I am really struck here and dont know how to proceed.

Thanks

[Matthew F]

1. Create a global field "Include Head" to allow the user to indicate which head to select for and enter "1".

2. Create a global field "Exclude Acct" and enter 14406.

3. Use the following calculation as your 2nd match field:

period number & fiscal year & Include Head & If(Acct num = Exclude Acct, "EXCLUDE", Acct num)

Hope that helps.

p.s. its a lot easier in FMPro v.7+ because the program includes conditional operators in the definition of relationships

Edited April 17, 2007 by Guest

[omega]

Thanks so much mfero. I implemented your logic and it worked great.

Though I was breaking my head on how to implement it, I just couldn't figure it out.

Again, thank you so much.