Null Relationship

[Bryan VonDeylen]

I have a FP12 database where I am using our SIS database (Infinite Campus) tables.

 

In FileMaker relationship, I want to find classroom rosters. The relationship I have setup between FileMaker and a roster table from IC is:

 

Globals <-> dbo.Roster

SectionID = sectionID

              and

Null = endDate

 

In Infinite Campus (IC), when a student drops a course, the endDate field has the date they dropped, otherwise the field is empty (null).

 

BUT, it appears that FileMaker will NOT match a "null" field. If the field in IC does not contain anything, it will not match a field in FileMaker that does not contain anything (is null).

 

Is this a bug, or just the way it is???

[Lee Smith]

Have you tried the isValid Function?

[Rick Whitelaw]

Not a bug. To be used as a match field it only makes sense that both fields contain data.

[Wim Decorte]

Not a bug. To be used as a match field it only makes sense that both fields contain data.

 

At the very least the field on the side/context where the relationship is being evaluated from.  The matched field can be empty and the relationship would still be valid.

[Bryan VonDeylen]

Lee, not sure what you mean. I cannot use a Function in a Relationship graph.

Rick, if it isn't a bug, than I guess it would be a feature request for a future version.

Wim, it doesn't. The relationship is not valid if nothing is in the sql table field (thus the thinking it was a bug). I am guessing it needs to be a feature in a future update.

 

I took care of the issue by designing a search instead of relying on a relationship.

[LaRetta]

Oh don't give up on this technique!  You can relate to empty child records if the data type is number or date because you can use a > from single unstored calculation string of 0 or from calculation Date ( 1 ; 1 ; 1 ) to the child (same data-type) field (please see attached).

 

I wasn't quite clear on your relationships but I hope this gives you an idea - it is very siimple.

NullRelation.zip

[David Jondreau]

Make a calc field on the child side = IsEmpty ( endDate ) and make a calc field on the parent side = 1 and match on those two fields instead of Null = endDate.

 

Globals <-> dbo.Roster

SectionID = sectionID

              and

Null = endDate

[LaRetta]

Hi David, Why add two new calcs when you only need one? :-)