Change From Special Code to Mailing Category?

[bschlect]

Is there a way to set a certain mailing category for all records in the family mailing list that have a particular value for Special Code 1? I imagine this is something that needs to happen through the Database Administration Utility. If PC10 ran on a MySQL database, this would be accomplished with an UPDATE command, but is there something similar to that I can run here?

Thank you, in advance, for any help.

[NeilZ]

Is there a way to set a certain mailing category for all records in the family mailing list that have a particular value for Special Code 1? I imagine this is something that needs to happen through the Database Administration Utility. If PC10 ran on a MySQL database, this would be accomplished with an UPDATE command, but is there something similar to that I can run here?

Thank you, in advance, for any help.

Its a Foxpro database, and you can use SQL commands to change the database. That said, its best to verify any SQL you're going to write with the techs here, as there may be some database dependencies that may also need to be adjusted. I would give them a call, to see what needs to be done.

FWIW ... how many families would this affect ?? Sometimes its just as easy (and safer) to do this by hand.

[bschlect]

When I called PC to ask about this, they suggested that I do it by hand. We have approximately 4,010 records, however, that will be affected by this change. So, SQL makes more sense. I will backup my database before running any queries, but beyond that, any other suggestions? Do you know which tables this involves?

[NeilZ]

When I called PC to ask about this, they suggested that I do it by hand. We have approximately 4,010 records, however, that will be affected by this change. So, SQL makes more sense. I will backup my database before running any queries, but beyond that, any other suggestions? Do you know which tables this involves?

Put it this way .... the techies at Powerchurch know what they're talking about. If they say do it by hand, I would do it by hand. Really, it shouldn't take more than a day to do this. When I had to do this in a smaller church, I just trained a volunteer to do the job. Just gave them permissions to update the membership area.

The PC techis probably know of database dependencies that can only be adjusted by using the program to do the changes.

As a programmer, I know that there are often programmatic edits, and functions that change many tables at once, and not all with the same data.

[Zorak]

The backup is very important!

What you will find is the table MA holds family information and references a two digit "code" for each of the code fields, Mailing Category and all the custom code fields. The two digit codes are related to the MACODES table.

Standard SQL queries should work fine. You can also do a Google search for VFP9 "Replace" syntax.

[bschlect]

Thank you for your suggestions. I was able to fix this using the database manager, without any issues. I included what I did, for anyone else who might find themselves in a similar quandry.

NB / Disclaimer: Before running any commands in the Database Administrator, BACKUP YOUR DATA. It is possible to both corrupt PowerChurch and to make it do exactly what you want with the Administrator; I hope that you do the latter, but I am not responsible if
you do the former. The queries below are exactly what I used, and things worked fine for me, but I am not liable for their use or misuse or any damages resulting from such activity. Whew.

1. Entered the DB Administrator.

2. I knew I was going to be working with the "MA" (Family Mailing List) table, but I didn't know what fields. A good old-fashioned SELECT told me what I needed to know:

Code: Select all

 SELECT * FROM MA

3. Great. If you read the previous posts, I was after switching all records with a certain Special Code into a certain mailing category. First task then was the find the column that contained this Special Code. The column I was after I found to be titled "Special1."

4. To confirm that I had the right field name, I checked the number of entries that contained the value I was expecting ("CR") with this query:

Code: Select all

SELECT COUNT(Special1) FROM MA WHERE Special1 = "CR"

5. 4018 entries. Perfect. I have the right field. Now I needed to figure out which field linked people with a Mailing Category. Ran the "SELECT" statement from Step 2 again. I guessed it was the field named "Category."

6. So, now I needed to find out which tables linked the Category names with the category codes which appear in the MA table. You can find these in the MACODES table, which I learned from the PC website, here: (I tried adding the URL here, but the forum won't allow me to. Email me for a link, or just search for "tables" in the PowerChurch knowledge base.)

7. A "SELECT *" on the MACODES showed that the table contained a field named "Field." All the entries titled "Category" had descriptions that matched mailing categories, so I assumed I had found it. I found the mailing category I was after, noted its code ("FC") and prepared to make some changes to the database.

8. I used a simple UPDATE statement to add all the records that had a specific Special1 code to this mailing category.

Code: Select all

UPDATE MA SET Category = "FC" WHERE Special1 = "CR"

9. Easy enough. Nothing crashed, and I didn't get any errors. To check that the query operated as expected, I queried to see the number of records that were now linked to the "FC" Mailing Category:

Code: Select all

SELECT COUNT(Category) FROM MA WHERE Category = "FC"

10. I was surprised, because the query returned 4,095; 77 more records than I expected. I suspected that there had already been some records that were specified under the "FC" mailing category, that did not have the Special1 code of "CR." I ran this query, to confirm:

Code: Select all

SELECT COUNT(Category) FROM MA WHERE Category = "FC" and Special1 != "CR"

11. Sure enough, the query returned a count of 77. This answer confirmed my suspicions.

12. Just to confirm that everything was still in tact, I exited the DB Administrator and checked a few entries in the Mailing List, through the PC main interface. Everything looked fine, and records were grouped in the correct mailing categories.

[NeilZ]

Looks like a standard SQL update, good to know there are no program edits to worry about.

[Zaphod]

We disabled links in emails as a spam-combating technique. Sorry about that.

It sounds like you were pretty thorough, and thanks for sharing that with other users. One note though, a reindex would probably be a good idea after making any kind of external data change like that. (Utilities -> Reindex -> All Files).

[bschlect]

Thanks for that tip. I will reindex the data.