Calculating Serial Numbers with text

[Neil Scrivener]

Hello,

So I'm a very much newbie, and perhaps staring to walk before I run - but I'm getting on OK with getting my head around things. Quite possibly the most complicated thing I'm adding to my database is a Serial Calc. 

The way it would ideally work is that each Client has a 3-figure Prefix unique to them. So ABC, XYZ and so forth. I have this as a Field. My next Field is a 'start from', which has Auto Data entered as 001, although it can be changed for the needs of that Client.  

Ideally, I am looking for a formula and/or calculation which does the following: 

1) Compares the ID of the Client against the 'start from' number 

2) Once compared, a calculated number to start the Invoice from

3) Continues to generate a +1 for every new invoice associated with that Client. So ABC001, ABC002.

I guess the Prefix isn't important, as the Prefix can be added to the Serial quite easily.

What I'm struggling to find is a way to get the comparison between the ID and the Serial Number to calculate what the new Invoice Serial Number would be. 

I looked at Summary Fields and then adding a 1 on to the existing number, but this only works when the invoices go up from 134 to 135 to 136 etc. Where as I am almost looking to compared ID+Serial then take away the ID and plus 1.  

I'm hoping that makes sense, any pointers and/or things to read up on?

Cheers

N

 

 

[comment]

Maintaining a separate series of serial numbers for each client is not easy to implement reliably. By "reliably" I mean without creating duplicates in a multi-user scenario. If you can accept gaps in the series, then work with a simple auto-entered serial number. Adding a client-specific prefix would be trivial using the Seriallncrement() function (in another field).

 

 

Edited April 3, 2016 by comment

[Neil Scrivener]

Thanks Comment.

I'm 50/50 on this. Part of me sees completely where you're coming from. But I'm also keen to keep my numbers as a Client may understand them.

So in terms of your idea above, that's pretty easy - in fact even easier, as I can just use the ID and add the Prefix to it. Then it's sort of unique, I can tell a customer by the prefix, and the letters at the end are simply random - which can be sorted/found if needed.

Turning to what you said about the unreliable idea (cringe). This is a single-user setup, so there wouldn't be two people making one invoice against a Client. With that in mind - have you and tips or things to read up to make it as per above, even if it's not reliable idea in a multi-user scenario?

The way I see it is if I can get something basic off the ground, as I do more reading and learning on FM, I can come back and adjust it.

Cheers

 

[comment]

1 hour ago, Neil Scrivener said:

This is a single-user setup, so there wouldn't be two people making one invoice against a Client

For now.

 

1 hour ago, Neil Scrivener said:

have you and tips or things to read up to make it as per above, even if it's not reliable idea in a multi-user scenario?

I'd rather not go there. If you really need this, do it correctly - meaning: script the creation of a new record and have your script open the parent Client record (i.e. lock it to other users), increment the NextInvoiceNum value by 1, and grab it to be used in the new invoice. And, most importantly: abort the script if the first step fails (i.e. record is locked by another user).

 

Edited April 3, 2016 by comment

[Neil Scrivener]

Hey Comment, thank you for this.

I hate to ask, but do you by chance know if there is a Demo script that shows me a similar script?

Sorry - I'm still getting my head around scripts, and would like to see one in action!