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.
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:
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.
How about adding a bit more complexity? If you create a custom function to handle the creation of the case statement you can have code complexity hidden behind the simplicity of the function call, see the mockup below.
FROM Table2 as T2
WHERE " & sql_compare("T2.FieldA" ; LocalFieldA ) & " AND "
WHERE " & sql_compare"(T2.FieldB" ; LocalFieldC ) & " AND "
WHERE " & sql_compare("T2.FieldC" ; LocalFieldD ) & " AND "
Thanks for your input.
I was thinking about a custom function, and you are correct that it would make the code easier on the eyes. I may go in this direction, and, if so, I will share it with the Forum. I am hopeful for other input and would welcome other things that could be addressed with such a function.
Why do you have empty fields for keys?
Gary, rather than "empty" keys, I believe the OP had three possible matches. Or rather, matches if any of three keys matched.
With SQL the OR can be used in the query.
In FM Relationships, there are multi-line keys (but I don't know if these were/are used).
-- sent from my iPhone4 --
Thanks for chiming in on this, I hope you are well.
Duh! Of course this is just the thing I was looking for.
WHERE ( T2.FieldA is NULL or T2.FieldA ='" & LocalFieldA & "'" ) & AND
( T2.FieldB is NULL or T2.FieldB ='" & LocalFieldB & "'" ) & AND
( T2.FieldC is NULL or T2.FieldC ='" & LocalFieldC & "'" );
I think that will work. And it is more readable and not overly complex. Great solution!
FileMaker does not recognize the empty string ['']
First this is not just a Filemaker thing its in the SQL and happens in non filemaker systems as well.
Second why do you have a NULL as a key this makes no sense unless this is a foreign key and no relationship exists for the record.
Third I agree your best option is a custom functions. This reduces the "complexity" the same way using a built in feature would. Do magic logic doesnt exist the only option is either Filemaker creates the "complex" function for you and hides it or you have to do it yourself.
Last I recommend you use the parameters as your SQL statement allows for injection and is a security risk. You could also do all of this in the SQL statement without the addtional filemaker logic. However this is still "complex" and may be even less readable depending on your knowledge of SQL.
Michael, I don't know what you consider a "match" any of the three or at least two of the three. See if this helps
ExecuteSQL ( SELECT T2.MyField FROM Table2 AS T2 WHERE T2.fieldA = ? OR T2.fieldB = ? OR T2.fieldC = ? "," ; "|" ; LocalFieldA ; LocalFieldB ; LocalFieldC ) You can combine AND with OR and put parenthesis where needed. WHERE ( x = 1 OR y = 2 ) AND z = 3 )
You can rewrite your "CASE" to be all in SQL-speak, but I think you want the OR instead of AND (or a combo)
FROM Table2 as T2
WHERE T2.FieldA ='" & LocalFieldA & "' AND
T2.FieldB ='" & LocalFieldB & "' AND
T2.FieldC ='" & LocalFieldC & "';
is NULL AND is NULL AND is NULL (a possibility) will net you records if all three are empty, if that ok?
Thanks again for your input. To clarify for the thread: what I am doing here is working with a non-FileMaker data structure overwhich I have no control. So the possiblitity of blank elements of the key is inherited, I am just working around what already exists. In most cases, I am working with 3 to 6 key elements, only one or two of which will ever be NULL, so there are always some values in the key.
I think I am going to work on this a bit, but I think I am much clearer now of my options.
Thanks to all for the help.