So I’m trying to make it so when you start a new order and complete the window form , the user then clicks the finish button and it adds all the info and auto creates an invoice number that i need to start at 102000 and then + 1 on every order after that and save it in the program what number it left off at even if the program is close . not sure if i would have to query my database and get all the items in the id column to remember what number it left off at but i just don’t know how to get it to start at a number i pick. Can anyone help? Show me an example ? Visual learner.
I a thinking instead of jumping to making the product… Spend some time of a few sql tutorials w3schools which will help understand how a db can help your front end application.
You can have a database auto increment values sqlite auto increment for you in which you could use for an invoice number.
Let the database assign it. Make an autoincrement column (usually the primary key) and add the invoice without specifying a number. If you need to know what number was assigned, each database has a way to retrieve it.
And I agree with Rich, it is very important to learn the technologies you are using. Xojo can make it easy to use them, but you still have to know what you’re doing.
While I fully support the idea of getting some more education, I would not support the idea of using an auto generated ID for anything other than record retrieval - these things often come unstuck later and can be very messy to clean up.
My approach is to have a service module that generates all master keys be they a simple series or something more complex. That way you can change the logic or reset the series without having to restructure the database.
Any database that resets them on you should be shot behind the tool shed.
Also, Max() + 1 is only a good idea if you want collisions. What happens when you delete the newest record? Yep, next new record reuses the id. Or how about when two transactions run at just about the same time? Yep, two records with the same id. That one is of course only relevant on concurrent databases, or poorly threaded single user databases.
No, the database was designed to handle exactly this. Let it do the job correctly. Especially if you use PostgreSQL, as a sequence would be awesome for this.
Seriously, if you have a database resetting your auto increment values, that engine isn’t worth anything. Your data is only as good as your engine, so if you can’t trust your engine, what is it doing for you?
I had assumed that the order_no would be on a unique index, which means you don’t get duplicates,
and if you delete an order, then why would you not want the next one you create to reuse the id?
I would think you wouldn’t want order number ranges with holes in them.
i would agree that a database SHOULD not renumber your autoincrement columns, but, I was taught not to use them because you cant trust database engines.
the Internet is littered with (anecdotal i realise) forum posts where people relied on autoincrement columns and got into problems.
but hey, use them if you want!
invoice number should be a unique number and should never be reused. If it is reused, then you run into the chance of collision.
for example… lets say you create an order and send a product to the customer. As soon as the product was shipped, lets say the order was deleted in the system by mistake but the end user didn’t report the error. Now, another customer calls in and places an order and they get assigned the same order number that was previous deleted. their order was fulfilled and sent. a few days later, you get a call from the first customer asking to pull up their order because there was a few items missing. When the end user goes to pull up the order, they will pull up someone else’s order even though the invoice number matches.
I agree with Thom, when you are relying on a database to store your data, you should let some key functionality be performed by the db.
Holes in the records are not scary. Coming back to see an invoice you thought you had deleted, is scary. Scarier is deleting it again not realizing it’s actually a new invoice.
As for a unique index, is it better to fire an error, or simply do it right in the first place? It’s at least better than doing the wrong thing.
I would solve this problem using PostgreSQL as such:
[code]CREATE TABLE “invoices” (“id” SERIAL NOT NULL PRIMARY KEY, “invoice_number” SERIAL NOT NULL UNIQUE, CHECK (“invoice_number” >= 102000));
PERFORM setval(‘invoices_invoice_number_seq’, 102000, false);
INSERT INTO “invoices” (cols) VALUES (vals) RETURNING “invoice_number”;[/code]
And problem solved. Never need to think about it again. Also, I it typed on a phone, so hopefully I didn’t screw it up.
If I were using SQLite, I’d have to do more work at the application level, as it isn’t very good at this particular job. If I were using MySQL, I’d just jump off a bridge.
+1 for some SQL learning first. I learned from the Wise Owl Tutorials on YouTube and found them excellent - he even manages to slip a bit of (English) humour into an otherwise dry subject. I also learned a lot of VBA from their videos too.
well, that depends on your point of view. certainly in the UK auditors and accountants do not like missing numbers because people
tend to hide things they have done wrong in the hole where the data & documents were! In fact, where Invoices are concerned it is actually UK Tax Law that they must be unique and follow on from the last number. no gaps allowed.
That’s not surprising. Personally, I wouldn’t allow ever deleting an invoice, instead only allow its status to change. But the technical details are valid for any type of data.
Russ is correct. In the UK all invoices must be sequencial, however you should also never delete an invoice. Auditors will expect the invoice to be marked as cancelled but still be shown. You must also never reuse the number of a cancelled invoice.
in a database application, DB design is critical in planning before one line of code is written for your front end application. A poorly plan DB design will only create headaches down the road.
What is being discussed is the pitfalls of assigning invoice number one way verses another and how it could fail under certain conditions. For example, I agree that a row should never be deleted and columns should be used like “is_deleted” to help mark if a record should be removed from the user sight… but can be easily restored by an admin of the app. I still agree with Thom that incremental value should be assigned by db verse relying on a select statement. Thom brights up a really great point about simultaneous transactions in which cause would be a problem. Though one could argue what are the chances, I then say. if you are being audited, how well would this go over with the accountant if duplicate invoice number exist for different orders?
without having details on the application or current db design, something like could work.
table one (T_INVOICE_NUMBER) would only contain the invoice number assignment. In addition, you could add in aliasing invoice numbers column (in the case there are few systems that uses different numbers that need to be tracked).
table two (T_INVOICE) would contain the invoice top level information as well as provide linking to other tables such as customer foreign key, invoice number foreign key, addition top layer information.
Two three (T_INVOICE_ITEMS) would contain the items ordered and would link to the T_INVOICE foreign key.
having the actual invoice kept in a separate table will help with future options such as creating relationships between multiple
grr… looks like my text chopped off and cant edit.
having the invoice number kept in a separate table will help with future options such as creating relationships among invoices. for example, lets say the customer wants to keep track of all invoices for a project… you could store the relationships. or lets say an invoice needs to be amended after its been issued. You could create a new invoice which also reflects the old invoice number.
That is called poor application design. Never allow deleting of any data that must remain sequential or have an auditable history. Just add a status field to your table and mark that as deleted and have all applications filter by that field. Then auditing reports can still show that transaction as a voided request.
My day job is at a 3PL and we have delete buttons all over the place but not a single one actually deletes anything. Some discontinue products, others remove clients from active user lists and others void jobs, orders and invoices. If an auditor wants to see the history they can see it all.
[quote=167573:@Russ Lunn]i would agree that a database SHOULD not renumber your autoincrement columns, but, I was taught not to use them because you cant trust database engines.
the Internet is littered with (anecdotal i realise) forum posts where people relied on autoincrement columns and got into problems.
but hey, use them if you want![/quote]
Russ, you are confusing two similar but very different concepts, “unique row id” and “autoincrement key”. Every database has to provide a unique row identifier, even in the absence of a primary key, to allow for update and delete of a single, specific record. That value is subject to change over time as it is usually associated with the position of the record in the physical data file. That is the value that people get into trouble with if they use it as a foreign key or external identifier, such as invoice number.
Autoincrement, on the other hand, was specifically designed to solve the need for a permanent, non-changing identifier and resolve the issues inherent in the application trying to create one, with max()+1 for example. It puts the creation of the unique identifier at the database engine level, which is the only place in a multi-user system that can guarantee a unique value without collision. You should use an autoincrement value, and you should only use an autoincrement value, where you need a unique record identifier that will not change and will not collide.
Yet another reason to emphasize the need to learn the tool before you use it.
which DBMS’s absolutely guarantee that an AutoIncrement field will ALWAYS be sequential, and not have gaps in the sequence?
when I have looked around before, there are people on the internet saying they get gaps in the sequences
For invoice numbers you cannot have gaps in the sequence, so in that case, unless it is guaranteed to have no gaps, an auto-increment field is not a good type for an invoice number.
if the DBMS does guarantee that it generates sequences with no gaps then i would whole-heartedly agree that it makes sense to use it.
