"AND" "OR" Relationships

[TOJ]

I am no expert here so please forgive me if there is an obvious solution to this problem. I am currently developing a solution for a friend and would like to have a find layout with a portal showing related records. There will be many fields on the layout which are not in the portal, that the user can input information into and the corresponding related records will appear in the portal. I know this can be done with a find request but I would like the portal records to simply update as the user alters the information in the find layout fields keeping in mind that not all field are required to contain data. I think this can only be achieved by an "and" "or" relationship. I don't believe FP7 supports "or" relationships but I have read elswhere in this forum, something about using multi-keys to produce an "or" relationship. As mentioned, I am no expert and I have no idea what a multi-key is or how to use it.

The other option (if I'm not loosing my mind!), I thought that I read somewhere that it was possible in FP7 to run scripts when entering and leaving fields? I have been unable to locate anything about this in the help menu. Is this possible and how do I set the field preferences to perform scripts? This option (if possible) has limitations though. If the user enters a field and types in some data but does not leave the field the perform find script set to run upon exiting the field wont run.

Any assistance with this dilema would be appreciated.

[Søren Dyhr]

First question ...read on McDuff here: http://www.fmforums.com/threads/showflat.php?Cat=0&Number=124596&page=&fpart=1&vc=1

Second question:

Should be somewhere on your installation disk...Or you can find it here: http://www.filemakerpros.com/PLUGINEX.zip

--sd

[Ender]

TOJ, a multi-key is simply each key listed in a text field separated by carriage returns. These are usually built in calc fields like this:

multikey (calculation, text result) = field1 & & field2 & & field3

It's not clear if you're looking for an "and" result (matching just related records that have ALL of the selected criteria,) or an "or" result (matching those records that have ANY of the selected criteria.) "Or" relationships are built with multi-keys, while "and" relationships are built by adding additional criteria to the relationship definition. Building one or the other is easy, but building a way to do both, depending on what the user wants to do is much more difficult.

[TOJ]

Thanks for the reply fellas. I had allready read the thread on your link Soren but it relates to range relationships. It was mentioned that multi-keys must be used for an "OR" relationship and thats where I was comming from in my original post. Let me see if I can further clarify this problem-

There are 12 fields on a layout and a portal that will show records based on the relationships between the 12 fields and the portal records. If the user types "red" in field 1 of 12 then all records in the portal with the match field = red are displayed in the portal. If the user tabs accross to field 3 of 12 and types in "1234" then all records in the portal with the "red" in field 1 of 12 and "1234" in field 3 of 12 are displayed, and so on.

I cannot simply build a multipart "AND" relationship for all 12 fields because no records will be displayed in the portal until all 12 fields are matched to the related fields in the portal.

Well you learn something every day, I've been using calculation field to build relationships for years but never called them multi-fields. Can you please explain how I would go about building a relationship on a calculation field that is required to cover hundreds/thousands of possible combinations? If I simply add the text together, doesn't FP still require an exact match of the entire content of the related fields?

Any help?

[TOJ]

Thanks for the reply fellas. I had allready read the thread on your link Soren but it relates to range relationships. It was mentioned that multi-keys must be used for an "OR" relationship and thats where I was comming from in my original post. Let me see if I can further clarify this problem-

There are 12 fields on a layout and a portal that will show records based on the relationships between the 12 fields and the portal records. If the user types "red" in field 1 of 12 then all records in the portal with the match field = red are displayed in the portal. If the user tabs accross to field 3 of 12 and types in "1234" then all records in the portal with the "red" in field 1 of 12 and "1234" in field 3 of 12 are displayed, and so on.

I cannot simply build a multipart "AND" relationship for all 12 fields because no records will be displayed in the portal until all 12 fields are matched to the related fields in the portal.

Well you learn something every day, I've been using calculation field to build relationships for years but never called them multi-fields. Can you please explain how I would go about building a relationship on a calculation field that is required to cover hundreds/thousands of possible combinations? If I simply add the text together, doesn't FP still require an exact match of the entire content of the related fields?

Any help?

[TOJ]

Thanks for the reply fellas. I had allready read the thread on your link Soren but it relates to range relationships. It was mentioned that multi-keys must be used for an "OR" relationship and thats where I was comming from in my original post. Let me see if I can further clarify this problem-

There are 12 fields on a layout and a portal that will show records based on the relationships between the 12 fields and the portal records. If the user types "red" in field 1 of 12 then all records in the portal with the match field = red are displayed in the portal. If the user tabs accross to field 3 of 12 and types in "1234" then all records in the portal with the "red" in field 1 of 12 and "1234" in field 3 of 12 are displayed, and so on.

I cannot simply build a multipart "AND" relationship for all 12 fields because no records will be displayed in the portal until all 12 fields are matched to the related fields in the portal.

Well you learn something every day, I've been using calculation field to build relationships for years but never called them multi-fields. Can you please explain how I would go about building a relationship on a calculation field that is required to cover hundreds/thousands of possible combinations? If I simply add the text together, doesn't FP still require an exact match of the entire content of the related fields?

Any help?

[TOJ]

Soren,

I downloaded the zip PLUGINEX from the link you posted but I don't understand what I am meant to do with it. I can't find anything that relates to triggering a script to run upon entering or exiting a field. This would be my first attempt at using plugins so please excuse my ignorance. Can you please give me a bit more advise?? Thank you.

[TOJ]

Soren,

I downloaded the zip PLUGINEX from the link you posted but I don't understand what I am meant to do with it. I can't find anything that relates to triggering a script to run upon entering or exiting a field. This would be my first attempt at using plugins so please excuse my ignorance. Can you please give me a bit more advise?? Thank you.

[TOJ]

Soren,

I downloaded the zip PLUGINEX from the link you posted but I don't understand what I am meant to do with it. I can't find anything that relates to triggering a script to run upon entering or exiting a field. This would be my first attempt at using plugins so please excuse my ignorance. Can you please give me a bit more advise?? Thank you.

[Ender]

If the user tabs accross to field 3 of 12 and types in "1234" then all records in the portal with the "red" in field 1 0f 12 and "1234" in field 3 of 12 are displayed, and so on.

So this is AND criteria, but only when something has been entered.

For this, use calc fields in the parent table and the related table and change the relationship to be based on these calcs:

In the parent table:

Color_Calc (calculation, text result) = if(isempty(Color); "All"; Color)

Number_Calc (calculation, text result) = if(isempty(Number); "All"; Number)

...

In the related table:

Color_Match (calculation, text result) = Color Field & & "All"

Number_Match (calculation, text result) = Number Field & & "All"

...

Then the relationship is:

Parent<=>Child

Parent::Color_Calc = Child::Color_Match

AND Parent::Number_Calc = Child::Number_Match

AND ...

With multikeys, the relationship matches to any of the paragraph separated lines (creating the OR relationship.)

[Ender]

If the user tabs accross to field 3 of 12 and types in "1234" then all records in the portal with the "red" in field 1 0f 12 and "1234" in field 3 of 12 are displayed, and so on.

So this is AND criteria, but only when something has been entered.

For this, use calc fields in the parent table and the related table and change the relationship to be based on these calcs:

In the parent table:

Color_Calc (calculation, text result) = if(isempty(Color); "All"; Color)

Number_Calc (calculation, text result) = if(isempty(Number); "All"; Number)

...

In the related table:

Color_Match (calculation, text result) = Color Field & & "All"

Number_Match (calculation, text result) = Number Field & & "All"

...

Then the relationship is:

Parent<=>Child

Parent::Color_Calc = Child::Color_Match

AND Parent::Number_Calc = Child::Number_Match

AND ...

With multikeys, the relationship matches to any of the paragraph separated lines (creating the OR relationship.)

[Ender]

If the user tabs accross to field 3 of 12 and types in "1234" then all records in the portal with the "red" in field 1 0f 12 and "1234" in field 3 of 12 are displayed, and so on.

So this is AND criteria, but only when something has been entered.

For this, use calc fields in the parent table and the related table and change the relationship to be based on these calcs:

In the parent table:

Color_Calc (calculation, text result) = if(isempty(Color); "All"; Color)

Number_Calc (calculation, text result) = if(isempty(Number); "All"; Number)

...

In the related table:

Color_Match (calculation, text result) = Color Field & & "All"

Number_Match (calculation, text result) = Number Field & & "All"

...

Then the relationship is:

Parent<=>Child

Parent::Color_Calc = Child::Color_Match

AND Parent::Number_Calc = Child::Number_Match

AND ...

With multikeys, the relationship matches to any of the paragraph separated lines (creating the OR relationship.)

[TOJ]

Ender,

I must take my hat off to you. It works beautifuly with as many match fields as I require. I did not know that a caraige return in a calculation would allow filemaker to match any of the individual text strings on different lines. Good stuff. Have you got any idea about triggering scripts when entering or exiting a field? Although no longer required for this problem, the capability to do it would be very useful in other applications.

[TOJ]

Ender,

I must take my hat off to you. It works beautifuly with as many match fields as I require. I did not know that a caraige return in a calculation would allow filemaker to match any of the individual text strings on different lines. Good stuff. Have you got any idea about triggering scripts when entering or exiting a field? Although no longer required for this problem, the capability to do it would be very useful in other applications.

[TOJ]

Ender,

I must take my hat off to you. It works beautifuly with as many match fields as I require. I did not know that a caraige return in a calculation would allow filemaker to match any of the individual text strings on different lines. Good stuff. Have you got any idea about triggering scripts when entering or exiting a field? Although no longer required for this problem, the capability to do it would be very useful in other applications.

[Søren Dyhr]

I downloaded the zip PLUGINEX from the link you posted but I don't understand what I am meant to do with it.

You should drop it in the folder Extentions next to your Filemaker App. Quit Filemaker and restart - and now will 7-8 new functions be listed under External functions in the calc'def.

This is here where you find: XMpl_StartScript( filename; scriptname ) ...my guess is that you put it inside a Evaluate(XMpl_StartScript( filename; scriptname );triggerfield) - but as it is, havn't I any particular desire to use it in my solutions, where I rely entirely on calcf'ields and buildin validations!

--sd

[Søren Dyhr]

I downloaded the zip PLUGINEX from the link you posted but I don't understand what I am meant to do with it.

You should drop it in the folder Extentions next to your Filemaker App. Quit Filemaker and restart - and now will 7-8 new functions be listed under External functions in the calc'def.

This is here where you find: XMpl_StartScript( filename; scriptname ) ...my guess is that you put it inside a Evaluate(XMpl_StartScript( filename; scriptname );triggerfield) - but as it is, havn't I any particular desire to use it in my solutions, where I rely entirely on calcf'ields and buildin validations!

--sd

[Søren Dyhr]

I downloaded the zip PLUGINEX from the link you posted but I don't understand what I am meant to do with it.

You should drop it in the folder Extentions next to your Filemaker App. Quit Filemaker and restart - and now will 7-8 new functions be listed under External functions in the calc'def.

This is here where you find: XMpl_StartScript( filename; scriptname ) ...my guess is that you put it inside a Evaluate(XMpl_StartScript( filename; scriptname );triggerfield) - but as it is, havn't I any particular desire to use it in my solutions, where I rely entirely on calcf'ields and buildin validations!

--sd

[TOJ]

Soren,

Thanks for the advice, I'll try it tomorrow when I get back to my other computer. I can see it would be usefull to run a script that changed layouts based on information in the trigger field without the opperator having to manually key a button.

[TOJ]

Soren,

Thanks for the advice, I'll try it tomorrow when I get back to my other computer. I can see it would be usefull to run a script that changed layouts based on information in the trigger field without the opperator having to manually key a button.

[TOJ]

Soren,

Thanks for the advice, I'll try it tomorrow when I get back to my other computer. I can see it would be usefull to run a script that changed layouts based on information in the trigger field without the opperator having to manually key a button.