5 Replies Latest reply on Oct 8, 2013 2:22 PM by JerryHall

    Relationship Issues



      Relationship Issues



           Ok, I would like to pile on here and see if what I'm trying to do is possible.


           I have 3 tables

           Table 1

           Table 2

           Table 3

           Table 1 is related by ID and then by a date to match with table 2

           Table 3 is related to table 2 by ID

           Currently there is a field in table 1 that calculates the number of hours from table 3 through table 2. This works great and it's calculating perfectly.

           However, I have an issue, there are some things being calculated that need to be filtered out. In Table 2 there is a field that is checked off if they shouldn't get credit for the item. If it's not checked, it should be null. I have added a field to Table 1, should null for each record. So the fields from Table 1 and table 2 should match when they are both null. But when I add the relationship criteria so that the relationship between table 1 and 2 now have ID, Date, Credit the Field in Table 1 that calculates no longer has any values. 

           I have checked the fields the records so that some are equal, some aren't but it doesn't seem to be calculating any of them. Any ideas for things I need to check?


        • 1. Re: Relationship Issues

                    Table 1 is related by ID and...

               Just so you know, that doesn't tell us as much as you might wish about your relationships. In WHICH table is the ID a unique identifier? Table 1 or Table 2? and the same goes with your description of the relationships between table 2 and 3.

               But we may not need to know. FileMaker cannot match empty fields in a relationship, there has to be data in both fields in order for them to match. So instead of a null value, perhaps the only problem is comming up with a value in the match fields that will serve your needs that is not null.

          • 2. Re: Relationship Issues

                 Null values (zero-length strings, empty fields, etc.) cannot match across a relationship. i.e. NULL = NULL won't be considered a match. You'll need to use a second, calculated field that returns an actual value.   e.g. with something like:

            if( isempty( FirstField ) ; 0 ; 1  ) )  

                 You'll need to make sure the field is always calculated (to save processor time, by default field calculations don't calculate if all referenced values are empty). 

                 Then 0 = 0 is a match, 1 = 1 is a match, 1 = 0 or 0 = 1 are not matches.

                 Hope this helps!



            • 3. Re: Relationship Issues

                   It's the NULL=NULL not being a match is issue then... I was wondering if that was the issue.

                   So basically, I would be better off setting the first field to the value I don't want to match, and then have it pull what isn't equal?

              • 4. Re: Relationship Issues

                     That's a method that I have been able to make work in my solutions. But keep in mind that we know next to nothing about yours.

                • 5. Re: Relationship Issues

                       Thank you! That was it... I'm updating the table now. All that's left is to make sure that all the other fields are updated properly.


                       THank you!!!