find text items in different possible configurations

[human]

I have a solution in which we can drag audio files on (we use the MBS plugin for drag/drop), and we parse out the data from the name. An example is:

Some Job JW "Some Title".wav

In the above example, the client = "Some Job", the composer = "JW", and the title = "Some title". Each of those is a field in the database "tracks". From that information we can look up all the other information we need about that particular track. So far so good. We can also have some tracks with multiple composers, separated by dashes in the name, so they could be JW-AB, JW-AB-CD, etc. Again it's no problem to search for that in the composer field. However, it gets tricky if the information in the database is entered differently than the name of the file, which can happen. in that case, the filename to be parsed might be CD-AB-JSW, but the composer was entered into the database as JW-AB-CD. I'm trying to figure out how to do a find that could find any combination of those. I think I can do it with a sql statement, eg:

SELECT client, composer, title from 'tracks' where client IS "Some Job" AND title IS "Some Title" AND composer LIKE "%JW%" AND composer LIKE "%AB%" AND composer LIKE "%CD%"

Is there a way to mimic that with Filemaker's find functionality?

[Søren Dyhr]

1 hour ago, human said:

Is there a way to mimic that with Filemaker's find functionality?

Yes and No, Quickfind in merge fields, outside the layout - but you would need to remove the needles from the real layout part. But unless some kind of normalization is performed of the data structure, will it give plenty of wrong answers, because quickfind doesn't provide any constrain feature!

--sd

[comment]

The default FM find (without any operators) will find records that contain words that start with the search phrase/s, in any order.

For example, searching for: ab cd ef will find any of these:

ab-cd-ef                
ab-ef-cd                
cd-ab-ef                
cd-ef-ab                
ef-ab-cd                
ef-cd-ab

For more options see:
https://help.claris.com/en/pro-help/content/finding-text.html
 

---
P.S. The "proper" solution for tracks with multiple composers is to have a join table between Tracks and Composers. In some cases you can get away with a multi-valued field instead, but you will face some limitations in reporting.

 

Edited April 26 by comment

[comment]

Consider also the option to standardize your data by sorting the substrings.

 

[human]

8 hours ago, comment said:

The default FM find (without any operators) will find records that contain words that start with the search phrase/s, in any order.

For example, searching for: ab cd ef will find any of these:

ab-cd-ef                
ab-ef-cd                
cd-ab-ef                
cd-ef-ab                
ef-ab-cd                
ef-cd-ab

 

I've been programming FM for a long time, but didn't know that. So much to learn! Thanks.

BTW We are interfacing with an external database so don't have much control over some things. When tracks are added to that database, the composer initials are parsed and entered into a join table, and entered in alphabetical order like AB-CD-EF. The problem is that the original file could have been named "Job CD-EF-AB title.wav". When that's dragged back onto Filemaker, the initials would have to be sorted back into alpha order to match, but the above solution is much easier. I just substitute a space for a dash and it works great. Thanks again.

[Søren Dyhr]

12 hours ago, human said:

Is there a way to mimic that with Filemaker's find functionality?

Meanwhile have I thought about how I would deal with such an issue, and have made a template showing a way to approach it, and admittedly, some measures have to made to make it scale better  ... so take this as initial thoughts:

possibleconfigurations.fmp12

[human]

4 hours ago, Søren Dyhr said:

Meanwhile have I thought about how I would deal with such an issue, and have made a template showing a way to approach it, and admittedly, some measures have to made to make it scale better  ... so take this as initial thoughts:

possibleconfigurations.fmp12 712 kB · 1 download

Not sure how to use this.

[Søren Dyhr]

1 hour ago, human said:

Not sure how to use this.

 

In  the fields above - start by pressing the buttons above clearing the field below, then type a letter in one ... and see the found set shown in the portal "narrows in", then put another letter into either the same or into the adjacent one. The trigging happens each time the cursor leaves the field by tabbing out of it. If you enter a combination that doesn't exist - will an error message be shown, which could be customized later ... if need be?

If you try, to enter "ja" in the leftmost of the three, and "b" in the middle, a short while after - the dwindling happens!

--sd 

film.mp4.zip

Edited April 26 by Søren Dyhr

[human]

Thanks, I'll try it again.

[Søren Dyhr]

On 4/27/2024 at 4:21 PM, human said:

Thanks, I'll try it again.

Provided it does in the vicinty of what you're after, would I be happy to tweak into in your direction ... if need be?

This template only shows 3 "where"'s and could easily be modified into a larger number of those. It operates only on right truncation at the moment, but could also be turned into looking inside into a each string....?

Others are also welcome to point at blatant things I might be ignoring here? 

--sd

Edited April 29 by Søren Dyhr

[human]

The simple solution was to substitute a space for dashes in the composer initials. That finds every combination of the initials, which solves my problem. But thanks!

[comment]

8 minutes ago, human said:

substitute a space for dashes

What difference does it make? A dash (or more precisely, a HYPHEN-MINUS character) is a word separator just as space is (at least as long as your composers initials do not contain digits).

However, you do need to use a space to separate the search phrases in the Find command.

 

 

[human]

1 hour ago, comment said:

What difference does it make? A dash (or more precisely, a HYPHEN-MINUS character) is a word separator just as space is (at least as long as your composers initials do not contain digits).

However, you do need to use a space to separate the search phrases in the Find command.

 

 

If I search for "AB-CD-EF" it only finds "AB-CD-EF" if it's present. But if I search for EF CD AB (in any combination) it finds any and all combinations of AB-CD-EF, CD-EF-AB, etc. Which answers my question. 

Edited April 29 by human

[comment]

Yes, that's exactly what I said:

1 hour ago, comment said:

you do need to use a space to separate the search phrases in the Find command.

But you said:

1 hour ago, human said:

substitute a space for dashes in the composer initials.

which I understood as replacing the separator in the data itself. And that's not necessary, as we now all agree.