How to convert data from ACS to PSFS

If this is your First conversion ever, you must set up your machine for conversions in general.

For every conversion: Set up folders for this conversion

Begin work on the data:

• Pre-conversion work
• Conversion Steps
•  

PreConversion Work

Prepare Data for Conversion

• ACS gives us their data in an Access file. We will call it ACS.mdb to make it easier to communicate but it will likely have a different name.

1. In the ACS.mdb, open each one of the ten tables in Design View and change the data type for Family Number from Text to Number: Activity, 
   
2. In the People table, add PSMMI & PSMemberID as the first two fields as data type Number.
   
3. In the Family table, add PSMMI in the first field as data type Number.
   

Top

Conversion Steps

• Set up Anything Tool for Org
• Begin Conversion of Main Tables
• Finishing Steps

After the conversion is done, the next step is Quality control. You start this step on the conversion laptop.

• Start the QC checklist on the conversion laptop.
  • The first step of the QC checklist is to add the information in the inidata table and then open the conversion in the 3.7.34 on the conversion laptop.
  • After running the Database maintenance check, you then move it to the G drive on the Remote desktop for the 4.2.2 version.   

Add Reports to report's folder

Top

Anything Tool Set up for org and for conversion from this software (A1)

Prepare the Anything Tool to convert the  ACS Database to the PSFS saved in the BlankStandard_PSData34_-Newstructure Access Database

1. 1. Open the AnythingToPS2000v5beta2_ACS tool and select the BlankStandard_PSData34_-Newstructure Access Database
      
      
      
   2. Enter the Parish OrganizationID in the Find Conversion For box, press Tab, and enter it again it the POL ID. Press Tab again and click the New Conversion box. (1-3)
      
   3. (A) Enter the Organization Name Format: ParishName_CityState_OrgID  shown above.
   4. In 2, Name of System from which we are importing data, 
      • (B) Select ACS from the dropdown  shown below
      • (C) In 2a, attach to the ACS.mdb file of their imported data tables. Make sure you select and click Open rather than double-click.
      • In 3 Clone from, enter 12685 (D) from the dropdown.
        

Back to Conversion Steps

Begin Convesion for the Main Tables (T1)

..

• Family
• Members
• Member/Family Record Queries
• Sacraments
• Append Tables Checkmarks/Queries
• Sac Migrate
• Contributions
• Pledges
• Back to Conversion Steps

Family (F1)

1. From the AnythingTool Main screen, click the Families button.
   
2. Select the dropdown to Retain Membership ID in this field: PSMMI
   
3. City Field:
   • Mark the Translation Table for the City Field and click the Create Table button.
   • Click the Extract Data button.
     
4. Check the ACS.mdb People table for the phone number to see if an area code is included in even one record.
   • If yes, leave the box blank.
   • If no area code exists in the HomePhone field in any record, mark the box as shown below. 
     
     
     
5. The Membership software has a prompt asking if the member wants their phone number to be published. Most other software prompts for the opposite: Mark 'yes' if you want to keep your phone number unlisted. So, we often must juxtapose the answer because a yes for publish is a no for unlisted.
   • Mark the box to the left of Create Table.
   • In the Publish_Phone pop-up screen, click the Extract Data button.
   • Since the ACS software is asking the unenlisted question, in the ParishSOFT Data fields, change the data to the opposite of what they have. If they have -1, use 0. If they have 0, use -1.
     
6. Notes Field:
   • Select the Auxilary Table box and click the Aux Table Button.
   • Click the Show Data button
     
7. There are two fields that seem to deal with status: Status (Y/N) field and the FamGroupID field. For each one:
   • Mark the Translator Table box then click the Create Table button.
   • Click the Extract Data button
   • Use your value judgment in the unclear cases for the results.
     
   • Click the Populate Table button at the top of the Family screen and make note of the count. Compare it to the checklist.
     
8. Close the Family Screen by clicking the X in the upper right corner.

Back to Main Tables Menu

Members (M1)

1. From the AnythingTool Main screen, click the Members button.
   
2. ACS allows customizable fields. These field names begin with the letters 'Ind.' Here are some examples.
   
   • As you go through the Members Table building process, check that all of the ACS customizable fields (in the example below they are all highlighted in yellow) match the correct ParishSOFT fields.
   • The first thing to do is to select PSMemberID in the last field on this screen, 'Retn MemberID here.
   • For any instruction where you are told to click the buttons, if you don't see the button (Aux Table or Show Xlator), mark the checkbox in that column and the button will display.
     
3. For the following two fields (Salutation & Suffix), you will go through the same process as follows:
   
   
   • Mark the box in the Translation Tables column.
   • Click the Show Xlator button.
   • Click the Extract Data button.
   • For any not found in the table, click Add to add the text to the lookup table.
     Salutation:
     
     Suffix:
     
4. For the following three fields (Work Phone, Mobile Phone, and Email), you will go through the following same  process:
   
   • Click the Aux Table button.
   • Click Show Data on each popup screen.
   • Exit the Popup.
5. For the Sex Field, click the ShowXlator button. Then, click Extract Data then Exit. Sometimes you must match up the fields such as Male to M and Female to F. That is all. 
   
6. For the following two fields (Member Status, Member Type), you will go through the same following process.
   
   • Click the Show Xlator button
   • Click Extract Data
   • Update the ParishSOFT Data dropdown to match the Family Suite text.
   • Exit the Data Translator Builder Screen.
     MemberStatus:
     
     Review the checklist to see how they would like these set. ???
     MemberType: Note that Child or Youth gets changed to Son. It is later updated to the correct sex via a query based on the first name.
     
7. Career Type: Click Show Xlator then click the Extract Data button in the popup window. Exit out. 
   
8. Ethnic ID: Click Show Xlator, then click the Extract Data button, Match the ACS terms to the correlating PSFS terms. If Any missing, click the Add button. Exit the pop-up screen.
   
   
9. Grad Year: Enter the correct grad year for grades. If Grade is tracked rather than the year, must calculate grad year. There is a spreadsheet attached to this article that will help you calculate the grad year.
   
10. Next two: Career Descriptions, School, click Show Xlator then Extract data Exit.
    
11. Next four: Education, Religion, Language, Spec Needs Desc, Show Table, Match up fields, If not found click the Add New button to the left of each. Exit. *If there are not these fields, they will not show with the checkmark.
    
12. Notes Field: Click the Aux Table button and in the pop-up screen, click Show Data. Exit.
    
    
13. As noted at the top, make sure the PSMemberID is in the Retn MemberID Here field.
14. Click the Populate Table button at the top of the Members screen.
    
15. Exit the Member Screen by clicking the Exit button at the top right.

Back to Main Tables Menu

Member/Family Records Queries (Q1)

In the Anything Tool, change the view to Queries, and run the following queries to update both the Family and the Member records that have been created prior to this point.

Note Queries:

• qACS-FamilyNotes1: This appends the note date to the front of the note for notes marked as Family Notes
• qACS-FamilyNotes2: This appends the note date to the front of the note for notes marked as Individual Notes and adds the word "Individual" to the note.
• qACS-FamilyNotes3: Same as above but for "business"
• qACS-IndividualNotes: Same as above except uses the word "Sacramental" and there's no date involved.

Phone and Email Queries: These basically make sure the right data goes to the right place in Family Suite.

• qACS-IndividualPhone-Business
• qACS-IndividualPhone-Cell
• qACS-IndividualPhone-MobileIfNull
• qACS-IndividualPhone-PagerBeeper
• qACS-MembersEmail1-Preferred
• qACS-MembersEmail2OtherIfNull

Back to Main Tables Menu

Sacraments (S1)

1. From the AnythingTool Main screen, click the Sacraments button
   
2. ACS allows customizable fields. These field names begin with the letters 'Ind.'
   • Here are some examples.
     
   • You will need to match which of these 'Ind...' fields should be matched with each of the fields on this screen.
3. Click the Magnifying glass for Marriage_Status.
   • Delete any that PSFS doesn't track. For example, Single, Engaged, Cohabitating, are not tracked in PSFS.
   • Update those PSFS does track but that is listed under a different name. For example, Remarried is tracked in PSFS as Married.
     
     
4. Click the magnifying glass for Currently Married or  Former_Marriage; note says: Possibly need to add Former Marriage or Mixed Marriage dependent on Marital Status used in PDS.
5. Click the magnifying glass for matrimony; any that are not listed here (single, unknown, engaged, living together) delete. (those with red lines get deleted)
   
6. Click the magnifying glass for CanonicalMarriage, (those with red line get deleted)
   
7. Click the magnifying glass for Baptism
   
8. Click the magnifying glass for Communion.
   
9. Click the magnifying glass for Confirmation.
   
10. Possibly need to add dates for baptism, communion, and confirmation if this data exists in the People Table. Does not need translation; add any additional dates as needed.NEW Record button. 
11. Remove any that do not exist.
    
12. Once all of the sacraments have been updated, click the Convert Data button at the top of the Sacraments screen.
    

Back to Main Tables Menu

Append Tables Checkmarks & Queries (C3)

At the bottom of the main screen, mark the following queries and then
Click 7. Run Selected Queries (To see this on the main screen, see the screen under SacMigrate below)

• Append New Career Types
• Append New Religions
• Append New Schools
• Make (Married) Heads Husband/Wife
• Create Members to Memberless Fam
• Append Blank Sacrament Records
• If they have languages, need to mark the Append New Languages option also. Possibly on checklist but must look at the table too.

Back to Main Tables Menu

Sac Migrate (S2)

• Be prepared for the following to take a long time. If the database is big, you may want to run this overnight or if moderately big, over lunch.
• Click the SacMigrate button
  

Back to Main Tables Menu

Contributions (C2)

• On the top right of the main Universal Tool screen, click Contributions.
  
  
  1. Click the COA Builder Screen.
     • Click Extract Data
     • Click Add New Items to COA Table
     • Exit the Chart of Accounts Builder.
       
  2. Run Queries
     • qACS1-UpdateCOAAccountNumbers
     • q70AppendContributions-ACS
  3. If the giving for this organization is family-based, run this query:
     • qACS2-Make1or11aFamilyContirbution

Back to Main Tables Menu

Pledges (P1)

1. 1. Click the Pledge mapping screen ???***
      back into contributions and will add to this instruction when have one with pledges to convert.
      

Back to Main Tables Menu

Finishing Steps (F2)

1. ???(Added this from prior doc) Check the box for Update Tables
2. Click the button for 13. Insert Data Into ParishSOFT
   
3. When the following error displays, click the OK button twice.
   
4. Copy the text from the "Conversion Logs" Results screen to a Word Doc and save it in the Reports folder for the parish.

Back to Conversion Steps

Reports in the To Parish File (R1)

Duplicate Envelope List: Comes from the QC Process. May or may not have duplicate envelope list.

Conversion Log

Potential Duplicates: Comes from the QC Process. May or may not have potential duplicates.

Back to Top

Go back to the Grad Year instruction

• Graduation_Year.xlsx10 KB

April 17, 2023 17:20