using all/any within WHERE parameter

[eoneon123]

Probably a super easy question but I cannot figure this out for the life of me. The short of it is that I'm trying to capture all records when a field value equals anything. I've tried using ALL, Any, and even Like "[a-z]%". Nothing seems to work. 

 

The bigger picture is that I'm using several global fields to filter a portal by the value list selections within said global fields. Given the selected values, I've got a calc field that captures a list of serials which filters in the portal. The issue is that for each of the global fields, I'm also using a "Show All" value that that effectively removes the global as a list condition.

 

I'm not sure if thats clear but basically the set up is something like this:

 

Let([

 

$GlobalValue1= "x";

$GlobalValue2 = Case(g_field2 = "Show All"; ALL; "y");

$GlobalValue3 = "z"

 

];

 

SQL ("SELECT id FROM Table WHERE Field1 = ? and Field2 = ? and Field3 = ?"; ""; "";

  $GlobalValue1; $GlobalValue2; $GlobalValue3))

 

Any ideas?

[Wim Decorte]

Seems to me that if one of the selections is "show all",  you don't have to include the field in the query.  So you can make the string after the WHERE dynamic based on the selections

[eoneon123]

That's the approach I've taken in the past, but making the WHERE parameter dynamic based on the contents of four fields seemed a little less simple than just capturing four variables and then plugging them into a static SQL statement. Plus, I just like to know what the limits are. 

[bruceR]

I don't think it affects your results but it seems odd to name something as global and then not define it as global.

 

Like this statement:

 

Let([ $GlobalValue1= "x";

 

I would suggest either truly making it a global:

 

Let([ $$GlobalValue1= "x";

 

Or not calling it global:

 

Let([ $Value1= "x";