Clean Up Your Data

You can run this process to neaten up your files and find potential issues in your data. This can help make data conversion easier. You'll be presented with information around families, members, financial data, phone and email types, and families shared with other programs (if applicable). In each step of the process, you can view a list of affected records and perform any actions to clean up the data in question.

We recommend that you print the worksheets available in each step of the process before cleaning up your data. More information about these worksheets is provided in the steps below.

CAUTION: This process can take some time depending on the amount of data in your program. Once your data is examined, you are in control of which changes, if any, are made. Before you make any major changes, you're prompted to back up your data.
  1. On the File menu, click Test/Fix/Clean Up Data > Clean Up Data.
  2. Click Yes to continue with the process.
    It may take a few minutes to examine your data.
  3. If you have multiple program data sets, enter the number for the data set you want to clean up in Show Church N= and/or Show Formation N=, then click Recalculate. Click Yes when you're ready to run the process with these data sets.
  4. Review the information about families.
    Note: Totals include families who are visible in either Church Office or Formation Office.
    1. Click Print Totals Worksheet to view all the cleanup totals related to family, member, and financial information, as well as each "N=" category. Use this worksheet to double-check your numbers and further investigate anything you need to.
    2. If there are any families with no last name, no members and no donations, no members but the family has donations, or no head or spouse, click Show List. From the list, you can select a family and click Go to Family to open the record. Make any necessary changes to the affected records, then return to the Clean Up Data process.
    3. If there are any families with no head or spouse, you can add a head of household without leaving the process. Click Add Head to change the member type of the oldest member in the family to "Head of Household", then click Yes to continue.
      Note: This action relies on the Birth Date field in each member's record.
    4. If you need to print or export a list of families to a spreadsheet, click Open File.
      The file is saved in your PDS program folder.
    5. If there are any archived family records (that is, records that have been deleted from either Church Office or Formation Office but not removed from the data), you can click Delete Arch Fams to remove those families and members from your data.
  5. Click Next.
  6. Review the information about members.
    Note: Totals include members who are visible in either Church Office or Formation Office.
    1. Click Print Worksheet to view the counts for each sacrament as well as the total number of types and entries. Use this worksheet to double-check your numbers and further investigate anything you need to.
    2. If you need to view the members of a listed group, click Show List. From the list, you can select an individual and click Go to Member to open the record. When you're finished, return to the Clean Up Data process.
    3. If you need to print or export a list of members to a spreadsheet, click Open File.
      The file is saved in your PDS program folder.
    4. If there are any archived member records (that is, records that have been deleted from either Church Office or Formation Office but not removed from the data), you can click Delete Arch Mems to remove those members from your data.
      Note: There may be archived members who belong to visible families, so families of archived members will not be deleted when you delete archived members.
    5. If there are any member names that need to be cleaned up (that is, names with missing or unnecessary parentheses, square brackets, or curly braces), click Clean Up Names to correct these records.
      For example, "Andrews,Christy),Ms." would be corrected to "Andrews,Christy,Ms." and "Waltham,James{B" would be corrected to "Waltham,James{B}".
    6. Review the member sacraments. If there are duplicate names for the same sacrament, you can combine them. Select the sacraments you want to combine, then select which name you want to keep and display on records. Click Combine Duplicate Sacraments, then click Yes to continue.
  7. Click Next.
  8. Review the information about financials.
    1. Click Print Worksheet to view the amounts for each fund, the number of entries, and all the grand totals. Use this worksheet to double-check your numbers and further investigate anything you need to.
    2. If you need to print or export a list of families with no donations, click Open File.
      The file is saved in your PDS program folder.
    3. Select Totals in Church and Formation / Totals in Archived Families to show the financial totals for families who are visible in your program(s) separately from the totals for archived families. Or, select Totals in All Records (Including Archived) to show all financial totals together.
    4. Review the amounts for each fund period in the grid.
      • Pledged—This is different than the pledge you see in the Rates grid in the Contributions window. This is the total due, including the pledge amounts plus any individual charges and initial payments in the fund history. If the amount the family paid to that fund period exceeds the pledged amount, that excess is also included in the Pledged amount.
      • KW1 and KW2—If you selected to show totals separately, you can view the family fund keywords 1 and 2. These are the Keyword fields located in the Rates/History/Keywds section of the Contributions window. If a family has a value selected for Keyword 1, then the number of members in that family is added to the KW1 total. If a family has a value selected for Keyword 2, then the number of members in that family is added to the KW2 total. This is useful when converting data for programs where information is based on members rather than families.
    5. Review the grand totals.
      • Grand Totals in Church and Formation—If you selected to show totals separately, this section displays totals for the left side of the grid, including the Count, Paid, and Pledged columns. Grand Total Paid is the sum of the Paid column in the grid, and Grand Total Pledged is the sum of the Pledged column in the grid. Total Givers, however, is not the sum of the Count column since many families could be counted in multiple fund periods. Instead, it's the total number of families who have given toward any fund listed.
      • Grand Totals in Archived Families—If you selected to show totals separately, this section displays totals for the right side of the grid, including the Archived Count, Archived Paid, and Archived Pledged columns.
      • Grand Totals for All Families—If you selected to show all totals together, this section displays totals for the Count, Paid, and Pledged columns for all families, visible and not visible in Church Office and Formation Office.
    6. If applicable, review the Funds with Bad Totals. This field only displays if bad totals were found in your data. This means there are one or more lines in the Contribution History list where the activity does not belong in the fund. In some cases, the activity may be blank. If you have either of these, click Open File and use the list to locate the incorrect activity in each affected family's contributions, then select a more appropriate activity.
    7. If applicable, review the Funds with Recurring Payments. This field only displays if recurring payments were found in your data. There is an option for using payments instead of pledges in the terms/rates, which is valid for things like scholarships. However, you may want to check these for potential mistakes. Click Open File and use the list to locate the recurring payment in each affected family's contributions, then select a more appropriate recurring act.
    8. If applicable, review the bad funds at the top of the grid, indicated by an asterisk. These are fund periods that are used in the Contribution window but are not defined in the Fund Setup. This is usually the result of data damage. To add the missing fund periods back in the Fund Setup window, click Add Bad Funds at the bottom, then click Yes to continue.
      Once you add bad funds back in, the asterisks are removed.
  9. Click Next.
  10. Review the information about phone types.
    1. Click Print Worksheet to view the counts for each phone type as well as the total number of phone types and entries. Use this worksheet to determine which of the "simple list" types each phone type should be changed to, if needed.
    2. You may have unneeded phone type descriptions in your data. To clean up this list, you can merge different phone types into one of the most common types: Home Phone, Cell/Mobile Phone, or Work. Or, keep the existing phone type by selecting No Change.
      CAUTION: If you use text messaging through your PDS program, don't map types beginning with "Text". Select No Change in order to keep those in your list.
    3. When you're ready to make the selected changes in your data, click Update Phone Types, then click Yes to continue.
      The values of any merged phone types are updated on records, and any unused phone types are removed from the list.
  11. Click Next.
  12. Review the information about email types.
    1. Click Print Worksheet to view the counts for each email type as well as the total number of email types and entries. Use this worksheet to determine which of the "simple list" types each email type should be changed to, if needed.
    2. You may have unneeded email type descriptions in your data. To clean up this list, you can merge different email types into one of the most common types: Primary or Other. Or, keep the existing email type by selecting No Change.
    3. When you're ready to make the selected changes in your data, click Update Email Types, then click Yes to continue.
      The values of any merged email types are updated on records, and any unused email types are removed from the list.
  13. Click Next.
  14. Review the information about marriage status types.
    1. Click Print Worksheet to view the counts for each marital status type as well as the total number of marital status types and entries. Use this worksheet to determine which of the "simple list" types each marital status type should be changed to, if needed.
    2. You may have unneeded marital status type descriptions in your data. To clean up this list, you can merge different marital status types into one of the most common types: Divorced, Married, Partnered, Single, Separated, or Widowed. Or, keep the existing marital status type by selecting No Change.
    3. When you're ready to make the selected changes in your data, click Update Marital Status Types, then click Yes to continue.
      The values of any merged marital status types are updated on records, and any unused marital status types are removed from the list.
  15. Click Next. If you see the Finish button instead, it means the Clean Up Process did not detect any shared families in your program, and you are done! Enjoy the rest of your day.
  16. Review the information about families shared with other programs. These are families that have been designated as N=2, N=3, and so on. This happens for a few reasons, like:
    • Churches are grouped together in a single data file, such as a mission or cluster church.
    • Specific groups are singled out for a separate data set, such as the Knights of Columbus.
    • Schools track pre-school, grade school, middle school, and high school in a single data file for convenience. Some of this old school data may still be present in your Church Office data.
    1. If you need to print or export a list of N=# families to a spreadsheet, click Open File.
      The file is saved in your PDS program folder.
    2. If there are any family records that you no longer need, you can click Remove N=# Families to remove those families and members from your data.
      CAUTION: Use the file provided to make sure you understand which family records will be deleted.
  17. Click Finish. To exit the process, click Yes.

Watch & Learn

Learn about the Clean Up Data tool, which examines your data for potential issues and allows you to quickly fix them. 8 min 55 sec

It may take a minute for the video to display. To watch in full screen mode, click the expand icon in the video player.

To watch more videos, go to PDS Videos.