Relationship Filtering Wirh SQL ESS Table

[JHaughey]

I have a FM GO layout with a portal. The portal show records from a a SQL ESS table (actually a SQL view) The relationship is built something like this:

 

uiProject  =  sqlProject

uiClass    =  sqlclass

uiType     =  sqltype

 

    The UI fields on the parent side are all global fields set by the users choices. All of this is fine. 

 

    The trouble comes when I want to add an additional relationship to serve as a filter.

 

  uiLine  = sqlLine

 

      The reason this is a problem, is that sometimes sqlLine is empty or null. So, while I can create the relationship and get the filter to work properly, I can never show all records. The usual way to do this in FileMaker would be to create a calculation field on the child side, something like calcSqlType = sqlType &  (return) & "ALL," then set uiLine to "ALL" in the appropriate circumstance. That won't work with ESS table because while you can create the calc field, it will always be unstored, and so not useful for a relationship.

 

   The other filters (uiClass and uiType) work because the child side fields they are related to are always populated and within a finite range. So, if the filter is clear and I want to show all records, I can set the parent side global to ""1" & (return) & "2" &" etc. That feels clunky, but it works.

 

   I hope my pre-coffee brain has explained this clearly. Any suggestions on how to do this are welcome and appreciated.