Conditional, global search and replace?

Got a question or comment about PowerChurch that doesn't belong anywhere else? Why not post it here!?

Moderators: Moderators, Tech Support

pbumc
Posts: 77
Joined: Sat Mar 10, 2007 4:28 pm
Location: San Diego, CA

Conditional, global search and replace?

Post by pbumc »

Hi,

Our PC 11.55 database is in need of a deep spring cleaning. I'm looking for ways to do things like this:

- If person has not been in Worship or made a contribution in six months:
-- Set their Personal Status to Member - Inactive or Visitor - Inactive.
-- Remove the person from the Worship activity.

- If all family members are inactive
-- Set the family Mailing Category to Alumni.

Ideally this needs to be repeatable and something I can teach the church admin so she can deactivate old records once a month.

I thought I had remembered seeing a global replace function, but I can't find it. I did find this similar 2008 thread:

viewtopic.php?t=3253

I assume what he refers to as "DB Administrator" is now called "Database Manager"?

Is it down to either writing SQL or working by hand?

Thanks,
Last edited by pbumc on Wed Feb 12, 2020 6:30 pm, edited 1 time in total.
Mark Berry

Zorak
Tech Support
Tech Support
Posts: 3006
Joined: Thu May 13, 2004 9:59 am
Location: PowerChurch Software
Contact:

Re: Conditional, global search and replace?

Post by Zorak »

It is (and always has been) the Database Manager. The OP was probably just mixing up terminology with the System Administrator user.

In any case, there isn't a global search and replace. We have enough problems with people "accidentally" deleting 50 contributors one at a time, clicking through all the warnings that go along with that.

If you want to take that on, it would have to be done outside the software, or at least in the Database Manager directly.

The PowerChurch Plus demo is a good place to sandbox this type of thing and to figure out data structures

https://www.powerchurch.com/demo

There is also a summary of what information is stored in each database file (not full table schemas) at

https://www.powerchurch.com/support/396 ... data-files

pbumc
Posts: 77
Joined: Sat Mar 10, 2007 4:28 pm
Location: San Diego, CA

Re: Conditional, global search and replace?

Post by pbumc »

Thanks for the quick reply! and for the reference material.
Mark Berry

pbumc
Posts: 77
Joined: Sat Mar 10, 2007 4:28 pm
Location: San Diego, CA

Re: Conditional, global search and replace?

Post by pbumc »

Okay here's what I have so far. To run, make sure each line ends in a blank. Copy the script to Notepad++, choose Edit > EOL Conversion > Unix, the copy and paste into the PowerChurch Database Manager window (which will strip the line breaks).

List individuals and their statuses with most recent activity 1002 attendance and contribution dates

Code: Select all

select me.lastname, me.firstname, mecodes.descript as status, max(atdata.date) as last_att, max(co.date) as last_cont 
from me 
inner join mecodes on 
mecodes.field = 'STATUS' and me.status = mecodes.code 
left outer join atdata on 
me.mail_no = atdata.mail_no and me.pers_no = atdata.pers_no and atdata.skill_no = 1002 and atdata.status = 'P'
left outer join co on 
me.env_no = co.env_no 
group by me.lastname, me.firstname, mecodes.descript 
order by me.lastname, me.firstname
List individuals and their statuses if they haven’t attended activity 1002 and haven’t contributed in 12 months. Your me.status codes will probably be different:

Code: Select all

select me.lastname, me.firstname, mecodes.descript as status, max(atdata.date) as last_att, max(co.date) as last_cont 
from me 
inner join mecodes on 
mecodes.field = 'STATUS' and me.status = mecodes.code 
left outer join atdata on 
me.mail_no = atdata.mail_no and me.pers_no = atdata.pers_no and atdata.skill_no = 1002 and atdata.status = 'P' 
left outer join co on 
me.env_no = co.env_no 
group by me.lastname, me.firstname, mecodes.descript 
having ( (max(atdata.date) is null or max(atdata.date) <= gomonth(date(),-12)) 
        and (max(co.date) is null or max(co.date) <= gomonth(date(),-12)) ) 
order by me.lastname, me.firstname 
where me.status in ('QN','VO','OV')
Now, make a backup and DO NOT run this UPDATE unless you know what you're doing!

Update individuals' statuses if they haven’t attended activity 1002 and haven’t contributed in 12 months. Change “Member - Active” to “Member - Inactive”. Your me.status codes will probably be different:

Code: Select all

update me 
set status = 'WP' 
where transform(me.mail_no,[@l 99999]) + transform(me.pers_no,[@l 99999])
in ( 
select transform(me.mail_no,[@l 99999]) + transform(me.pers_no,[@l 99999]) 
from me 
left outer join atdata on 
me.mail_no = atdata.mail_no and me.pers_no = atdata.pers_no and atdata.skill_no = 1002 and atdata.status = 'P' 
left outer join co on 
me.env_no = co.env_no 
group by me.mail_no, me.pers_no 
having ( (max(atdata.date) is null or max(atdata.date) < gomonth(date(),-12)) 
        and (max(co.date) is null or max(co.date) < gomonth(date(),-12)) ) 
where me.status = 'QN' 
)
The trick here, and I hope it's right, is to create a unique key for the "me" table by concatenating 0-padded me.mail_no and me.pers_no. Credit for the 0-padding "transform(me.mail_no,[@l 99999]" statement goes to Luu Minh's July 12, 2017 post in this thread.

The Database Manager is a helpful tool but seems to lack two key features: allowing and preserving carriage returns and line feeds in the command, and allowing selecting the results grid for copying e.g. to Excel.

Feedback and suggestions welcome!

[Edit 01/29/2021: I realized that the SELECTs were including profiles whose attendance was expected (active in the 1002 activity) whether they were Present or not. I updated the three scripts to add "and atdata.status = 'P'", which confirms that the person was Present at the activity.]
Last edited by pbumc on Fri Jan 29, 2021 1:17 pm, edited 1 time in total.
Mark Berry

NeilZ
Posts: 10209
Joined: Wed Oct 08, 2003 1:20 am
Location: Dexter NM
Contact:

Re: Conditional, global search and replace?

Post by NeilZ »

Have you tested this in the Demo? Or installed PCPlus on a non-networked machine with a backup of your data and tested it there? If so, nice work.

As far as the unique key ... don't forget that there are some internal program functions that create the keys and status codes. I'm fairly sure the status codes are generated by the program so if others are using this code, they need to find the status codes used in their system.

That said, I would not attempt a mass update of the data, there are too many things that could go wrong. I'd use this to create an output report that your admin can use to manually do the update. The first time going through this will take time, but it doesn't need to be done all in one day. Taking some time every day over a month will accomplish this. Then once a month this can be run to create the report, and you shouldn't have that many to update each month.
Neil Zampella

Using PC+ since 1999.

pbumc
Posts: 77
Joined: Sat Mar 10, 2007 4:28 pm
Location: San Diego, CA

Re: Conditional, global search and replace?

Post by pbumc »

NeilZ wrote: Have you tested this in the Demo? Or installed PCPlus on a non-networked machine with a backup of your data and tested it there? If so, nice work.
Oh yes, the only way to do this kind of thing is with lots of trial and error on test data. I also have Visual Studio 19 connected to the database using the Visual FoxPro OLEDB connector, which allows a little more insight into the data as well as a more friendly testing platform.
NeilZ wrote: As far as the unique key ... don't forget that there are some internal program functions that create the keys and status codes. I'm fairly sure the status codes are generated by the program so if others are using this code, they need to find the status codes used in their system.
Exactly right, as noted in the post, "Your status codes will probably be different." The mail_no and pers_no keys are numeric, probably just incremented for each new row. Hence the functions to convert them to strings and concatenate them to make up a single key for the me table.
NeilZ wrote: That said, I would not attempt a mass update of the data, there are too many things that could go wrong. I'd use this to create an output report that your admin can use to manually do the update. The first time going through this will take time, but it doesn't need to be done all in one day. Taking some time every day over a month will accomplish this. Then once a month this can be run to create the report, and you shouldn't have that many to update each month.
I hoped to be able to print the results of the SELECTs, or copy them to Excel, but have not found a way other than multiple screen shots. Still contemplating the best way to implement this.
Mark Berry

Zorak
Tech Support
Tech Support
Posts: 3006
Joined: Thu May 13, 2004 9:59 am
Location: PowerChurch Software
Contact:

Re: Conditional, global search and replace?

Post by Zorak »

pbumc wrote:I hoped to be able to print the results of the SELECTs, or copy them to Excel, but have not found a way other than multiple screen shots. Still contemplating the best way to implement this.
After you run a select and view the query results, you can run one of the following to save the result

Code: Select all

COPY TO "C:\TEST.CSV" TYPE DELIMITED
or

Code: Select all

COPY TO "C:\TEST.XLS" TYPE XL5

Zorak
Tech Support
Tech Support
Posts: 3006
Joined: Thu May 13, 2004 9:59 am
Location: PowerChurch Software
Contact:

Re: Conditional, global search and replace?

Post by Zorak »

pbumc wrote:The Database Manager is a helpful tool but seems to lack two key features: allowing and preserving carriage returns and line feeds in the command, and allowing selecting the results grid for copying e.g. to Excel.
Sorry, replying to these in reverse order.

You can write your code in a plain text file saved as something like MYQUERY.PRG, then run DO MYQUERY in the Command window. You can also compile it before running to make sure there aren't syntax errors before running it by calling COMPILE MYQUERY.

pbumc
Posts: 77
Joined: Sat Mar 10, 2007 4:28 pm
Location: San Diego, CA

Re: Conditional, global search and replace?

Post by pbumc »

Thanks! Will test when I can get back to this project.

Mark Berry
Mark Berry

pbumc
Posts: 77
Joined: Sat Mar 10, 2007 4:28 pm
Location: San Diego, CA

Re: Conditional, global search and replace?

Post by pbumc »

Zorak wrote:
Mon Feb 24, 2020 9:29 am
After you run a select and view the query results, you can run one of the following to save the result

Code: Select all

COPY TO "C:\TEST.XLS" TYPE XL5
This is extremely helpful, thank you! With this, I can export a list of people to discuss with others before we make changes. We might even do the changes manually.

One other useful feature I stumbled across: The DB Manger seems to blank out each command after it is run. BUT, it has a command memory! Press the Up and Down arrows to scroll through recent commands.

I was unable to get the DO PROGRAM thing to work--kept telling me the PROGRAM.PRG file does not exist, even when it does. But I can live without that.
Mark Berry

Zorak
Tech Support
Tech Support
Posts: 3006
Joined: Thu May 13, 2004 9:59 am
Location: PowerChurch Software
Contact:

Re: Conditional, global search and replace?

Post by Zorak »

pbumc wrote:
Mon Apr 27, 2020 7:52 pm
I was unable to get the DO PROGRAM thing to work--kept telling me the PROGRAM.PRG file does not exist, even when it does. But I can live without that.
It may just be looking in the wrong directory for the program. Run the following command to see the current working directory. The usual CD... commands let you change directory.

Code: Select all

?curdir()

pbumc
Posts: 77
Joined: Sat Mar 10, 2007 4:28 pm
Location: San Diego, CA

Re: Conditional, global search and replace?

Post by pbumc »

Here's what I tried:

1. Opened Notepad++. Pasted in a command. Edit > EOL Conversion > Unix. Save as C:\Users\<username>\Desktop\test.prg.

2. In PowerChurch DB Administrator, cd \Users\<username>\Desktop\. Use dir *.prg to confirm file is there.

3. do test returns "Error: File 'test.prg' does not exist."

Not sure what I'm missing...
Mark Berry

Zorak
Tech Support
Tech Support
Posts: 3006
Joined: Thu May 13, 2004 9:59 am
Location: PowerChurch Software
Contact:

Re: Conditional, global search and replace?

Post by Zorak »

Run the COMPILE TEST command. I apologize, I thought that step was optional. Compiling will create an FXP file in the same location.

pbumc
Posts: 77
Joined: Sat Mar 10, 2007 4:28 pm
Location: San Diego, CA

Re: Conditional, global search and replace?

Post by pbumc »

Great, thanks. Will try it next time.
Mark Berry

pbumc
Posts: 77
Joined: Sat Mar 10, 2007 4:28 pm
Location: San Diego, CA

Re: Conditional, global search and replace?

Post by pbumc »

Hi Zorak,

We've finished our initial cleanup and I went back in to re-run the SELECT and see if we got everyone. However, the COPY TO command is now returning

Code: Select all

Error :OLE error code 0x80030003: Unknown COM status code.
We just updated to the 04/16/2020 release of PowerChurch 11.55, which also updated the client components. Did this update kill the COPY TO option? We're on Windows 10 1909 x64 Enterprise.

UPDATE: Tried the COPY TO on another computer and it worked. Hmm...do I need to refresh local components on the first computer or something? Not worth spending a lot of time on since I have a workaround.

Thanks and regards,
Mark Berry

Post Reply