Text operators/calculation?

Tap Forms – Organizer Database App for Mac, iPhone, and iPad › Forums › Using Tap Forms › Text operators/calculation?

Viewing 7 reply threads
  • Author
    Posts
  • July 24, 2017 at 2:39 PM #23770

    Marisa Ong
    Participant

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

    July 25, 2017 at 1:08 AM #23772

    Brendan
    Keymaster

    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.

    July 25, 2017 at 12:37 PM #23775

    Marisa Ong
    Participant

    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?

    July 25, 2017 at 3:56 PM #23777

    Brendan
    Keymaster

    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.

    July 26, 2017 at 2:51 PM #23779

    Marisa Ong
    Participant

    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)))

    July 26, 2017 at 7:49 PM #23781

    Brendan
    Keymaster

    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.

    July 27, 2017 at 9:19 PM #23788

    Mike Schwartz
    Participant

    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

    July 28, 2017 at 11:13 AM #23795

    Marisa Ong
    Participant

    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
Viewing 7 reply threads

You must be logged in to reply to this topic.