Jump to content
Sign in to follow this  
omega

Need help on multi-criteria selfjoin

Recommended Posts

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

Share this post


Link to post
Share on other sites

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

Thanks

Share this post


Link to post
Share on other sites

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 by Guest

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.