Incrementing Text Fields

[butchm]

Hi All,

When I add a number to a text field, only the number part is retained. Is there a way to make the result include the other characters?

For example, when I add XYZ100 + 1, I get 101, but I would like to get XYZ101.

Thanks in advance!

Butch M

[Chuck]

Are these serial numbers (primary keys)? If so, FileMaker has the built in capability of adding one to the highest used serial number for use in a new record. Define a field with Auto-Enter Serial Number and set the next value to XYZ101 and the increment by 1.

If not, and you really do need a calc that will add on to the number portion of a text field, you can try this:

Let(

  [

    Number = GetAsNumber( YourField ),

    NewNumber = Number + 1,

    TextPortion = Substitute( YourField, Number, "" )

  ],



  TextPortion & NewNumber

)

This assumes that the number portion is always at the end (although it will work if it's at the beginning as well). In other words, if the text is ABC101XYZ, this will not work to produce ABC102XYZ.

Chuck

[butchm]

Thanks for the reply, Chuck!

The serial numbers are not primary keys, so I wasn't able to use FileMaker Pro's built in functions. I am building a script that would let users add a range of serial numbers to an inventory database, so each item in the inventory would have a different inventory serial number.

I will try your suggestion (as soon as I figure it out) . Thanks again!

[butchm]

Hi Chuck! The solution you proposed works great! Thanks again!