Multiple Filtered Relationships

[Simon UK]

Hi,

 

I have (at the moment) a very straightforward database.  Two tables, with one table containing data for 'Time' worked on various jobs by employees.  Also in this table is 'Department', 'Client Name' etc.  The other table is 'Jobs'.

 

I have created a third table called 'System' which has a single record with an ID of '1' which links to a Foreign Key in the Time TO which in turn links via a Foreign Key of 'Job Number' to the Job TO ... System>Time>Jobs.

 

I have a layout based upon 'System' which has a portal on it that is linked to the Job TO.  I then added global fields to the 'System' table and created multiple relationships between 'System' and 'Time' using equijoins and as I select combinations of 'Client Name' and 'Department' the portal is filtered.  However, as I am using Equijoins I cannot leave any of the globals empty and just filter for Time based upon a 'Department'.

 

So what I need is a multiple relationship that allows for any and all of the globals to be populated and return relevant result in the portal.

 

And that's where my Filemaker experience just about dries up!!

 

Any pointers would be gratefully received.

 

Thanks in advance.

 

Simon

[LaRetta]

Hi Simon,

 

There are a few approaches you can use:  1) Include 'All' as second line to Department and Client name, 2) Only use relational filter for criteria required and use portal filter to further 'constrain' the results only when value exists in your department or client globals, or 3) change your global fields to auto-enter calculated (replace).  For instance, Department global could be:

Case (
  IsEmpty ( Self ) ; ValueListItems ( Get ( FileName ) ; "Departments" ) ; Self
)

Be sure to uncheck in the bottom of the calculation dialog 'do not evaluate if all referenced fields are empty' and be sure to uncheck 'Do not replace existing value if any', back at the auto-enter tab right below 'calculation'.  In this way, you can attach your value list of Departments to your global departments field as checkbox.  The User can check any values they wish but if all are unchecked, it will fill in with all departments and will never allow the field to be empty.

 

I am not crazy about creating a SECOND field only to hold a multiline of List ( Department ; "All" ).  Allowing the global to pre-fill with the value list (if it is empty) is what I would normally use but that would depend upon the size of the value list as well.  It would work well for Departments but Clients might be really large (?) so you may wish to portal filter the Client.