nuBuilder Forum

How would you solve the following.

The company uses Quotes and invoices. The quotes get a running number and can not skip a number. A customer requests a job done and so he gets a quote with a number (lets say 241-Q). Now he is not happy with the quote and negotiates and a new quote is made (lets say 242-Q) He now is happy and accepts the quote. So he gets and invoice the invoice is based on the quote 242 and subsequently has the invoice number 242-I. The receipt wiwll then be 242-R and the Delivery note 242-D

Now the challenge is how to create the number in the first place. If I use a mysql auto number field (the id field in the table) it will not work as the number only gets assigned at the point when the record is saved. Plus the number can only be used once.
If I use a normal field it does not autoincrement (as there can be only one column to autoincrement).
If I use select max(number) at number from Tbl_number then I get the last number. This could be incremented and presented to the user. The problem with this approach in a multiuser environment is that if a second user is faster he/she will get the same number and save it before the first user clicks submit.

Any insights how to do this with nuBuilder?
Your comments are appreciated.
Fundi

Fundi,

The quotes get a running number and can not skip a number

There is no reason why numbers cannot be skipped, sorry. (its not like you are cutting down trees)
I'm not getting involved to help a customer that demands something like this.
Its not really a nuBuilder question.

They could pay http://www.nusoftware.com for professional support, but usually when it comes to forking out money, people like this usually back done on their requests, because it turns out to be not that important.

Steven

Hi Stephen

I have grappled with this for some time now and I was indeed looking for a nuBuilder solution. I know some boffin could probably come up with something like special code but for me the beauty of nuBuilder is that you can do stuff without writing extensive code.

This is what I have come up with:
The user gets a one field (code field) lookup. When he/she clicks on it the user can either select a number (redo a quote) or create a new number by clinking add record, save and then select the new number. The new number generated in the add process is filled with a default value [select (max(number)+1)].

Agreed there is a chance that in the very second one user generates a new number another user generates a new number and thus create a duplicate. But I set the field not to allow duplicates. So one user will get an error (not nice I know) and as to do the add record. But the chance that in our small office with a few people that two people will do this at the same time is virtually impossible.

May this be of help to others and you see Steven after all it was a nuBuilder question.

Nice to see you solved it.