Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Portal filtering through a join table


Eleanor
 Share

Recommended Posts

After looking around for awhile I wasn't able to find something similar to this, or I wasn't clever enough to realize what I was looking at.

 

I have a filemaker solution which is relating building keycodes to the rooms they open. This is done through a series of three tables:

 

Keycodes (table)

keycodeID

...

 

 

Code_Rooms (join table)

keycodeID

roomID

 

 

Rooms (table)

roomID

wing

room number

room letter

...

 

Currently I have a portal on a Keycodes layout correctly displaying all the rooms which the keycode gives a person access to. However, for some keycodes (master keys) this list can be long and I want to give the user a method of filtering. How do I allow a user to filter the portal by wing, room number, room letter or any combination of the three? (and ranges of room numbers, but I get ahead of myself...)

 

Any insights into this problem would be appreciated!

 

Using FM8 Pro

Link to comment
Share on other sites

Welcome Eleanor!

 

There are two things to think about here:

 

1. In order for you to filter records from the join table, the match fields must necessarily reside in the join table. This means you will need to have regular fields within the join table that have auto-entered calcs to pull in the corresponding values from the Room table. The difficulty then will be updating those join records should the parent Room data change.

 

2. Filtering the records will likely require calculated parent and child match keys that produce a match if the filter choice is empty. I offered a solution to this type of multiple filter scenario just a few hours ago:

 

http://fmforums.com/forum/showtopic.php?tid/179915/

Link to comment
Share on other sites

Perhaps I am not reading this correctly - it seems to me the portal is to the Rooms table, so the filtering globals can reside in the join table, and there would be no need to duplicate data.

Link to comment
Share on other sites

I am not aware of any - at least not in this situation: the globals are participating directly in the relationship, so any modification will cause a refresh of the join results.

Link to comment
Share on other sites

Thank you so much to both Ender and comment for repling so quickly, I'm sorry I couldn't reply sooner.

 

It makes sense to me to put the matchkey in the Codes_Rooms table. If I have the theory right:

 

Codes_Rooms gets a global which holds something like:

if (IsEmpty(UserFilter),"All", UserFilter) where UserFilter is a concatenation of fields which make up the users filter request.

 

Rooms would hold a carriage return separted list of all possible values to search for, including "All".

 

I would then relate these two fields, in addition to the roomID relation between Code_Rooms and Rooms and a filter should be born?

 

I know how to put the global in Codes_Rooms, but am unsure of how to set up the match keys in Rooms.

 

Thanks so much for your help thus far.

 

Another question, if I were to reverse this layout, Show keycodes in the portal which open the Room shown in the main record, would having this filter hurt my chances at creating that layout?

Link to comment
Share on other sites

Sorry Eleanor, it looks like you've misinterpreted something (or I've misinterpreted what you've interpreted?!)

 

Codes_Rooms gets a global which holds something like:

if (IsEmpty(UserFilter),"All", UserFilter) where UserFilter is a concatenation of fields which make up the users filter request.

Code_Rooms gets a global for each filter field, AND a corresponding calc (that's not a global).

 

While the calc could be what you have, I'd use "zzzAll" instead of "All", to prevent false-positives caused by the target field actually containing the text "All". This may or may not be a concern with your data.

 

Rooms would hold a carriage return separted list of all possible values to search for, including "All".
Not exactly. Rooms contains records, where the filter fields are calculated by the field's value & "¶zzzAll", like this:

 

cMatchNameFirst (calculation, text result) = Name First & "¶zzzAll"

 

This resulting values might look like:

 

Joe

zzzAll

 

Bob

zzzAll

 

Jane

zzzAll

 

allowing matches on either the field's entire value, or "zzzAll" in the case of an empty global in the Codes_Room table.

 

I would then relate these two fields, in addition to the roomID relation between Code_Rooms and Rooms and a filter should be born?

 

I know how to put the global in Codes_Rooms, but am unsure of how to set up the match keys in Rooms.

The relationship for the filtered portal would look like:

 

Codes_Rooms Rooms Filtered =

Codes_Rooms::RoomID = Rooms Filtered::RoomID

AND Codes_Rooms::cWing = Rooms Filtered::cWing

AND Codes_Rooms::cRoom# = Rooms Filtered::cRoom#

AND Codes_Rooms::cRoomLetter = Rooms Filtered::cRoomLetter

 

Another question, if I were to reverse this layout, Show keycodes in the portal which open the Room shown in the main record, would having this filter hurt my chances at creating that layout?
I'm not visualizing your question, but in general, use the primary TOs (table occurences) for the base of the layouts, and reserve the filtered TOs and relationships for special-purpose relationships and portals that must be filtered.
Link to comment
Share on other sites

Thanks Ender,

 

This filtering stuff is still confusing me (I think i'm too SQL headed). I haven't been able to try your suggestion but I'm starting to see the theory.

 

I'll let you know if I find success.smiley-smile

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use