AnsweredAssumed Answered

ExecuteSQL function and is Null vs ''

Question asked by MjtBiz on Oct 11, 2012
Latest reply on Oct 12, 2012 by MjtBiz

One of the things I was looking forward to with the ExecuteSQL function was getting around the issue with empty values in compound keys. In a relationship, if I have three values that establish my key and one of them might be what Filemaker considers empty, the relationship will not find a match for those records even if the match field on the other side of the relationship is also empty. There are tricks around this, but they just add more complexity to the solution.

 

So with ExecuteSQL, I figured that I could not only cut down on many of those relationships on the graph, but also deal with the empty values issue. I write my select statement:

 

ExecuteSQL(

"SELECT T2.MyField

FROM Table2 as T2

WHERE T2.FieldA ='" & LocalFieldA & "' AND

T2.FieldB ='" & LocalFieldB & "' AND

T2.FieldC ='" & LocalFieldC & "';

",";

"|"

)

 

The problem is that when LocalFieldA, B or C is empty, FileMaker does not recognize the empty string [''], so the select statement fails on the match. It does, however, recognize the NULL value, so I can wrap every condition with a Case/If statement:

 

ExecuteSQL(

"SELECT T2.MyField

FROM Table2 as T2

WHERE " & Case( isempty( LocalFieldA ), "T2.FieldA is NULL"; "T2.FieldA ='" & LocalFieldA & "'" ) & " AND "

& Case( isempty( LocalFieldB ), "T2.FieldB is NULL"; "T2.FieldB ='" & LocalFieldB & "'" ) & " AND "
& Case( isempty( LocalFieldC ), "T2.FieldC is NULL"; "T2.FieldC ='" & LocalFieldC & "'" ) & " AND "

",";

"|"

)


But now I am back to more complexity and less readable code.

 

I hope I am missing something here and that one of you geniuses has a better workaround.

 

Thanks,

 

Michael Thompson

Outcomes