Take a system excel reports, Query, Prompt, Decide, update excel file to upload current CRM Data.
Budget: $50 – $0 USD
My client operation and my Customer Relationship data need to be compared and updated as they do not talk to each other. My primary goal is to compare and update the addresses. Test data is being used.
Report 1:
This is a downloaded system report that links a Client SSN to a household. There can be multiple client SSN in a household. (Spouse, Kids Biz. Trust)
If the household text is empty please alert an error box, So I can update the household name in the System and redownload this report to run again.
This report shows duplication of records because it would include all account level data which is not needed. all accounts
Report 2:
SSN Field Can contain duplicates from outside system feed accounts, however we need to make SS/Tax id unique in this report.
Error test System Report 2 against duplicate SSN in column. Probably a 20 or less is duplicated of 1000.
Show mailing address differences in a pop up selection box allowing me to choose the correct Residential, and Mailing Address, then save the report with a unique SSN, with the correctly selected address from the user. This data will be used later.
Report 3:
Household should be Unique, but if not Check for duplicates of Household, then show the values in a dialog box, and prompt user to add the values together, choose one, or manually type in a value.
Then update CRM Upload sheet with the correct data. Its color coded.
CRM Download:
This report is downloaded from my customer relationship manager I would like to compare Report data (residential and mailing address Data now) with Current CRM data, then user prompt to select the correct record for CRM through message box which by default will be Report data not CRM Download Data. After a dialog box is selection is made for the unique Household address selection by the used, then the data can be filled in the CRM upload sheet.
ID HH Table
This is the unique identifier in the CRM upload sheet in yellow, linking the household address. Ignore blank HH Data rows.
CRM Upload Sheet
Data fields should be color coded to match each other.
There is a chance a new record will need to be added to the bottom.
Please prompt the user to add the new record or leave off the report
Update HH_Monthly_Rev, LPL_Household_Name, Last_Review
Other notes
Please error checks things I haven’t considered as far as duplicates or other errors that will goof up my data.
Try to make decision dialogue boxes user-friendly, functional and fast.
Print a data file with all record updates that can be reviewed after the fact if needed
Report 1:
This is a downloaded system report that links a Client SSN to a household. There can be multiple client SSN in a household. (Spouse, Kids Biz. Trust)
If the household text is empty please alert an error box, So I can update the household name in the System and redownload this report to run again.
This report shows duplication of records because it would include all account level data which is not needed. all accounts
Report 2:
SSN Field Can contain duplicates from outside system feed accounts, however we need to make SS/Tax id unique in this report.
Error test System Report 2 against duplicate SSN in column. Probably a 20 or less is duplicated of 1000.
Show mailing address differences in a pop up selection box allowing me to choose the correct Residential, and Mailing Address, then save the report with a unique SSN, with the correctly selected address from the user. This data will be used later.
Report 3:
Household should be Unique, but if not Check for duplicates of Household, then show the values in a dialog box, and prompt user to add the values together, choose one, or manually type in a value.
Then update CRM Upload sheet with the correct data. Its color coded.
CRM Download:
This report is downloaded from my customer relationship manager I would like to compare Report data (residential and mailing address Data now) with Current CRM data, then user prompt to select the correct record for CRM through message box which by default will be Report data not CRM Download Data. After a dialog box is selection is made for the unique Household address selection by the used, then the data can be filled in the CRM upload sheet.
ID HH Table
This is the unique identifier in the CRM upload sheet in yellow, linking the household address. Ignore blank HH Data rows.
CRM Upload Sheet
Data fields should be color coded to match each other.
There is a chance a new record will need to be added to the bottom.
Please prompt the user to add the new record or leave off the report
Update HH_Monthly_Rev, LPL_Household_Name, Last_Review
Other notes
Please error checks things I haven’t considered as far as duplicates or other errors that will goof up my data.
Try to make decision dialogue boxes user-friendly, functional and fast.
Print a data file with all record updates that can be reviewed after the fact if needed