Jump to content

Calculating Serial Numbers with text


This topic is 2272 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

 

 

Link to comment
Share on other sites

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 by comment
Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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 by comment
Link to comment
Share on other sites

This topic is 2272 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.