Deleting Shared Records

Panorama X Beta 10.2
1

Is there a way for a procedure to delete a quantity of records in a shared database other than looping through them for a one by one delete?

I have a large, shared database that needs to archive a few thousand records every month, then remove them from the active file. It can be converted to single user, delete the records all at once and then be reshared, but this is a lot for the office staff to handle. As far as I know, there’s no way to programmatically accomplish that.

I did come up with a procedure to handle it while the file is shared and loop through all the deletions during off-hours, but it always errors out after deleting a few hundred. The error is that the server is no longer available, although it is, and the file claims to be connected.

2

This capability was added to Panorama X a year or so ago.

I would recommend this approach for an application like this. There’s a reason Panorama doesn’t make it easy to delete large numbers of records from a shared database. In order to enable synchronization, Panorama has to keep a record of every record that has ever been deleted. To do that, it keeps a list of all deleted records. In your application, this list will soon contain tens of thousands of items, maybe over a hundred thousand items in a year or so. The server will have to go thru this list every time a client syncrhronizes, so synchronization will gradually get slower and slower.

When you do a new generation that includes the data component, this isn’t necessary. When a new generation happens, the list of deleted records (if any) is cleared out. This is possible because in the first synchronization after a new sharing generation, all clients will re-download the entire data set, not just updated records. If you need a further refresher, I did cover how deletion of shared records work in the Panorama Server Deep Dive videos, I think in Part 3 but possibly Part 2. I don’t think I covered automating the process however, because I don’t think that was available yet. But you should have no trouble with the documentation page I linked to above.

So my recommendation would be to create a monthly Archive procedure that starts a new generation, archives and deletes the old records, then uploads the new generation. All of this can proceed automatically, however, the other users will need to disconnect from the database while this is happening (if they don’t, the procedure will stop with an error, then re-run after making sure everyone else has closed and/or disconnected from the database). But the entire process of new generation/archive/delete can be fully automated so that one click initiates the entire process.

3

'Tis amazing how every solution to a problem creates a new problem. lol.

Now it will be on you to get everyone to remember to close out of Panorama. Experience says that this may be challenging.

4

Another issue is the assumption that everyone is a good player. It is not unheard of that someone may “leave” a company (i.e. be escorted out) and if he/she gets wind of that pending event, they might “do something” before they leave.

In the olden days, the word was, you never wanted to be invited to lunch by your boss. Because while you were out of the office … . That happened to me in '70 at Crown Zellerbach. I was invited to lunch and my desk was locked up when I returned. I told the supervisor I had been working on a common report (COBOL - punched cards) and it would be wise for him to allow me to put the card stack back together before I left, which I did; because I’m usually a good guy.

But a definite part of the design of my tutor hours tracking program for a community college was making sure a student, doing data entry with the ability to edit records, couldn’t also delete - accidentally or on purpose - a whole bunch-o-records. You don’t want that action to be easy in a shared environment.

1 Like
5

Shared databases in Panorama 6 worked the same way, but there was no way to automate it.

Usually? C’mon Paul, I think you are always a good guy :100:

6

Thanks Jim. This certainly looks like the solution we needed.

We don’t have need to worry about “bad” actors because the process starts with asking for a password that is only available to a select few. The next step is to save a backup copy of the entire file.

It includes a check for connected users. Sessions can be terminated, (manually only, as far as I know) so we can get by that obstacle as well.

And it will surely beat the speed of loops to delete.

7

The good news is that it works, but with a few caveats…

After selecting the desired records and archiving them, this is the part of the procedure that deletes records and updates the server

startnewdatabasegeneration initializedictionary("COMPONENTS","data","NOTES","Monthly Archive")
SelectReverse
RemoveUnselected
uploadnewdatabasegeneration ""

It’s easy enough to do my own check ahead of time, but if others are connected, it fails silently. There is no error reported. I’d love to be able to procedurally terminate sessions.

On any error, the display at the top of the Datasheet gets stuck with the Uploading to Server message and the database becomes Read Only. Selecting, closing and re-opening, or other actions don’t always get it to refresh but its popup menus continue to work and perform actions…
Pasted Graphic

Manually running a New Generation for Procedures did cause it to refresh and return to read/write. I am able to correct it with Connect to Server when that choice is available.

If using startnewdatabasegeneration solely for procedures, the progress bar comes up and at the end of the process I get notified that it was completed. However, the bottom of the procedure window consistently shows an error: “New Generation has not been initiated.”

Once, the above process ran fully but for unknown reasons, it ended with an error: “**Could not upload new generation of SalesRecords on Mac Server
listfiles( function error, folder “/Users/panorama2023/Library/Application Support/PanoramaX/Server/Public Databases/F70C7356-7B7C-4C2B-AA52-9E3126C1A243-212” does not exist.

The next time it ran flawlessly. Then again and again.

So now I think I know about all the preparatory steps I need to take in order to avoid the errors and it appears to work as desired.

Related: In the Active Session Panel of the Server Administration, the Last Activity seems to be static and never moves beyond the Logon Time and Date.

8

Huh. I didn’t think it would fail silently. I’ll have to look into it.

This is of course technically possible, but I’m very reluctant to implement this for hopefully obvious reasons. (If it’s not obvious, it is because users tend to get cranky when someone else terminates their session.)

9

And when those users know who has that power, they do tend to speak up if appropriate. But If I’ve told everyone to log out at the end of Friday’s work, I expect them to do it. I warned them. If they’ve failed to quit, why should I have to work harder spending my time checking to see if they did it and thend doing a manual booting of them?

10

Currently you can go to the Server Administration window and boot them one at a time - you don’t have to go to each computer. I’m just not sure it’s a good idea to make it so that one button could boot everyone on the network with no review.

11

What Robert said.

I wouldn’t see it as a routine to be performed in the middle of a busy work day. And I’m very confident that in most office environments, whoever wields the boot power would use it with some awareness. But I’ve seen that far too often, someone leaves their files open when they head home at the end of the day.

Rather than boot all users at once though, a command to drop a single user could be implemented. It would require the Panorama programmer to go through users on by one in some manner. That’s what we do now through the Admin interface. But brings it out of the weeds to present the opportunity where it’s appropriate.

12

To be clear, this occurred for me when I had it set to “data”.

13

I chose to quote myself on this in order to add to it. I needed to boot a user in order to update a field structure. It showed when they logged on - yesterday - but suggests they never did a thing. The second session is me and it’s going on to being live for 24 hours with a lot of activity yesterday and today.

Pasted Graphic

If this truly displayed the last activity, I could confidently end idle sessions. In this case, the first has actually been logged off but never got updated to show the fact.

Meanwhile, I’m working on ideas of how to use a server variable and a timer in order to push a message to all users when I need them offline.

14

Oh my, you’re quite right. My initial investigation suggests that the server isn’t even keeping track of the last activity time for each session. I think I must have used the logon time as a placeholder and then never realized that I hadn’t circled back. I’ve added this to the issue tracker, but I think this might take quite a bit of code to implement, so it might not be fixed right away. I agree that it should be fixed however.

I cannot duplicate this. When a user closes all of the server databases it has open, the session list immediately updates to show this.

This is a technique called “polling”. I’ve considered implementing a messaging system this way, and may do so at some point. The problem is it doesn’t scale very well. If there are more than a handful of connected users then the continuous polling is going to cause a significant load on the server, perhaps enough to affect performance. The advantage is that it is much simpler to implement than any kind of push notification system.

15

I seem to have it working as desired. Every five minutes the timer checks the contents of a server variable and if it has changed, it pops up a message using the variable’s contents.

For a shared system of less than 10 users it seems to be workable. I can always increase the time between checks. It will allow a notice to be shared with all users with the knowledge that it will be X minutes before they see it.

Now if I can just have a button to nuke all open sessions… :smirk:

16

Ok, sure, at every five minutes you’re not going to have any performance problems. I was imagining more like every few seconds.

17

For anyone interested, here’s my solution for polling - or popping up a message on connected client computers.

Every client has a file with the following in the Initialize procedure. 300 seconds has it checking every 5 minutes. I’m using the global to ensure that the message pops up once unless its content gets changed.

    DefineServerVariable dbname(),svNotify,""
    LetGlobal gvNotify = ""
    If info("timers") notcontains "Notification"
        starttimer "Notification","interval",300,"scope","global", "code",||| Let lvNotify = servervariable("MiniServer",{svNotify})
        If lvNotify ≠ "" and gvNotify ≠ lvNotify
        	gvNotify = lvNotify
        If gvNotify ≠ ""
        	Message gvNotify
        EndIf
        EndIf |||
    EndIf

To post a message, I’ve come up with this.

Let lvNotify = servervariable("MiniServer",{svNotify})
GetTextDialog lvNotify,"Prompt","Enter the message to be presented to connected users.","Height",54,"Button","Ok","AbortButton","Cancel"
If info("dialogtrigger") = "Cancel" 
    Rtn
Endif
LetServerVariable svNotify = lvNotify
Rtn
18

Just a reminder that in Server Administartion, tthe static display for Last Activity continues:

PastedGraphic-1