home

PDS

RealmRealm AccountingMinistryPlatformGivingPDSACSConnectShepherdGrowth MethodHeadMasterRefresh WebsitesTrust CenterGo MethodMissionInsite
RealmRealm AccountingMinistryPlatformGivingPDSACSConnectShepherdGrowth MethodHeadMasterRefresh WebsitesTrust CenterGo MethodMissionInsite

What would you like to know more about?

Show Contents

thumbnailPDS

Formation Office User Guide

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, sacraments and sacrament places, financial data, different keyword 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 in your PDS program, 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, click Select Program Number (N=). 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 the number of families and members in your program(s).
    1. To view the records associated with each total, click Show List.
    2. Click Print Totals Worksheet to view all the totals related to records in each category. Use this worksheet to double-check your numbers and further investigate anything you need to.
  5. Click Next.
Clean Up Family Data:
  1. Review the information about families.
    1. 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.
    2. If there are any families with no members, you can add a head and spouse record based on the family name, formatted as "Last,First(Spouse),Title,Suffix". For example, "Smith,John(Amy),M/M,Sr." would produce a head record for Mr. John Smith, Sr. and a spouse record for Mrs. Amy Smith. If the Title suggests a gender, it is also added to the new member record(s). Click Add Head & Spouse to create the member records, then click Yes to continue.
    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. You can remove old family interactions from the Letters, Visits, Etc window of family records. Enter the date (the default is 5 years prior to the current year), review the number of affected entries, then click Delete Entries Before <Date> to remove those entries from your data.
    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.
    6. If there are any unused families (that is, families who meet ALL of the following conditions: no payments or their last payment was more than 7 years ago, no members have any active ministries, no members have a sacrament preparation class within the last 2 years, no members have a Formation Enrollment date within the last 2 years, no members have any current Formation classes, or no members have any attendance dates of any type within the last 2 years), you can click Delete Unused Fams to remove those families from your data.
    7. If needed, click Find Duplicate Families to identify possible duplicated records. Duplicates are based on matching last names and street numbers, and this does not include inactive or archived records. If duplicates are found, click Show List to review them. You can then use the Combine Families process to clean up your records.
  2. Click Next.
Clean Up Member Data:
  1. Review the information about members.
    1. If you need to view the members of a listed group, click Show List.
    2. 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}".
    3. 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.
    4. If there are any members whose last name differs from their family's last name, the number displays. However, it may reflect manual changes made in your data. Click Recalculate Flags to make sure this data is accurate and reset the flags if needed.
    5. If there are any members who are 18 years or older but are marked as a child, the number displays. Click Set Type to Adult to change the Type field on the records of those members.
    6. You can remove old member interactions from the Letters, Visits, Etc window of member records. Enter the date (the default is 5 years prior to the current year), review the number of affected entries, then click Delete Entries Before <Date> to remove those entries from your data.
  2. Click Next.
Clean Up Sacramental Data:
  1. Review the information about duplicate sacrament places.
    1. Click Print Worksheet to view the information for the possible duplicates. Use this worksheet to determine which sacrament places need to be combined, if any.
    2. For each line in the grid, review the matching churches and determine which duplicates need to be combined.
      Tip: To view a list of member records where a place's information is being used, click Print Usage in that place's column.
    3. Select the checkbox beside the parish you want to clean up.
    4. Below the grid, the columns display information from each sacrament place. Select the name, address, email, phone, and diocese information you want to keep.
    5. Repeat this for each line of duplicates you want to clean up.
    6. When you're finished, click Combine Selected Sacrament Places.
      The information that you did not select will be removed from any member records. It is replaced with the information you selected to keep.
  2. Review the member sacraments.
    1. Click Print Worksheet to view the counts for each sacrament name as well as the total number of sacrament types and entries. Use this worksheet to determine which sacraments need to be combined, if any.
    2. 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.
  3. Click Next.
Clean Up Financial Data:
  1. 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 Show List.
    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 Tuition & Fees 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 Tuition & Fees 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 Tuition & Fees 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 Show List and use the list outside of this process 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 Show List and use the list outside of this process 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 Tuition & Fees 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. If there are any unused fund activities (that is, activities that have been added in the Fund Activities list but not used for any funds), click Show List to view them. To remove these from your data, click Remove Unused Fund Activities.
    10. If there are any unused family fund periods (that is, fund periods that have been added to a family but have no pledges and no history entries), click Show List to view them. To remove these from your data, click Remove Unused Fund Periods.
    11. If there are any fund periods that have been created in the Fund Setup window before the selected date (7 years, by default), click Show List to view them. (Note that the number reflects the fund periods, not the families using those periods.) If you want to consolidate these fund periods, select an option for Monthly, Quarterly, or Yearly, and click Consolidate.
      Consolidating works just it does in the Fund Setup window to process the families and consolidate all the fund history entries within those periods into monthly, quarterly, or yearly totals. This way, you keep the totals for each fund period while removing the extra details you don't need anymore.
  2. Click Next.
Clean Up Keyword Data:
  1. Review any exact-matching duplicate keywords.
    1. If duplicates are found (that is, entries that match 100% exactly), the keyword displays in red text and the number beside it indicates how many there are. Hover your mouse over the number to see which entries are duplicated.
    2. Click Print Worksheet to view each keyword name, count, and any duplicate values. Use this worksheet to determine which keywords need to be combined, if any.
    3. To clean up the duplicate entries for all keywords, click Eliminate Duplicate Keywords, then click Yes to continue. This combines the duplicate entries and eliminates the duplicate from each keyword list.
    4. A new number displays beside the keywords after eliminating duplicates. This indicates how many records were updated in order to replace the duplicate entries.
  2. Click Next.
  3. 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 phone type descriptions that indicate some relation to a certain family member. For example, "Spouse Cell", "Dad's Office", or even "Mary's Phone". Click Assign Member Phones to assign matching phone numbers to member records (as indicated in the list below). You can print a report of the changes that are made.
      • Types with "Head" or "H" in the description are assigned to the record for the head of household.
      • Types with "Spouse" or "S" are assigned to the spouse's record.
      • "Husband", "Husband's", "Dad", "Dad's", "Father", "Father's", or "His" are assigned to the head or spouse marked as Male.
      • "Wife", "Wife's", "Mom", "Mom's", "Mother", "Mother's", or "Her" are assigned to the head or spouse marked as Female.
      • The first word in each type is checked for a person's name. If the member's first name matches exactly, the phone is assigned to them. (Note that this doesn't match nicknames or double names, such as "Bob"/"Robert" or "Mary Ellen"/"Mary".)
    3. You may have unneeded phone type descriptions in your data. To clean up this list, merge different phone types into one of the types you use, or select No Change to keep the existing phone type. (To define the phone types you can choose from, click Phone Type Choices. Select the ones you want, or reset the list to use the default values.)
      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.
    4. 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.
    5. If there are any phone numbers that have an invalid format, click Show List to view them. If you want to correct these, click Fix Invalid Phones.
  4. Click Next.
  5. 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 email type descriptions that indicate some relation to a certain family member. For example, "Spouse", "Dad's Office", or even "Mary's Personal". Click Assign Member Emails to assign matching email addresses to member records (as indicated in the list below). You can print a report of the changes that are made.
      • Types with "Head" or "H" in the description are assigned to the record for the head of household.
      • Types with "Spouse" or "S" are assigned to the spouse's record.
      • "Husband", "Husband's", "Dad", "Dad's", "Father", "Father's", or "His" are assigned to the head or spouse marked as Male.
      • "Wife", "Wife's", "Mom", "Mom's", "Mother", "Mother's", or "Her" are assigned to the head or spouse marked as Female.
      • The first word in each type is checked for a person's name. If the member's first name matches exactly, the email is assigned to them. (Note that this doesn't match nicknames or double names, such as "Bob"/"Robert" or "Mary Ellen"/"Mary".)
    3. You may have unneeded email type descriptions in your data. To clean up this list, merge different email types into one of the types you use, or select No Change to keep the existing email type. (To define the email types you can choose from, click Email Type Choices. Select the ones you want, or reset the list to use the default values.)
    4. 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.
  6. Click Next.
  7. 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, merge different marital status types into one of the types you use, or select No Change to keep the existing marital status type. (To define the marital status types you can choose from, click Marital Status Choices. Select the ones you want, or reset the list to use the default values.)
    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.
  8. Click Next.
  9. Review the information about sacrament sponsor types.
    1. Click Print Worksheet to view the counts for each sacrament sponsor type as well as the total number of sacrament sponsor types and entries. Use this worksheet to determine which of the "simple list" types each sacrament sponsor type should be changed to, if needed.
    2. You may have unneeded sacrament sponsor type descriptions in your data. To clean up this list, merge different sacrament sponsor types into one of the types you use, or select No Change to keep the existing sacrament sponsor type. (To define the sponsor types you can choose from, click Sponsor Type Choices. Select the ones you want, or reset the list to use the default values.)
    3. When you're ready to make the selected changes in your data, click Update Sponsor Types, then click Yes to continue.
      The values of any merged sacrament sponsor types are updated on records, and any unused sacrament sponsor types are removed from the list.
  10. 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.
Clean Up Shared Data:
  1. 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:
    • 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 Formation Office data.
    • Specific groups are singled out for a separate data set, such as the Knights of Columbus.
    • Churches are grouped together in a single data file, such as a mission or cluster church.
    1. If you need to print or export a list of N=# families to a spreadsheet, click Show List.
    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.
  2. Click Finish. To exit the process, click Yes.

After you run this process, the date is updated for Last Clean Up Data on the Dashboard.

Watch & Learn

Learn about the Clean Up Data tool, which examines your data for potential issues and allows you to quickly fix them. 17 min 13 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, visit the PDS Video Library.

Best Practice
Video
portal
Church_Office
Formation_Office

Last updated: December 10, 2024