deleting records w/ auto-enter serial number

[Shana]

I am creating a database for a client where each record must have a unique ID number (serial number) that increases by 1. If a user makes a mistake and enters wrong information they need to have the option of deleting that particular record. However, the problem arises that let's say the user deleted record #5, and creates a new record, it is given the #6 but since #5 doesn't exist, i wold need the databsse to create the new record with an ID of #5 rather than #6. is this possible>

[BobWeaver]

If the user makes a mistake entering data, the record doesn't have to be deleted, just change the incorrect data. However, there are ways to create serialized records without the auto-enter serial number but they can get complicated and unreliable in a multi-user environment.

The simplest method is to use a script and a selfjoin relationship to set the field to Max(self::SerialNoField)+1

[Fenton]

Yes, this has been a controversial subject for a long time. Not only are such custom-built ID fields rather slow (Max(field) over the whole database takes quite a while on large files), but while it's calculating that number, someone else could ask for one, and, because the earlier request has not been committed to index, which does not happen until the calculation is finished and the record is exited, both could end up with the same number; disaster. It would rarely happen, but tests have shown that with a multi-user system eventually it will happen.

The only safe way to do this is to have a 1 record file whose function is to provide these serial numbers. When a new ID is requested, a script immediately enters a field, locking the record. A loop commences, checking for such a lock, entering when it can.

Here is an email list post by Ilyse Kazar, the queen of record locking :-/

>1. are you enclosing the incrementing routine in a loop that checks if the serial number record is locked before incrementing and moving out of the loop?

Yes, this kind of routine is enclosed inside a loop.

Example (in external file where "next serial" is stored):

Loop

Go To Field [NextSerial]

Exit Loop If [status(CurrentError) <> 301]

Pause/Resume [0:00:00]

End Loop

Set Field [gSerial in your main file, NextSerial]

Set Field [NextSerial, NextSerial + 1]

Notice that once the current user succeeds in getting into the NextSerial field (either right away or after trying several times), the record remains locked (because the cursor is still inside that field once the loop is exited) while the serial is being sent to the main file and while the NextSerial is being incremented.

>what happens to the lock status of the record after the first person moves on?

Their machine "closes" the record; your machine "opens" (locks) the record on the next pass through the loop.

>does the record remain locked because your cursor is in a field, or are you degraded to 'read only' and need to exit/re-enter to trigger the lock?

Your cursor does not get in a field while somebody else's cursor is in the field. So when the Exit Loop If condition is met, you are switched from read-only to read-write status.

[Shana]

i am very confused as i am new to file maker databases.

Where would I place this:

Max(self::SerialNoField)+1

and how would i implement the locking? All the users are technically guests on our filemaker server.

[BobWeaver]

Back to my original suggestion. Don't delete the record. Just change the information.

If you are new to Filemaker, then this is probably something you shouldn't be getting into, for all the reasons that Fenton gave you. It's opening up a big can of worms. And really, once the database starts to get big, who's going to be checking if there are a few serial numbers missing. Also, if someone deletes one of the earlier records, that serial number will be gone and can't be re-used.

[Shana]

well we have several databases that our company uses and we have problems where records constantly need to be deleted. i just generalized my first post so that i didn't hajve to get into specifics.

I have created several databases before but I have never tackled this particular problem.

The reason we need the serial numbers in order is that this is a database of purchase orders that our company uses (like checks) and every purchase order *must* be accounted for due to legal reasons.

[BobWeaver]

What did they do in the old days when they filled out the PO by hand and then made a mistake? Probably wrote "VOID" across it and went on to the next one. Generally, that's the same thing you do in a database in that situation. Rather than delete the record, have a Void field that you can set. Then the accountants can see what happened to the number.

If this is going to be a multi-user situation, consider what happens when User A creates a new PO #00501 and starts entering data. User B then creates a new PO #00502 and starts entering data. User A then makes a mistake and decides to delete PO #00501. Since PO #00502 already exists, there is a gap where #00501 used to be. What do you do now. If you use the Max() function given above it will give 00503 for the next number.

We had a discussion on this same topic a few months ago. Someone wanted to close up all the gaps in their serial numbers if a record ever got deleted. It makes for an auditing nightmare, because records that may have been the system for a long time with one serial number suddenly would have to change to a number that belonged to another record.

[Ugo DI LUCA]

Well Bob, I swear I'd like it to be like that, but the "Fiscal controllers" here may become suspicious if I had some missing serials, when delivering them my Invoices or Orders Folder.

I won't take the risk they jump on me because of these "VOID" things.

What I ended up doing is flag those Customer Orders or Invoices numbers that were currently empty, and drop them in a global field.

The "New record" script first checks for existing serials, then go to the related record, reset the "autoentered" creation date, and set the Customer_Id.

Because I don't want my users to mess-up with datas, they can't delete any record, nor use shortcuts to create records. If it happen that they accidentally create an order before the customer really explicitly tell he is a "buyer", they just "mark" this new record.

This happens all the day. Curious these vendors that think because the order is created, it will necessarilly be filled and signed

[BobWeaver]

Ugo, I'm not talking about having the purchase orders go missing. By voiding the PO, it remains in the database, and can be listed in reports if necessary. This how I have things set up with one of my clients' solutions, and have had no complaints from their auditors.

It's the same situation as when you have any manually filled out paper forms like cheques, etc. If you make a mistake, it's not likely that you will have replacement forms printed up with the missing numbers. You just mark it void and file it with the rest of the cancelled cheques.

But, you've made me curious now. I would like to hear what other developers do in this situation.

[LiveOak]

I'm with Bob. People make too big of a deal about the need to delete records. The "need" is mostly phychological. There is seldom much of an impact to the operational speed or storage requirements for a set of files. Just mark the records as "deleted" and hide them from the user. They are then available for auditing purposes. If you need more of an audit trail, make the user enter a reason for the "deletion".

-bd

[LaRetta]

I've worked between auditors, controllers and Administration for over 30 years. I understand both sides of this. A voided PO or Invoice is never a problem to an auditor but missing numbers certainly are.

There is another important consideration here ... Administration (the interaction between Client and Biller or Office Manager). It is not uncommon for a client to want to know why their merchandise for PO#___ wasn't delivered. Or the Biller will question why Invoice#___ wasn't paid (maybe they have a printed copy that appears in a file but it's not in the system anymore). They probably have no idea what happened to that PO or Invoice but they are the ones that have to unscramble the mess and put the pieces together. And if that PO or Invoice can't be accounted for it causes unnecessary stress between Client and Admin staff which is bad for business (it reflects incompetence and shakes customer faith), not to mention wastes staff time.

I know several businesses that delete voided items from their databases but keep a printed hardcopy in a file. That works, I guess ... but I've seen too many mis-filings to trust that. And digging through hardcopies is time-consuming (been there ... done it). A database (as we all know) will show a Client's record and easily identify cancelled Orders, POs and Invoices and takes up little Admin space. Store it in your database so all associated staff knows about it, or store it in hardcopy (in a black hole) and dig and pray; but either way, keep them.

I suggest requiring the void have the reason (it's okay Invoicer, to admit you messed up and re-created a new Invoice; or that Clients change their minds and cancel POs) and Auditors DO understand these things as long as they are accounted for. Also include the date and who voided it. Including a Preferences field to flag if Clients should receive Notifications of Work Order or PO cancellatons is also nice. No surprises for anyone!

I want to clarify ... Invoices should only be voided if they haven't been posted or given to a Client. Invoices that have been posted (or in any other way given out) should never be modified, deleted or even voided. They should be frozen and adjusted via a Credit Memo. Many businesses require that other documents be frozen at certain points ... you could inadvertently delete something you wish you hadn't. I'd rather keep everything and there's no better place than a database. That's why I'm here.

Anyway, that's just my experience with it.

LaRetta

[LiveOak]

All good advice, thanks LaRetta!

-bd

[LaRetta]

  Quote

If a user makes a mistake and enters wrong information they need to have the option of deleting that particular record ... and creates a new record.

Nope! Shouldn't delete it but you can re-use it. Users need the option to modify what they've entered, including removing all their data from the PO and starting again. If a record hasn't been committed, then the User authorized to create and modify that record can delete any text. Before a record is committed, it's under Admin control and up for modification - and thus blanking. This is not an issue. The issue is deleting a committed record (PO sent to a Supplier) or deleting a record whose serial number needs to be maintained for, as you say, legal purposes.

So you can have both as mentioned above, although a bit vague - don't delete, but blank. This will preserve your serial numbers but assist Users in their data-entry. Provide a CLEAR button (or even fib and call it DELETE). You would need checks in place to prevent accidental clearing - more than just the 'Are You Sure?' dialog.

Management needs to answer some serious questions about exactly when a record is committed and what the rules will be on allowing clears of a record or field. You need to determine your test critera to stop a User from blanking a record of another User, for instance. So you can assist them (via script) in going backwards through their data entry - thus blanking what they just entered one field at a time - or blanking the whole record.

Your tests might include: 1) How old is the record? 2) Was it created by the Current User or someone else? 3) Have there been any related records created (test by using IsValid)? 4) Have other Users modified a field (began using the record for their own purposes)? 5) If it has a Child db, it could allow children whose detail will not match it's parent (if you only blank Main and enter other information). So think through your rules and get Management's decisions in writing. Your CLEAR/DELETE script will depend upon this information. The buck's on them to tell you.

A nice way for Users would be to allow them to step backwards through their data entry sessions(thus blank what they entered) by the methods outlined in a demo by CobaltSky called Multiple Undo. So, don't delete the record but assist your Users in starting over on that record.

Cheers,

LaRetta

[Ugo DI LUCA]

Bob,

I wasn't suggesting that all non confirmed quotes or orders would be reused for new ones.

Quotes are highly used in my business to generate some stats about the products most required, and some forecasts too. It also is related to a Prospect File, and an order could follow even 2 years later.

Same is true for orders, which when cancelled, as LaRetta pointed out, should be flagged as such so that we know who, when and why this order was confirmed, or on hold.

However, an Order Number may be created but this order not populated. In such a case, when leaving the record, the user is asked to "save this number" for later use. I've also set a limit to 2 days, as dates are as reliable as numbers.

Now, this solution adressed a need for my first Business Solution, which has now been developed so that any quote or order is processed using some kind of Shopping Cart technique.

This later is very reliable, and really stops any accidental creation of a number. Items are picked from the Product Table through a portal, and dropped in a set of global fields (for quotes) or temporary file (for orders), waiting for being processed.

I've uploaded a sampler a while back in the Sample section, which was designed to cover the Multikey technique but was using this technique. It's called Multikey I think.

When the customer is OK with what's inside its pre-order, then an Order Number is created and a set of items populate the Line Item.

Quotes are processed through the same process using global fields this time, then scripted to the Line Item.

In both cases, no number would be created if no item is inside.

[Shana]

after reading all your responses I have decided to purpose the idea of locking the records so that they exist for auditing purposes however if they are void the user can enter a reason by click on a button. Thanks for all your help!