OR relationships

[teckert]

Is there a simple way to setup an OR relationship, i want to select records from a table based on one of 4 different variable (3 will be ORs and 1 will be an AND

[Ender]

Use a multi-key to create an OR relationship. The multi-key can be on either side of the relationship or both, depending on what you're doing. Keep in mind that using a calculated multi-key on the child-side of the relationship will mean you can't use that relationship for creation of related records.

An example multi-key is a calculation like this:

ID_Category_mk (calculation, text result) =

ID & " " & Category &

[Søren Dyhr]

The 3 of them in a returndelimited textfield or repeating field while the AND is the other part of a multicriteria relationship definition.

The returndelimited key lets call it orKey is a calcfield defined to return text, containing this:

aField & "

Billede1.pdf

[teckert]

Not sure i understand that answer, let me explain the problem further i have a table of tests ids, score and dates. I want to return all of the scores that are either type 0710, 0711 or 5710 and are associated with the proper student thru SS#

how would you establish the key if the criteria is in a test type field??

[Ender]

In this case, the parent side would have a multi-key with each specific type on a separate line, and the match key would have just the ID and Type:

Student fields:

StudentID (number)

StudentID_Test_mk (calculation, text result) =

StudentID & " 0710

[Søren Dyhr]

Ah! ... a slight change; take at look at both pages of this upload:

--sd

P.S. Ender quite pre 7'ish - isn't it - and the benfits are??

Udennavn.pdf

[Ender]

On second thought, since this is in FM7, you need not include the StudentID field in the multi-key and the match key if you use a multi-criteria relationship. In this case the multi-key would simply be the return separated list of Test Types. This would be matched with the TestType field in the Test table and an AND criteria would be added for Student::StudentID = Test::StudentID

[teckert]

thanks, will try this later, had a priority interrupt

[Lee Smith]

Hi teckert,

Take a look at the file I attached to this recent thread, [color:"green"] Re: Displaying certain records in a portal by Clicking Here

It is a v5 file that converts easily to v7, it's ugly, but it is easy to see how you can do this using a global field in the left side of your relationship.

HTH

Lee

[teckert]

Thanks that worked great, it returns all of the tests of those particular type, now one more question, i need to select the maximum score, so max works great

however i need to return other data that is in the record that contains the max score, such as test date

thoughts??

[comment]

Instead of using max, sort the relationship so that the max record is the first related record. Then you can refer to all the other fields in that record as TO::field.

[teckert]

Thought this was going to be easy your solution was so simple, unfortunately and I do not understand why the following occurs

Using a calculation field, I access the result of the sorted data, FM7 indicates it is an unstored value and therefore nothing is being moved to the other fields. Trying the same thing with a text field or number field the fields do not get moved from the related table. i'm confused

[comment]

I am afraid I do not understand. What is meant by "moving" data?

[teckert]

trying to move information from a related table to the main table in the database using a calculation field

main table::score = related table::score

[comment]

To me, "moving data" means deleting it from one location and placing it in another, e.g. cut & paste. Please understand that precise terminology is the key to understanding your problem.

I presume you mean a calculation field in the main table referencing a field in a related table?

Any calculation field that references a related field will be forced to be unstored. Why is that a problem? The calculation will still compute and show the correct result.

[teckert]

i need to copy the value from the related table into the main table and at this point the calculation results in nothing being stored. is there some other technique i can use to get the data placed in the main table

[comment]

Ok, let's take this slowly:

1. Is your relationship working? If you place a portal into the related table on the main table layout - do you see all the records that you expect to see?

If yes:

2. Is your relationship sorted? In the portal, is the record with the maximum score in the topmost row? (Do not define the portal itself to sort records - do this in the Define Relationships window.)

---

BTW, I object to the term "copy" as well. To copy something, you select it and choose Edit>Copy from the menu.

[teckert]

relationship working, no problem

actually sorting is set at both relationship and portal level

how about duplicating data??

[comment]

3. Go to layout mode, and place two fields on the main table layout: (a) the field 'score' from the related table; (: the field 'test date' from the related table.

When you switch to Browse mode, do you see the max score and its associated date? (It would help me if I knew the name of the related TO).

---

"duplicating" is no good either - the very purpose of working with related data is not having to duplicate it. The data stays where it is. We are merely refering to it.

[teckert]

will have to check in morning, app is on my pc at work

i don't really want to duplicate the data either as i understand reason for not duplicating, however could not think of a better way to perform function i needed maybe you would have a better idea that would not take a ton of processing time.

the problem is as mentioned above, i have a table of test scores with dates, passing scores and some other data called "PRAXIS Test Results". Each student make take a number of different tests, any number of times until they pass. each time they take a test a new record is generated in "PRAXIS Test Results".

for one particular certification a student needs to take test 0710 (0711 and 5710 are variants of 0710), test 0720, 0730 and 0511. these tests need to be completed before the end of their sophmore year.

i am trying to create a report or display which shows which tests have been taken and even more importantly which test have not been taken for each of the students that are in their sophmore year

What i was attempting do was generate all of the data in the main table based on calculations in order to make processing as fast as possible. i could write a script for this but it would take an excessive amount of processing time

thanks for your help

[Vaughan]

Pipeline-ing. That's what you call using calculation fields to carry related data through related databases.

It's completely unnecessary with FMP 7.

[teckert]

so vaughan, do you have a suggestion of how to get around the problem??

[teckert]

fixed, thanks for all your help, data was there just was not looking at it properly had some fields an not calculated, changed them all to calculated and added all data to a columnar report and voila!!!

[Rob 7 Collins]

(Seriously, I'm not as well versed in FMP as the other respondents here ...)

it seems to me the "multi-key" idea is a work around. If the FMP developers wanted to intrinsically support OR relationships they would have done so in the relationship definition. the disadvantages to this method are the requirement of a derrived key (and the implicit requirements of uniqueness that are harder to ensure in a calculation on variable data), the fact that field options have to be changed to change the relationship (as opposed to only working in the relationships graph when altering relationships), and that each time an action is taken (such as goto next record) in the mutli-keyed table a few calculations per record are required.

An alternative, one I'm sure is deficient in very real respects too, is to use an intermediary table, often called a map or a join table. You might check out this thread.

-- my post contains a sample file. It's harder to impliment with mature data sets especially with lots of records, because you have to populate the map table. Aside from this though, it requires no difficult or unusal practices; everything here works the way FileMaker's GUI suggests it should, with relations always edited in the relationships graph, keys relating intrinsically to their tables rather than a calculation.

The FMP documentation is all you need to understand this structure at a glance, and maintain it.

Maybe this will suit your needs (?)

[Ender]

Well, multikeys ARE supported and do work well for some purposes. They carry a little overhead, but can significantly simplify the interface, so are often worth it.