Advancing letters when script triggered

[madman411]

I have a field that displays a "version" in my invoices. All new invoices start as version "A", however when a certain script is triggered I need the version field to advance to "B", then to "C" if it's triggered again, and so fourth. 

I thought if I could capture the current version as a variable and then set the Version field as 

$version + 1

would do the trick, alas I was incorrect.

What's the best way to script this? Additionally, should the Version hit letter "Z" I would like the alphabet to start over again as doubles, such as "AA", "AB", "AC", etc...

Thank you!

 

===

Update:

So some tinkering and I got this calculation to achieve what I want. Using Set Field:

Char ( Code ( $version ) +1 )

However, after "Z" I start getting other characters, like "[". Is there a way to prevent this like I mentioned in my previous post?

Edited January 13, 2016 by madman411

[webko]

You need to look at what the Code function actually does:

http://www.filemaker.com/help/11/fmp/html/func_ref3.33.46.html

So, A returns 65 and B returns 66 and Z returns 90

And AA returns 6500065, and AB returns 6600065 (note that Code returns the values right to left...)

It can be done with some Case statements, or a Custom Function... Personally, if there are 26+ iterations through an invoice, I'd probably just start again with a new invoice.

[comment]

5 hours ago, madman411 said:

should the Version hit letter "Z" I would like the alphabet to start over again as doubles, such as "AA", "AB", "AC", etc...

I would suggest you keep the version stored as a number and only display it as letters. Otherwise you will face an additional problem of translating the existing representation back to a number before raising it by 1.

To do the translation from a number to letters, you can use: http://www.briandunning.com/cf/399

Note:
There is something "wrong" about this request. If your invoice can have multiple versions, they should be individual records in a related table.

 

[madman411]

6 hours ago, comment said:

Note:
There is something "wrong" about this request. If your invoice can have multiple versions, they should be individual records in a related table.

 

Thanks comment.

This did cross my mind. Do you mean the entire order is "duplicated", child records and all (related 'line items' within the order)? Is there a link you can share that can explain a little more? I would've thought this would be a hefty script...

[comment]

1 hour ago, madman411 said:

Is there a link you can share that can explain a little more?

I think we would need a much more detailed explanation from you first (perhaps in a new thread).