The idea is to store the number 1 on each side, allowing me to view all items through a portal window.
It sounds like Filemaker is working correctly, just not as you expected it to. As a general rule, you should not use a field with global storage on the "child" or "many" side of a relationship.
If you have global fields on both sides of the relationship, you don't have any values on the portal's side that are specific to any one record. It's as though you have this relationship:
Table1::anyField X Table2::anyField
But it doesn't matter what value I enter into the global fields, or if there is one. The relationship is still valid and I can see all items in the portal
Hmm, isn't that what you just said that you wanted? Don't you want to see all records in the portal?
Perhaps you want to see all the records in Table2, but only if you enter a 1 in the global field defined in Table1?
If so, define a stored calcualtion field in Table2, cOne and enter: 1 as the sole term in its calculation. Link the global field in Table1 to this new calculation field and you will match to all records in Table2, if there is a 1 in the global field and to no records in Table2 if there is not.
I thought that a global field was equivalent to the same value across all records but it seems like you're saying this is not quite the case?
I could have sworn I've done this before but perhaps I haven't. I had one of those hair pulling moments there for a little bit.
I'm having another similar problem on the same layout I'm working on. I'm trying to view available units through a portal. A local calc field in the child side table gives 1 if a unit is available, 0 if not. This calc field is calculating based on criteria gathered through another relationship. The parent match field is still our abovementioned parent global "1" number field.
I can only get related records through this portal if I make the child match field a number field, it's not working when it's a calc field.
Close, but it's a bit more complex than that.
When it comes to data entry, global fields do act as though it is the same as having the same value in every field, but there's more to it than that. In a relationship, Filemaker matches records by using the match field's index. Global fields--as well as unstored and unindexed fields, do not have an index to use to match to specific records in the table.
I'm still not sure what you wanted to happen with this portal. If you were to put the same value in all records of table 2, wouldn't you expect to see all the records in the portal--which is exactly what happened here?
It is what I'm going for, but the bahavior was throwing me off. Additionally, I'm having the problem mentioned in my last post, which I noticed was related to the original issue.
Put simply, on this layout I'm trying to have two portals. One that will show all units and one for all available units. I'm using the global "1" field as the parent match field for both portals.
And what value in the 'units' table identifies it as available? There are several ways to set up either a relationship or portal filter so that only available units appear in the portal.
I'd consider using the X operator to match to all records in the units table.
I tend to use 1 and 0, 1 being available.
The issue here is that the child side calculated field that gives me my 1's and 0's isn't working for this relationship. If I change this field to a number field and manually test-enter values the portal works fine.
That would indicate that your calculation is an unstored calculation--probably due to referencing fields in a related table. If you recall my previous post, unstored calculations also lack the index needed for the "many" or "child" side of a relationship.
If you are using Filemaker 11 or 12, you can use a portal filter.
Stick with one version or another of an "all records" relationship for the portal, but use.
as the filter expression. (values of 1 will evaluate as "True" and values of 0 will evaluate as "False" so this will limit the portal records to just those that are available.
If you are using an older version of Filemaker, you can use a number field for an availability field and use it in the relatioship, if you can script methods that correctly update the status for a given unit. This might be a script that runs automatically once a day and/or scripts that are performed by script triggers when values that affect a unit's availability are edited.
Is there a compilation of the rules that relationships play by? There are so many instances where one thing works and another doesn't and the only difference is what the field type/setting is. It's been largely a trial and error learning process for me and it would be great to just have it available as a reference.
The "rules" aren't terribly complex and I don't know what current reference books and other training resources provide but here's my take on them:
- In almost all cases, fields on each side of a relationship should be of the same data type--either the defined type for a data field or the "return type" specified for a calculation field should match. Text should match to text, numbers to numbers, dates to dates, etc. There are cases where text and numbers or dates and numbers can be made to match records correctly, but generally, you can avoid a number of problems if you avoid that approach.
- Fields on the parent or "many" side of a relationship may be stored or unstored, global, indexed or not indexed. They are used as a "match value" against the index of the child side field to determine which records in the child table are related to a given record in the parent table.
- In almost all cases, fields on the child side of a relationship should have an index. Unstored calculations, fields with indexing turned off in field options and fields with global storage do not have the needed index.
- Text fields that store a list of values separated by returns function as an "OR" type relationship where any single value in the list can be used to match to values on the other side of the relationship. Such lists of values can be used on either or both sides of a relationship and can be produced with checkbox groups, executeSQL, or a list function calculation to name a few methods.
- Repeating fields function the same as Lists of values where each repetition functions as an "OR" type of match to values on the other side of the relationship.
- Relationships with the cartesian join operator match any value on the parent side to all records on the child side. For the purpose of displaying related data, it does not matter what fields are selected as the 'match' fields in this relationship. You can even delete one or both fields used as match fields from the tables and the relationship still functions. In some cases, such as a filtered portal that refers to a field in it's filter expression, using a specific field as a match field in this relationship results in automatic screen updates (when the value in the field is changed) without needing to resort to the Refresh Window [flush cached join results] script step--a script step that can result in very slow screen updates if used in some situations.
- Note that using a global field as the match field on the child side of a relationship produces results that are very similar to using a cartesion join operator.
- Relationships that use inequality operators such as <, and > will evaluate much more slowly than those that use =. In some cases, a list of values with the = operator can be used in place of the inequality operators to get faster updates on your layouts.
- While unstored calculation fields can be used as the match field on the parent side to display related data, performing a find with criteria in fields in the related table will trigger an error message. This is not the case when global fields are used.
Saving this text for future reference...
That's fantastic, thanks! #3 makes this whole scenario make a lot more sense, I didn't realize it was as simple as indexing nor not.
I'm curious, is there more than one person behind your username or are you always the same person?