Restore an Indivdual from a backup or copy from another DB?

[pbumc]

Hi,

We're using PCPlus 15. A volunteer accidentally deleted an individual from a family, not realizing this wiped out not only her donation history but also 2+ years of attendance history. Unfortunately, too much time has passed to restore the entire database.

I assume I can restore a backup to a temporary location to review old data, the manually re-add to the live database. But I wondered if there is a procedure for copy one individual (with contribution and attendance history) from one database to another? I'm not averse to using SQL (INSERT INTO / SELECT FROM), but then I'd need to know all the tables involved and how to identify the correct records.

Thanks for your thoughts,

[Zorak]

Step one of this recovery process should really be to review which users have vs. need Delete permissions in the software. Speaking probably a little too frankly, PowerChurch is very explicit in its messaging when deleting someone, showing the dates they last contributed and attended, as well as what is going to happen to that permanently deleted data. It takes a lot of effort to accidentally do this.

OK, all of that aside - the delete process opens and updates the following tables:

• ATDATA
• BGCHECK
• CKHISTORY
• CKLINK
• CO
• COAUTO
• COPLED
• CORECEIPTD
• COSCAN
• EVDEFTASK
• EVTASKLINK
• EVOCC
• ME
• MECONTLOGD
• MI
• PCUSERLINK
• PREMPL
• SK
• SKSERV

And then deletes the Personal Profile picture if it exists in data/pictures.

If it was a whole family that was deleted, then it also updates the following tables:

• MA
• VI

And then deletes the Family picture if it exists in data/pictures.

The link referenced in the family tables is MA.MAIL_NO. For personal profiles, it is ME.MAIL_NO and ME.PERS_NO.

Contributions (CO* tables) are linked by envelope number (ME.ENV_NO).

PowerChurch moves the contributions for deleted profiles to envelope number 99999 and updates a reference to old_env_no in the CO table, so with that table in particular, you won't want to insert new records from the old backup CO. That will need to be queried directly, updating the envelope number, replacing with old envelope number.

[pbumc]

Zorak,

Thanks for your prompt and detailed reply. Point taken about Delete privileges. And you're right, "accidental" was the wrong word.

The issue was that a member contacted our financial secretary asking for a contribution statement separate from her spouse. I thought the finsec knew about just assigning a different envelope number--we have several other households who do that--but he thought he had to delete the member from the family and re-add her as a new family, then re-enter her contributions for this year. Attendance updates are done by someone else (the office admin) so the finsec he didn't understand the ramifications of the delete.

Re. permissions, which permission is required to merge duplicate profiles, or to move a Personal Profile under a different family? Just "Add/Change"? That is something that the office admin needs to do sometimes.

It's tempting to try to write a script to do the copy (or use it as an AI programming experiment), but it may be faster to just re-do the data entry.

Regards,

[NeilZ]

<snip>

Re. permissions, which permission is required to merge duplicate profiles, or to move a Personal Profile under a different family? Just "Add/Change"? That is something that the office admin needs to do sometimes.

It's tempting to try to write a script to do the copy (or use it as an AI programming experiment), but it may be faster to just re-do the data entry.

Regards,

Add/Change is all that is needed.

[pbumc]

Great, thanks Neil. I've removed Delete profile and family from all users.

[pbumc]

Now that I've restored a backup to a temporary location, I thought I'd correct my previous statement now that I see what actually happened.

The two household members did in fact have different envelope numbers, so a statement was generated for each.

The issue was that when the second profile was moved into the family of the first person, the address was modified like this:

Mailing Name:
Person 1 Name

Address:
Person 2 Name
1234 Main Street
etc.

This meant that the contribution statement for the for Person 1 was printed with this header:

Person 1 Name
Person 2 Name
1234 Main Street
etc.

Person 1 understandably requested that Person 2 be removed from her contribution statement. But the finance guy didn't realize why both were on the statement (a name in the Address field), so wound up deleting Person 1, creating a new family profile for her, and re-entering her 2026 contributions.

A similar household simply sets Mailing Name to "Person 1 Name / Person 2 Name" and leaves address alone. Which is all that would have been needed here.

A perfect storm of misunderstanding the program and too-high privileges. Live and learn.

[pbumc]

Okay I think this is fixed.

Before starting, I reassigned the Envelope Number (for which the financial secretary had re-added contributions) to number 9901, and added "DO NOT USE" to the new family and profile he created.

I remembered that DBF files don't actually delete records until you pack the database, which fortunately we had not. The list of tables that @Zorak posted above was extremely helpful. I went through each of those tables and determined that only six have records we use (atdata, co, copled, me, mecontlogd, and sk). I copied the live data files to a machine running Visual FoxPro 9 and undeleted records with statements like this:

Code: Select all

USE me.dbf EXCLUSIVE
COUNT FOR DELETED() .AND. mail_no = 2410 .AND. pers_no = 1
BROWSE FOR DELETED() .AND. mail_no = 2410 .AND. pers_no = 1
RECALL FOR DELETED() .AND. mail_no = 2410 .AND. pers_no = 1

The CO table got this treatment to restore the old_env_no:

Code: Select all

USE co.dbf EXCLUSIVE
COUNT FOR env_no = 99999 .AND. old_env_no = 1234
BROWSE FOR env_no = 99999 .AND. old_env_no = 1234
REPLACE env_no WITH old_env_no FOR env_no = 99999 .AND. old_env_no = 1234
BROWSE FOR env_no = 99999 .AND. old_env_no = 1234
BROWSE FOR env_no = 1234
COUNT FOR env_no = 1234

After copying the files back to the customer's location, the deleted profile re-appeared with full attendance and contribution history.

One table, mecontlogd, had a discrepency--the backup said I should have 15 records to undelete but I only had 13. The other two ContactIDs were linked to the temporary profile that the finance secretary created. Since these appear to be log entries, I didn't think they were too critical. Do they even appear in the UI? By the way, this table is linked on meid, which I assume is a unique ID for the Personal Profile--I pulled it form the ME table.

The only table I couldn't figure out was EVOCC. That didn't seem to be linked on mail_no or meid. The dates in the records were all pretty old.

Hope that helps someone else who might have to dig out of this. Note that there are stand-alone utilities to browse and undelete DBF records (Claude suggested DBF Viewer Plus or DBF Commander), but I had a licensed copy of Visual FoxPro 9 available, so I used that.