Jump to content

Calculation - Relational problem

Kris M

This topic is 6448 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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?



Link to comment
Share on other sites

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??



Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.



Link to comment
Share on other sites

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.


Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 6448 days old. Please don't post here. Open a new topic instead.

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
  • Create New...

Important Information

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