Calculation - Relational problem

[Kris M]

I am getting results I do not understand from a relational - calculation situation and I need some help...

I have TO #1 that has a text field with properties:

autoenter - data - "Yes", (Actually in quotes)

prohibit mod flag checked,

validata only during entry set,

repetition = 1,

allow user override unchecked,

and autocreate indexes.

This TO is equijoined (equals sign in relationship) to TO #2 via a related field in TO #2 with properties:

calculation type = "Yes",

calculation result is text,

Do not eval flag is checked,

1 repetition,

global storage,

and evaluated from the context of TO #2.

The edit relationship dialog has TO #2 on the left and TO#1 on the right. TO #1 is also set to be sorted.

I then created a simple list type layout based on TO#1 and expected that the join would only let me have access to records flagged as "Yes" but that did not occur. I can see all records regardless of the setting in the field.

I verified that TO #2 contains only one record with the specified value - Yes.

I have used this method to filter TO's to produce value lists in the same solution and it works. For the life of me I cant see my error. How do I get to my goal of seeing only specifically flagged records in the layout?

TIA

KM

[comment]

The "left" side of the relationship (i.e the parent TO) is the TO of the layout. Since relationships can be bi-directional, the arrangement in the edit relationship dialog is meaningless.

[Kris M]

Thanks Carpal,

I knew that but I wanted to mention it anyway. Always err on the "More details are better" side.

Got a solution?

Kris

[comment]

I assumed you knew that the match field on the child side must be indexed? IIUC your description, the solution is to open the edit elationship dialog and switch the fields around:

TO1::globalField = TO2::indexedField

Who is "Carpal"?

[Kris M]

You just made my head spin a bit..

1. Carpal Tunnel shows up under your user name in email notifications of a response. I thought that comments was well... just a comment and that CT was your user name.

2. In your first post you said position in the edit relationship dialog does not matter... in your second post you said to switch the positions because child related field must be indexed so it appears that position does matter.

3. What determines child parent status of a TO? If layout is based on TO 2 then parent is TO 2 and child is TO 1? If layout is based on TO 1 then that is parent? (Just want to make sure i understand.

4. Can I just force an index by setting the child field to index always instead of changing the join direction??

Thanks,

KM

[Kris M]

I just switched the relationship direction and same eroneous result. I also tried forcing an index and same result.

Thx,

Kris

[comment]

Let me change the terminology here, because mostly you would use the 'parent-child' distinction to identify which is the 'one' and which is the 'many'. This obviously doesn't change with context.

However, if you place a portal to TO2 on a layout of TO1, Filemaker needs to indentify which records in TO2 are 'related'. It takes the value of the match field in TO1 and looks in the INDEX of the match field in TO2 to establish the related set. If the match field in TO2 is not indexable, this won't work.

Therefore, you need to switch the FIELDS each TO uses in the relationship - not the position of the TO's (which is meaningless). IOW the relationship:

TO1::globalField = TO2::indexedField

is exactly the same as:

TO2::indexedField = TO1::globalField

[Kris M]

Ok comments.

I created a global field in TO 1 that is of calculation type and is set to field.value where state yes or no is stored for each record.

I created a calculation type field in TO 2 that is not global and set to text value of "Yes"

I created the relationship between the fields and went back to the list layout based on TO 1. Two records with Yes and one record with No are visible.

Did i miss something in your reply? If I understood right the index field is now in the parent table as it should be.

Thx,

KM

[comment]

I am afraid it doesn't make much sense to me (if each record in TO1 has a yes/no field, why do we need a global calculation?). Could you perhaps explain what are you trying to achieve?

[Kris M]

What i am trying to to is take the entire record set from a table, filter it to contain only these records that have a certain value in one field and display it on a layout without using a filter. I will be accessing the layout via a script and i want it completely hidden from the user.

Kris

[comment]

Well, you could simply find the records that "have a certain value in one field" and show them in list view...

But if you want to show them in a portal, then create an unstored calculation field cMatch = and make your relationship:

TO1::cMatch = TO2::oneField

Alternatively, create a global field gMatch, type a certain value into it (or set it by script) and make your relationship:

TO1::gMatch = TO2::oneField

This is assuming oneField is indexed. Make sure your calculation/global field matches the type of oneField. Note that both methods will show the same records in the portal, regardless of which record is the currently viewed record.

[Kris M]

Not trying to do it via portal.. just whole layout.. how would i script the find?

Kris

[comment]

Roughly:

Go to Layout [any layout of TO1]

Enter Find mode

Set Field [ oneField ; ]

Perform Find

[Kris M]

No wonder why i could not figure it out on my own.. It was too easy..

Thanks comment

KM