Conditional, global search and replace?
Moderators: Moderators, Tech Support
Conditional, global search and replace?
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,
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
-
- Tech Support
- Posts: 3015
- Joined: Thu May 13, 2004 9:59 am
- Location: PowerChurch Software
- Contact:
Re: Conditional, global search and replace?
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
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
Re: Conditional, global search and replace?
Thanks for the quick reply! and for the reference material.
Mark Berry
Re: Conditional, global search and replace?
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
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:
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:
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.]
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
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')
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 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
Re: Conditional, global search and replace?
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.
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.
Using PC+ since 1999.
Re: Conditional, global search and replace?
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: 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.
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: 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.
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.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.
Mark Berry
-
- Tech Support
- Posts: 3015
- Joined: Thu May 13, 2004 9:59 am
- Location: PowerChurch Software
- Contact:
Re: Conditional, global search and replace?
After you run a select and view the query results, you can run one of the following to save the resultpbumc 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.
Code: Select all
COPY TO "C:\TEST.CSV" TYPE DELIMITED
Code: Select all
COPY TO "C:\TEST.XLS" TYPE XL5
-
- Tech Support
- Posts: 3015
- Joined: Thu May 13, 2004 9:59 am
- Location: PowerChurch Software
- Contact:
Re: Conditional, global search and replace?
Sorry, replying to these in reverse order.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.
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.
Re: Conditional, global search and replace?
Thanks! Will test when I can get back to this project.
Mark Berry
Mark Berry
Mark Berry
Re: Conditional, global search and replace?
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.Zorak wrote: ↑Mon Feb 24, 2020 9:29 amAfter 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
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
-
- Tech Support
- Posts: 3015
- Joined: Thu May 13, 2004 9:59 am
- Location: PowerChurch Software
- Contact:
Re: Conditional, global search and replace?
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()
Re: Conditional, global search and replace?
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...
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
-
- Tech Support
- Posts: 3015
- Joined: Thu May 13, 2004 9:59 am
- Location: PowerChurch Software
- Contact:
Re: Conditional, global search and replace?
Run the COMPILE TEST command. I apologize, I thought that step was optional. Compiling will create an FXP file in the same location.
Re: Conditional, global search and replace?
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
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,
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.
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