(Note: this article assumes the reader has a working knowledge of Apsona and Apsona reports).
Running monthly or yearly statements for your households can be a daunting task, from creating a report in your Salesforce instance –to exporting the data to Excel –to merging the data into your statements. And what happens if you have multiple open items or donations for a single constituent or household? It can turn into a nightmare to create these statements, while your staff spends precious hours performing a task that should be automated. Apsona Mail Merge to the rescue!
You may already be using Apsona because of its rich reporting capabilities, the ability to update data easily and in mass, or its multi-object importing capabilities. But have you tried the Apsona Mail Merge? No? What a great time to get started.
Using Apsona Mail Merge, you can create those monthly statements or year-end tax documents for your constituents with just a few mouse clicks. Once you have a merge/mail action set up, you simply run the action and voila – your statements are downloaded and ready to print! This article will show you how to create a monthly statement of open unpaid balances (i.e. yearly membership dues, school registrations, etc). Just follow the simple steps below to get started.
Step #1 Create the Word Merge Document
Creating the merge document is relatively simple. You can use an existing Word merge document or you can create a new one if you want to update it a bit. One thing to note here: while you still need to insert your merge fields, the merge fields can be named anything that makes sense to you. You do not need to spend hours of time trying to find the API field name for the account billing address or other fields to put into your document. The best thing about Apsona Mail Merge is that you actually set up a mapping of the Salesforce fields to your Word merge fields, so you can put in a merge field called «Street Address» to make it simple. Use the terms that make the most sense to you. Check out this example:
Step #2 Upload the Word Document into the Salesforce Documents Object
Apsona Mail Merge uses the standard Salesforce Documents object to pull the mail merge documents from. Make sure the document name is something easily recognizable, especially if you store lots of documents. It might even be helpful to set up a folder in your Documents specifically for Apsona Mail Merge documents to make them easy to access.
Step #3 Create an Account (or Household) Report in Apsona
The first bit of information we need to pull is the name of the household, a greeting, a mailing address, and the total amount the household owes. We are going to create a report from the Account object for this. (Note: if you have a custom object for Households, use that object instead). Make sure to include the following fields when pulling this report: Account Id, Account Name, Account Billing Address, Total Amount Owed.
In this article, we are using an app that has renamed the standard Salesforce Opportunity object to “Invoices”. To get “Total Amount Owed,” go to the “Add Column” menu, find the Invoices object, but instead of clicking the sub-menu titled “Fields”, we are going to scroll down a bit to the “Metrics” menu.
Find the field that holds the amount. In this article, we are going to use the “Amount” field (which is the standard Salesforce Opportunity Amount field). Click on the Amount field to see calculation options appear. Choose “Sum” to get a total of the Amount field. Don’t forget to save the report when you are done!
Next, we need to add in some search terms. We will only want to pull items that are still outstanding, so we will add a filter item of Invoice Stage Is Among “Not Received.” Remember that your stages may be different, so use whatever term your organization has for “Not Paid.” Add in other filter terms as necessary for your process.
Step #4 Create an Opportunity Report in Apsona
This report will show the individual details of the items the constituent still has outstanding. For this example, we will pull a description of the item, the amount of the item and the date the item was added to the Account. No calculations are necessary in this report, so you can pull the fields right from the “Fields” menu in the “Add Column” list. Remember to pull the Account Id from the Opportunity object in this report. We will use the Account Id from each report to merge all of the data together.
Step #5 Create Your Merge Action
Now it’s time to see how all of the pieces fit together. The first time you set up a merge action it can take a bit of time to get it all set up right, but once the merge action is ready and saved you won’t have to go through these steps unless you want to add or change something later on.
1) Go back to Accounts in Apsona. Run the report you created in Step #3 above, then click Merge/Mail
2) Click “Create and Run a New Merge Action” and choose the type of Merge (we are doing “Document” in this article). Click Next.
3) Choose your mail merge template in the “Document Template to Use” menu. Choose an output structure. You can choose to have each statement created as a separate document or a single document. In the “Field to Use as File Name” menu, choose which field you want to use to name your documents (we are choosing “Account Name”).
4) On the Match Field page, Apsona will display all of the merge fields from your document. This is your chance to match up the merge fields from your document to Salesforce fields, with the fields you chose in your Account and Opportunity reports.
5) In the merge document, we created a table that will display the details of open invoices for each account (note the TableStart and TableEnd in the merge document). In the middle section of the Match Field page, we are asked to choose the Data Source for the table; in this case, it’s the Opportunity object.
6) You can specify additional filter terms for your data if wish, but for our merge action, we do not need to define anything else. Simply click the red button in the filter terms area.
7) At the bottom of the Match Fields page, you will find a section called Linkage. We need to link the two data sources together; we are choosing “Account ID matches Invoice Account.” Click Next.
8) You have the option of doing a field update or adding a task to the record as part of your merge action. Fill in the fields, if necessary, for the merge action.
9) Give the merge action a name in the “Save This Action As” field. When you click Finish, you will save the merge action for future use and generate your merge files.
By using Apsona Mail Merge, you can save hours of time each month to create your mail merges.
