Letting the end user specify the starting invoice number.

[preston2]

I am finishing up a new version of one of my apps and I want to give the user the ability to specify the starting invoice number.

In the table I have a field "Invoice_ID" that is C,16,0 setup as Auto Increment and used for the primary index. No one really ever sees this number and it is only used within the database for index purposes and to tie everything together.

I Also have a field "Invoice_Number" in the table and is the one that they see and prints out on reports. It is also currently C,16,0 Auto Increment and I have the starting value set at "1001". I can change the field type or settings if needed as long as I do it before deployment of the app. I want the end user to be able to specify the starting number so if they want to start the invoice numbering at 20000 they can.

I was thinking I could setup a field in a "Settings" table where they could input the number they wished the next invoice to start counting from but my brain is going blank on the best way to get the number to auto increase up from there.

One of the main reasons for wanting to do this is I have several hundred customers of my app that want the new version and do not want to start the invoice numbering back with invoice numbers they have already used. The new app is so different from the other one that I cannot just put it out there as an upgrade but will be another stand alone app instead.

Any advice or suggestions would be appreciated.

Thanks in advance,
Preston.

[Stan Mathews]

Re: Letting the end user specify the starting invoice number.

For a field ID in a table atest, the following sets the default starting value to "9009".

tbl = table.open("atest")
rul = tbl.rules_get()
rul.change_begin()
rul.ID.Default.Default = "9009"
rul.change_end()
tbl.close()

[Stan Mathews]

Re: Letting the end user specify the starting invoice number.

It is also currently C,16,0 Auto Increment and I have the starting value set at "1001".

If I am not mistaken, "1001" increments to "9999" and then "0000", regardless of the field size.

[preston2]

Re: Letting the end user specify the starting invoice number.

For a field ID in a table atest, the following sets the default starting value to "9009".

tbl = table.open("atest")
rul = tbl.rules_get()
rul.change_begin()
rul.ID.Default.Default = "9009"
rul.change_end()
tbl.close()

That changes the starting value but if it has already started the count once the invoice form is first opened it has no affect on any future increments. This means the end user would have to specify the starting rul.ID.Default.Default before they ever opened the invoice form for the first time.

[preston2]

Re: Letting the end user specify the starting invoice number.

If I am not mistaken, "1001" increments to "9999" and then "0000", regardless of the field size.

Actually it goes to '9999000000000000' then '9999000000000001' (just tried it) so that is something I will also need to fix. I may be better off setting is as N,16,0 and then trying to figure out how to let them specify a starting number or jumping to a new higher number to use. Just need to also make sure the numbers stay unique.

[DaveM]

Re: Letting the end user specify the starting invoice number.

If you do numeric and the people don't see it, the start number does not matter and it is autoincrement which unique also not necessary.

The Invoice number is different since they see it. If it is numeric, they will just see it go up 1(if it is ai) and you or they(using stan's method) can specify a start point.

I have had a lot of clients tell me they wanted some wierd invoice number system, but I gave them a number and they wound up very hapy with it.

on the first one pad the number left to the end if you do character. as "0000000000001001" It just might work better.

[preston2]

Re: Letting the end user specify the starting invoice number.

If you do numeric and the people don't see it, the start number does not matter and it is autoincrement which unique also not necessary.

The Invoice number is different since they see it. If it is numeric, they will just see it go up 1(if it is ai) and you or they(using stan's method) can specify a start point.

I have had a lot of clients tell me they wanted some wierd invoice number system, but I gave them a number and they wound up very hapy with it.

Dave,

I have a "Invoice_ID" which the user never sees and does not care anything about. But the "Invoice_Number", they do see and do care about. I do not personally want to custom code a invoice number starting point for nearly 200 users of my application so I need to find a way that they can go into say a Settings table and specify what the next invoice number will be and then the application will AI from there.

I may look at just writing a script, have the form OnInit look at the value in the settings table, add 1 to it and write that number to the Invoice_Number Field. Then run a script OnSave that writes that new number to the settings table. If my thoughts are right and I allow them to open a small window to edit that number in the settings table then it may just work. Then again, my luck it will all blow up.

[DaveM]

Re: Letting the end user specify the starting invoice number.

I do similar by getting the max in the table and suggesting by ui_msg box the next numeber accordingly. Up to them what they do at that point. I think stan had it right for the first number/character. message box to ask for the first one??

[GGandhi]

Re: Letting the end user specify the starting invoice number.

hello

the auto increment field need not be used at all for the invoice number. that field is set for your table designs, set designs and to get the proper record.
i think the better way to handle the invoice number to start some random number
have a separate table say the company table that has all the control data
like
sales_tax_rate1
sales_tax_rate2
date_last_close
date_last_period_close
date_last_year_close and so on and on
and the
invoice_number.
then get the invoice number from the user to populate this table as the other fields.
then at the end entering the record one can lookup that invoice number and assign to the record to field called invoice_number which is not the auto increment field. and increase the invoice number in the control data.
i think this better than attacking the auto increment field.

[preston2]

Re: Letting the end user specify the starting invoice number.

Well I think I am on the right track.

I got something to work the way I want it to. Still need to do some cleanup before implementing it in my app and I still need to track down why I have to click the save twice to get the save button to clear. And yes I have tried topparent.commit

But, it works....

I will add it to where they can set a password in order to be able to set the next invoice number in the settings table. That way only the boss can set that and employees cannot mesh with it.

[Ray in Capetown]

Re: Letting the end user specify the starting invoice number.

I have to allow users to combine Alpha and Numerics in an invoice number.
All digits increment when their turn comes. A little dialog allows the user to re-set the starting number. Each new client wants to continue from their past system's last number.
Needs a table called SETSEQUENCE with at least a field with the last number used (linvnum).

Before beginning the postings for next invoice

tc=table.open("setsequence",FILE_RW_shared)
invnxt=increment_value(tc.linvnum)
newinvno = invnxt
[COLOR="#A9A9A9"]tc.change_begin()
'tc.linvnum= NEWINVNO 'after
tc.lockby=var->u_nam
tc.locknum=invnxt ' This must release to invoice next
tc.locktime=now()
tc.lockfor="Inv"
tc.change_end()[/COLOR]
tc.close()

And then after invoice succeeds.

ttc=table.open("setsequence",66)	'FILE_RW_shared)
ttC.fetch_first()
ttc.change_begin()
ttc.linvnum= NEWINVNO
[COLOR="#A9A9A9"]ttc.locknum="" ' This must release to invoice next
ttc.lockfor="Completed"
[/COLOR]ttc.change_end()
ttc.close()

You don't need the greyed out code - notifies all users when an invoice is busy updating (No one has ever seen that appear because the update is so quick) but when a failure occcurs, all users are prevented from proceeding to post invoices until a rollback or clear is performed. 100% integrity