Try using a WHERE clause where you specify "Not Null" as the criteria.
When I'm trying to create a relationship based on a null value, what I do is create a calculation field: endDateX that is = endDate & "X". Then, I have a constant X field in my left table and match it with the endDateX field. The endDateX field will only be "X" with endDate is empty.
I'll be interested what other approaches people use.
Misunderstanding. I am trying to create a relationship between the fields in the Relationships of Define Fields. I cannot specify any SQL commands there.
I tried that, but apparently you cannot make a join between SQL Tables in FileMaker from a calculation field.
I created a Calculation (number) in the HouseholdLocation table = Case ( IsEmpty ( endDate ) ; 1 ; 0 )
When endDate is filled it results in 0; otherwise 1 (can search for null records so had to do the opposite). The calculation works. When endDate has data in it, the calculation displays a 0. Records with no endDate have a 1.
BUT, I cannot use that field to join. Just doesn't work at all (if it did, my problem would be solved).
and perhaps you should set up a View in MySQL to narrow your set of records (shown in FileMaker) to only include where ISNULL or NOT ISNULL (maybe two views?)
"Millions of records" is certainly a very good case for creating Views. FileMaker shouldn't have to load all the records/rows to narrow the set.
I'm wondering if it's an index issue. When you created the calculation field was it stored or unstored? Did you define it to have/create an index?
You could try a regular Number field (be sure that indexes are turned on) and give it the same calculation you did, but as an auto-enter calculation: Case ( IsEmpty ( endDate ) ; 1 ; 0 ). Set this up to replace contents (i.e. remove the checkbox for 'do not replace contents').
In the relationships graph any empty value in related field on the many side will cause the entire join to return nothing.
This is especially problematic for multi predicate relationships.
if their is a possibility for a related field to be empty its best to use a calculation that never leaves an empty value
It could be an index issue, but I cannot turn on indexing (it is grayed out).
So my calculation looks like this in FMPro
Unstored, = Case ( IsEmpty ( endDate ) ; 1 ; 0 )
Calculation result is "Number" (tried making it Text with no luck as well)
I tried unchecking "Do not evaluate if all referenced fields are empty" as well (didn't help)
You can't index an Unstored calculation - you have to deselect the 'do not store calculation...' checkbox. But that also means, then, that the calculation may not update as accurately as you like.
The other method, an auto-enter calc, you have to change the field type from 'calculation' to 'number'. This is not the same thing as setting the calculation result to 'number'. Once the field type is set to number, you can go to the Options for the field, on the 'Auto-Enter' tab, check 'Calculation', and then specify your formula. Then deselect the 'do not replace...' checkbox.
A stored calc or an auto-enter calc on a regular field will only update if the formula contains a reference to a field in the same table, and then only if that field changes.
Cannot create NUMBER fields in a table coming from a MS SQL database. Only option is creating Calculation fields and Summary fields. Eveything else is grayed out.
I cannot uncheck the Do not store calculation results. It is grayed out. It won't let me.
yes, that is your limited options. Can you get your IT (or you) to create Views?