Free-text portal filters with null records

[Rainy Pass]

I set up multiple filter fields to limit a set of records shown in a portal. The filters work fine unless a field contains records with no data. Any records will null data in a field are not displayed. How can I show all records including those with null data.

This is how it is set up...

global filter field:

_z__FilterStyle_gxt

Calc fields:

_c__FilterStyleStart_uct

_c__FilterStyleEnd_uct

Relationships

_c__FilterStyleStart_uct ≤ Style__lxt

_c__FilterStyleEnd_uct ≥ Style__lxt

Script on filter field triggered on modify:

Commit records/requests (no dialog)

Set Selection [_z__FilterStyle_gxt: Start Position: (Length( DIR_Import::_z__FilterStyle_gxt) +

1);

End Position: (Length( DIR_Import:_z__FilterStyle_gxt) +

1)]

This is repeated for multiple filters. Records will null data in any field with a filter applied, do not show.

Can this approach be modified to work? Is there a different approach I need to take.?

[jamesducker]

From a quick scan of the above I suspect your problem is simply that FileMaker relationships don't return records that match on null fields.

You could create a calc field on the foreign table, called "_c_NullRecord", which is defined as something like:

if ( isEmpty(field1) and isEmpty(field2) and isEmpty(field3) ; 1 )

Test the field first on the foreign, make sure "Do not evaluate if all referenced fields are empty" is switched OFF.

Then you could create another filter above your portal, maybe just a global check box field that returns "1", which the user ticks if you want to see null records. Relationship would be mainTable:_g_nullRecordFilter --> foreignTable::_c_NullRecord.

Depending on your setup you may need to tweak the relationship on the portal so that the checkbox field doesn't interfere with your normal search. May be easier to have a separate portal.

Hope this gets you started at least.

J

[Rainy Pass]

Thanks

[Rainy Pass]

Can't get this to work.

With or without the Null record filter added.

My current set up follows. (There are other similar filters in the relationship. All work fine with the exception of the Dimension filter; Dimension contains null data).

Any ideas? I would rather not have to populate the null records.

Primary Table

_c__FilterDimensionStart_uct Case (IsEmpty (_z__FilterDimension_gxt) ; "_" ; _z__FilterDimension_gxt )

_c__FilterDimensionEnd_uct _z__FilterDimension_gxt & "zzz"

_z__FilterDimension_gxt

----------------------------------------------

_z__FilterNullDimension_gxt

Secondary Table

Dimension__lxt

----------------------------------------------

_z__NullRecordDimension__lcn If(IsEmpty(Dimension__lxt);1;"")

Relationships

_c__FilterDimensionStart_uct =< Dimension__lxt

_c__FilterDimensionEnd_uct => Dimension__lxt

----------------------------------------------

_z__FilterNullDimension_gxt => _z__NullRecordDimension__lcn