Seeking surefire way to prevent duplicate IDs

Panorama "Classic"
1

We have a shared database that is intended to have permanent, unique IDs, but duplicates keep slipping in. I’ve identified three ways this could be happening, and I’ve only found solutions for 1.5 of them.

  • Manually editing the ID field and typing in a pre-existing ID. This is prevented by the “no duplicates” setting in the design sheet for that field. (Resolved)
  • Creating a new record (by hitting “return” or the “Add New Record” or “Insert New Record” buttons in the sidebar). The “+” setting in the design sheet causes Panorama to automatically increment the ID number, but if the “next record ID#” is set wrong, this feature is worse than useless. The next record ID# sometimes becomes wrong, and since we don’t notice right away, I have no idea what circumstances lead to this. Once I notice it, it’s easy enough to reset it (but with many vexing inventory discrepancies to clean up each time). (Half resolved)
  • Duplicating an entire record and then selectively editing it, and forgetting to change the permanent ID. I don’t know how to safeguard against this. Pasterecord doesn’t seem to trigger .NewRecord, so I’m not sure what else to try. A warning would be enough to satisfy me, or making the permanent ID field active and clearing out the duplicate ID, or setting the ID based on the “next record ID#.” (Not resolved)

Any suggestions for robustly defending our database against duplicate IDs?

2

Perhaps if you set a procedure to duplicate the values of an entire record (variables to copy field values?) and incorporate into the procedure something like “Get text to set permanent ID” to force awareness of the need for a new permanent ID…

3

You seem to use a number field with automatic increment and seem to be struggling with manual entries and errors.

Maybe you should have a look in Panorama’s Programmer’s Reference and read about the uniqueid( function.

4

Thanks for the ideas. I think the issue is that we have a lot of pretty experienced (which is not to say infallible!) users who prefer to interact directly with the data sheet. I can’t say that I blame them–I often prefer it myself. Requiring them to use a macro to duplicate a record would be a tough sell. Or rather, people would probably be receptive, but would revert to duplicating records without the intended macro because it’s just not as convenient as clicking the button in the sidebar of the data sheet.

5

Why not use a .NewRecord procedure? It would run automatically any time a record is added. Within the procedure create a process to generate a unique ID.

You’ll find it in the PanX Help under “Implicitly Triggered Procedures”

6

I investigated that, but it doesn’t seem that .NewRecord is triggered by the pasterecord procedure.

7

Sorry, I failed to catch your reference to it in your original post.

As for using a macro, if you implement a keyboard command for a procedure that does a CopyRecord, PasteRecord and ID = a unique number, there’s no reason anyone should have resistance to using it. Even in the Datasheet, it would be far more convenient than the manual steps to do the copy and paste and would resolve the numbering concern.

8

Thanks–I know it just seems like I’m shooting down the ideas that everyone is giving to me, but while I’m sure my coworkers would try to remember to use the macro, we have dozens of Panorama databases and they all have slightly different keyboard shortcuts. Users who only interact with one or two databases often memorize all of the shortcuts relevant to them and use them daily. For those of us who manage and troubleshoot databases across the company (with divergent designs that people are often very attached to), remembering all of the keyboard shortcuts for each database is less realistic–and this cohort is the group most likely to be interacting with the data sheet directly. Unless there were a way to prevent copying/pasting a record except with the new macro, I think it would have a low adoption rate.

9

Once I took an first aid class that covered various serious situations - like arterial bleeding, poison, not breathing. The students started to propose scenarios to the teacher, “What if an artery was cut? What if there were signs of poison?” the teacher did his best to give some course of action. Then - just because they were students and not to be mean - they started doubling up, “What if the person isn’t breathing and there are signs of poison?” When three serious injuries were presented, “What do we do if …” The teacher sort of threw up his hands and said, “The guy dies.”

At one time, maybe still, there was a product called QuickKeys. It allowed you to assign various actions for short key combinations and I’m pretty sure those actions took priority over the standard actions - you could “remap” Cmd-C to do something besides Copy. And the modifications could be General or only apply to specific applications. I recall, when I’d support over the phone, having to explicitly tell the caller to “use the menu selection”. Because sometimes the user assigned a different action to the shortcut keys and forgotten about it.

It sounds like your situation gives the people many options to do the same thing. But in Panorama you can control the menus they see and the actions - no matter how they are labeled - behind the shortcuts.

It seems like .NewRecord and the unique ID function will handle most your situations. And if they are using Copy/Paste from a menu or via shortcut keys, you can “capture” that to. And you don’t have to worry about other apps because the modified action will only happen inside Panorama.

I feel your pain - I wrote something for student data entry and had to cover many bases of possible erroneous entry.

To the outsider it seems as simple as saying, “You’re all adults, there is a process we need to follow, please follow it.” But providing a tool that only lets them follow the desired process allows you to sleep better at night.

In your bullet points, you’ve pretty much done the first step of identifying the actions you need to control with “If this, then that actions”, But the problem with “Duplicating and entire record” is it is too general an action. How many ways can they do the duplicatingt?

If you gave them a “Duplicate record” button, menu option, and shortcut key. they couldn’t complain about extra key strokes. But you think they might work around that with the standard “Copy/Paste” actions under the Edit menu. You can control that too.

If fact, you could create an action for those shortcuts that reports who is not following the rules. But probably better to bow to human frailty and just have the Copy/Paste (assuming in this situation it is just used to add records) do a NewRecord action giving a uniqueID.

10

I would like to. But I don’t think I have the ability to edit the behavior of pasterecord.

11

But what is the actual action? When you say, “Paste Record”, is that an action with the buttons on the data sheet, a cmd-key shortcut, a menu choice? What keys/clicks are actually happening? If CopyRecord/PasteRecord are choices under the edit menu, though it would be work and a last resort sort of thing, you could have a custom edit menu.

If they are doing that with shortcut keys - less labor than taking their hand off the keyboard, grabbing the mouse, moving and clicking, moving hands back to the keyboard, you can override those command keys - at least you could in the past.

The problem almost dips into philosophy. You want them to have complete freedom with the menus/keys but you want to restrict their actions. You can restrict their actions by removing choices or they can change their behavior by having the undesirable actions have … consequences.

I haven’t worked with Menu Bars for a long time so I don’t have specifics. But I’ve worked with Pan files that had completely custom menu bars that only allowed certain actions.

It could be that I’m referring to forms and if you allow the user access to the data sheet view, then you are giving them the keys to the kingdom. I do know that you have a lot of power over the menus, their choices, and their cmd-key shortcuts. I don’t know if that power extends to data sheet view.

But giving them a legitimate Procedure Action that copies the current record and makes a new one - but with a unique ID is possible. If you give them that action and they don’t follow your request to use it … that sounds like … something else.

12

I’m talking about the sidebar in the data sheet. There are buttons for “Copy Record” and “Paste Record.” Preventing people from accessing the data sheet is not an option for us.

13

It’s this button here.

Screen Shot 2020-08-26 at 7.33.21 PM
Screen Shot 2020-08-26 at 7.33.21 PM2049×343 37.2 KB

14

Well that’s a tough one. If the DataSheet view is sort of a SupremeManagement view and you have to give them access to that and it bypasses all “captures”, what can you do!

In days of old, we used to have two windows open - the data sheet and a form, side by side. The Data sheet was sized so you could see the fields necessary to scroll/select what you wanted and the chosen record would display on the form so specific fields could be edited.

Habits are hard to break but you from could have a BIG paste record button on it that would copy the content of the current record, paste a new one in with unique ID, and allow editing of that new record.

If you have added some tracking field so you know who added what records you could check the database at the end of the day and see who is not using the button on the form and maybe have a discussion with them about the problem it causes.

I think a long, long time ago there was a problem with an apple in a garden and people were told not it eat it - they were given plenty of alternatives. If I recall, that didn’t turn out too well either.

15

Once the record is copied and pasted I assume there will immediately be an edit made to one of the fields to differentiate the copied record from the original. If you have a .ModifyRecord procedure it will be executed when that happens and that procedure can check to see if the ID number is unique and then reset it if necessary.

16

I think that just might be the solution! Thanks!