Text operators/calculation?

[Marisa Ong]

Is there any support for text operators or calculations beyond Concatenate? I’m looking for stuff like Length, Left, Right, Replace, etc.

[Brendan]

Hi Marisa,

I haven’t written any of those kinds of functions yet, but it’s certainly something I would consider if the demand was there. I have a few new functions coming in the next update. More to do with dates and times though.

[Marisa Ong]

Thanks for the quick response! I was interested in text operators because I’d like to use Tap Forms for my book collection. But I’d like to alphabetize my books after stripping out “A,” “An” and “The” from the book title. Can you think of any way to accomplish this?

[Brendan]

Hi Marisa,

The only way I can think of doing that would be to have the names of the books in this format: “Hunger Games, The”. I know it’s not a nice way of displaying them though. I’m trying to think of a way that a REPLACE(X,Y) command could be useful in this case. I could see it being useful for one value. E.g. REPLACE(“The “, “”) which would scan the value for “The ” and replace it with the empty string, but that would work for only one value. You want it to replace multiple potential values at once. Something like REPLACE(“The ” or “A ” or “An “, “”). And also it should not replace anything if it’s not the first word in the list of words. So lots to think about to do it right and make it easy to use.

[Marisa Ong]

Thanks for thinking about this! Doesn’t sound like there’s a lot of demand for text operators, but I’d certainly welcome them :-)

This is the calculation that I’m using in my ancient FileMaker Pro database for an “alphabetized” version of the book title:

If(Left(Title;4)=”The “;Replace(Title;1;4;””);If(Left(Title;3) = “An “; Replace(Title;1;3;””); If(Left(Title;2) = “A “; Replace(Title;1;2;””);Title)))

[Brendan]

Thanks for the function. That’s interesting. Right now the math parser I use doesn’t allow for strings to be used in the comparison part of a function. That’s something I’d have to work on at some point. I was just investigating this right now actually and it’s not super straight forward in the code I’m using to make that kind of a change. I’m working on some other code parts now, but if I get some spare time outside of that I’ll see if I can make a change that would allow string comparisons.

[Mike Schwartz]

Marisa,

Here’s a messy workaround for you to consider. It might worthwhile if you are starting out with a large list of existing book titles in your FM Pro database, and perhaps only make infrequent additions to your library.

Export the clean (original) book titles to Excel, along with your “alphabetized” versions that have “a/an/the” stripped out. Sort them in Excel by the alphabetized titles. Next, use another column to add sequence numbers, maybe by tens (10, 20, 30, etc.).

Import the clean titles along with the sequence numbers into your new Tap Forms database. You can now sort by your sequence numbers field to achieve your desired sort order.

Adding records: When you add a new title to your database, figure out where it belongs alphabetically. Let’s say it falls in between your books with sequence numbers 1260 and 1270. Then manually assign sequence number 1265 for the new title, and it’ll fall into place when you re-sort.

Hope that helps,
Mike

[Marisa Ong]

Thanks, Brendan, for the tip! I have almost 2,000 books in my library, but of course all of my current collection already has an “Alpha Title” field. I think my most straightforward workaround is to just keep that “Alpha Title” field and hand-enter text in that field for any new books. That way I can display books with the correct title but alphabetize after stripping out the unnecessary articles. I think that’ll work :-)

[Author]

Posts