Filtering by List field

[MikeKD]

HI Folks,

I'm filtering portals but am struggling when it come to filtering by a calculated list field.

My usual technique is to do by relationship, using great or equal to gSearchField, which usually works fine. EXCEPT when I use a calculated list field; which doesn't work using this technique with a relationship or with a portal sort.

I thought patterncount might be a snazzy option for portal filtering, and although it works nicely with standard text fields, it doesn't work with with this calculated list field either. I have tried removing the line breaks with substitute, but that didn't help.

I assume I missing something; any ideas please? 

I have a few ideas (e.g. scripting the calculation bit etc) , but I don't actually know why it's not working or what to do to fix it..

[comment]

I can't figure out what your question is. What is "a calculated list field"? And what exactly does "filtering by a calculated list field" mean?

You speak of filtering portals, but then you say "My usual technique is to do by relationship" which makes no sense to me. Most often you filter a portal in order to avoid adding a relationship.

Why don't you back up a bit and explain what you're trying to accomplish here, not how.

 

[MikeKD]

Oops, sorry for my confusion - thanks for replying so quickly.

I'm setting up a database of music resources: scores, parts, CDs etc.

Each score or CD etc (Resource) can contain more than one piece, each of which can be by more than one composer.

There's a join table between composers and pieces, and a join table between resources and pieces.

I'll be adding lots of stock to the resource table so want a way to quickly search for a piece to add to a resource.

I've set up a gSearchField which searches by relationship to Piece title, (that works fine) and would like to also be able to search for pieces by who composed them. The trouble is, that composers are in a separate table so I can't use them in a relationship with out a summary List field in the composers table being referenced by a calculation field in the pieces table. I think that filtering by relationship is probably the most sensible, because I think that there will ultimately be 1000s of pieces to choose from. Having said that - filtering by patterncount in the portal is very slick for the user..

I hope that helps a bit more - I think I'll need to attach the table (which may be useful for others anyway - the composers table has thousands of names on it now), but it will take a bit of doctoring to remove private tables.

Cheers!

Mike

[MikeKD]

Here's the database - it's very much work in progress I'm afraid.

The table names need work; some obvious choices were used elsewhere in the music department's database.

I've kept in all of the composers (for a couple of threads in this forum that might have been helpful) even though, there's still a bit of work to do in tidying it up.

Cheers :-)

 

Music Resource Catalogue.fmp12.zip

[MikeKD]

Ah, I think I've got it.

When I refreshed the window, I didn't tick the "flush the cache" tick box, so the portal was giving old data. That's got the portal filtering working - will check the relationship filtering in a sec..

Many thanks,

Mike

mmm, it still doesn't work with relationship filtering.

Still - using patterncount to filter the portal work really well - thanks for the idea comment (found on another thread!)