Hello, I have one table with orders,invoices,warehouse documents, diffrence is in document type. I want to add automaticly unique number which start numbering from 1 in each month ie: 1/01/OR , 1/01/IN … There can work few users on the same table. How I can create this number. When I use select max(id)+1 sometimes it happend that it return the same number to few users. Also there will be document status (CLOSED, OPEN), how to disable possibility to edit documents with CLOSED status.
The first issue is a very common one. You need to have to see what works best for you. One option might be is to generate the number as late as possible, i.e. onbeforeinsert. That will not prevent your issue, but the chance will become smaller. The best solution is to work with autosequences but on mysql it needs to be the primary key which might not be conveniant. A more brute approach might be to trap the duplicate key error. If your month and number are primary key then you can only add one record. If you generate this number as late as possible then the chance of getting a duplicate error is small. But if it happens you trap the error, increase the number one more time and redo your save.
Latest issue: apply a where clause testing for the correct status. Then you will not have the closed documents on your screen. Create a read-only form for the closed documents.