Archive for the ‘Uncategorized’ Category

Announcing New Faces at Apsona!

Wednesday, May 19th, 2021

Apsona for Salesforce is continuing to grow and build its tool set for Data Management, Reporting, Deduplication and Document Merge solutions. As our company grows its support of business and nonprofits globally we wanted to share several new updates from our team:

We are excited to announce that Justin Barss has joined our team this month to support customers, partners, and the wider community as Apsona’s Chief Engagement Officer. In this role, Justin will drive customer success through strategic planning, partner engagement, and driving awareness of Apsona’s key feature solutions. Justin has a ten year history in the Salesforce ecosystem, having previously worked as an Admin, Developer, Consultant, and Advisor. He’s also a 2x past Salesforce MVP and a tremendous addition to the team.
You’re able to reach him at Justin@Apsona.com.

On the development front, Manoj Mardithaya has joined as our new Head of Development Team to ensure that we continue to push the capabilities of Apsona and develop new features for our customers. Manoj is also a founder and indie game developer at HIGHKEY Games, and has ten years of technical experience including working on improving the diversity of Google’s search index and on the Microsoft Windows storage team. Manoj is available at Manoj@Apsona.com.

As our team continues to expand, we have enlisted several Apsona Support Team members to respond and provide support to customers. You may email “Support@Apsona.com” for all User Support, Feature Requests and further details regarding Apsona’s tools.

With Warm Wishes and Excitement for the future,

Sadna Dutt, Co-Founder

Product Updates: https://apsona.com/pages/sfdc/recent-changes.html
Webinar Recordings: https://www.youtube.com/user/apsona4sfdc/videos
Apsona Demo Tutorials: https://apsona.com/pages/sfdc/demos.html

Take a Scrub Brush to Your Data!

Monday, February 25th, 2019

There is nothing like dirty data to undermine everyone’s confidence in your Salesforce database. Telltale signs:

  • – You find the fundraising and development people with their own individual spreadsheets, hoarding their data like 10-year-olds after the annual Hallowe’en night of Trick or Treating.
  • – Your CEO asks for a specific report and it takes more than an hour for the department to produce it.
  • – The monthly board meeting is coming up and everyone takes the afternoon off to get the reports generated. Even worse, nobody on the board actually READS the reports.

Running an organization without complete and accurate data is like driving through a blinding snowstorm or a downpour of rain. Nobody can truly see where the business is going!

Missing information is the most likely culprit, 78% of nonprofit staff members report. Next, 67% are plagued with general typos, misspellings, incorrect IDs, and wrong dates. And, it’s often difficult to see who’s making the mistakes, so no one is learning from their mistakes.
You may be completely familiar with creating reports to show your positive results. Did we serve more homeless people this year than we did last year? How much did the percentage of women served go up? How much did the annual gala bring in and who were our major donors?
Tackle the dirty data with NEGATIVE reports. Look for the mistakes, missing info, and bad dates and you can figure out how to fix the underlying problem. Look for reports by types of errors—then you can more easily clean them up.
Judy, our executive director, wants to send a Happy Birthday to all the students in our database. She knows that keeping in touch with engaged individuals is what will make our job a success. She wants to see the folks getting birthday greetings next month.
Just eyeballing the data convinces us we have a dirty data issue. Let’s take a scrub brush to the birthday data. Both missing dates and clearly inaccurate dates are the black spots.

Missing or inaccurate data

We set up an Apsona report that looks like this:

  1. We add the Salesforce Contact ID to take us directly back to the Contact record if we need to check up on something.
  2. With Apsona, we can easier see both the birthdate (and quickly find those that are missing) AND just the year, to check for those that are clearly too old to represent clients.
  3. We also have the record owner (usually the person who created the record) and the name of the user who last modified the record.
  4. Note that we can change the filters from the report screen. Find all the records missing data, find all the dates outside of the plausible range. In our case we want to question anybody over 65 or under 18.

Notice all the choices we get when filtering on a date field!

Here are the filters to catch ALL the birthdate errors.

We will probably want to split these to have a bad date report and a no date report. But let’s see what we’ve got first. There are 103 date errors ranging from folks born in 2788 to a several who are supposedly in their 90s. Worse, though, are the empty date fields—2266 of them.
The next task will be to find out which of the Organization staffers need some extra training about adding these birthdates. We created a Visualization on the completed report that Groups by Owner First Name, and shows the number of records in the group. No need to set up a formula to get the record count.

Let’s find out which staff members are entering improbable dates. That is perhaps a different training. First I’ll clone our Birthday Report. In the report list, click the report, then click “Clone.” Then I rename the cloned report to something meaningful.

This time I’ve set the filters to show only the records outside of our normal range of students, and I don’t want to see the empty ones.

The same five users with the highest blank birthdates also have the highest date mistake count. We’ll export these reports and send them to the staff, to correct the inaccurate dates and to reach out to the students to get the missing birthdates.
Set up reports on the accuracy and completeness of every field on every record where the data is critical to your mission.

Duplicates in the System

The best way to deal with duplicates, is not to import them in the first place. “Hit that lookup button to SEARCH FIRST” should be the watchword of every staffer. But staffers do get busy and the occasional dupe makes it in. Apsona will reject importing or updating records if there are duplicates in the system.
Create a dupe-catching report: Add the fields that you think will distinguish one contact (account, opportunity, etc.) from another. For Contact, Full Name (which is a blessing, don’t you think? Not having to put FirstName and a separate LastName), Contact ID, Mailing City, Created Date and Owner. I’m just using First Names there to protect the innocent!
Using Excel—the details of this part are outside the scope of this blog entry—you can find and highlight all the dupes, find the owners who put the records into the system, and provide them with that spreadsheet to merge records, if necessary.

Set up a System to Deal with Data Quality Issues

  1. Identify the data quality issue.
  2. Is training the solution? Do you need staff-wide training or individual training?
  3. Is the database set up to be user friendly? Is staff entering data points only once? Can you automate some entry so staff has fewer fields to fill out?

Monitor Data Quality regularly with your Apsona reports. Don’t wait to start cleaning your data. Some fancy brushwork now can save some tedious scrubbing on your hands-and-knees further down the road!
Soon you’ll be seeing the benefits – saving time correcting errors, increasing overall accuracy, and improving the core confidence all the staffers, from the executive director right down to the data entry intern, have in your Salesforce system. Thanks, Apsona!

Thanks to TaroWorks.org for the basic principles expressed here. Their 2015 webinar on data quality inspired me to ask what Apsona could do to help.

Apsona Multi-Step Reporting: Learn the Basics

Tuesday, September 19th, 2017

This is the first in a series of posts about Multi-Step Reporting

Introduction

The sky’s the limit with Apsona’s multi-step reporting (MSR) tool for what kind of reports you can generate. Need a report that spans multiple objects, including sibling objects as well as parent/child? No problem, MSR can do it. What about applying a different filter to each object? Yes, got that covered. Can I output calculated fields? Check, and check. Are you excited yet? You should be! However–if you’ve never worked with MSR before, it can be intimidating to start. In the MSR interface you’ll see “steps”, filters, “linkages”, and more. How do you know which objects to pick, how to configure steps, and what to do with linkages? Description: confusion 2.jpg Don’t worry, we’re going to break it all down. In this first in a series of posts about MSR, you’ll learn:

  • a strategy for designing your report
  • what steps are, and how to configure them
  • how to link your steps, and what that means
  • how to filter results to get only the records you want

Let’s work through each of these points using a typical, fairly complex reporting scenario. Imagine your boss has asked for this: I want to see the top three opportunities for all of our accounts, along with the name and email of the contact in the role of business user for each of those opportunities.

Design Your Report

A little upfront planning will save you time (and frustration) in the long run. (As an aside, this is true regardless of the reporting tool you’re using.) Consider these questions:

  • What are you reporting on?
  • What do you want the report output to be?
  • Which records and how many do you want?

Let’s look at our report requirements again: I want to see the top three opportunities for all of our accounts, along with the name and email of the contact in the role of business user for each of those opportunities.

  1. What are you reporting on?This question gets at the objects you’ll need for your report.(Don’t worry about the fields yet–that comes in the next step.) In our example, it would be: Opportunities, Accounts, Contacts. At this point, it can be helpful to do a quick sketch of these objects so you can see how they’re related: Description: IMG_0009.JPG Even though we don’t need any data from the Opportunity Contact Role object, we include it in our sketch because it’s the link between the Opportunity, and the Contact for that Opportunity,
  2. What do you want the report output to be?What fields do you want to see from which objects? In what order? Another quick sketch comes in handy: Description: rpt_output_sketch.JPG
  3. And finally, which records and how many do you want? In other words, are there any restrictions on the records you want returned?

From our report example:

  • Accounts: all of them, no restrictions
  • Opportunities: the top three opportunities
  • Contacts: only business users for the opportunities

Fleshing Out Requirements You might wonder– what does “top three” mean? And can the opportunities be in any stage, or just the closed/won opportunities? And how do I identify business users? Great questions! This is when you’d go back to your boss and ask for clarification. This is also a good chance to review your sketch with her and make sure you’re on the same page in terms of the report output. Let’s say you had that meeting (where your boss was very impressed with your sketches and well-thought out questions.) Now you know that “top three” means the opportunities with the highest value in the amount field, regardless of stage. But speaking of stage, your boss realized she’d also like to see that information in the report output. Oh, and the opportunity amount. You also found out that “business user” means someone with the opportunity contact role of “business user.” Here’s our revised sketch: Description: revised_sketch.JPG

What Are Steps and How Do I Configure Them?

Ok, now that we know where we’re headed, let’s start building!

  • Go to the Multi-step Reports tab and click New:Description: msr_tab.jpg
  • Give your report a name and description, and select a folder to store it in. Then click the Add Step link on the left:Description: start_new_report.jpg The Step interface opens up: Description: new_step_blank.jpg

A “step” is the big cheese of Multi-step reports. Think of a step as a container that holds all the information about one of the objects you want in your report. The step holds information about:

  • which object you want to report on (Step Info tab)
  • which fields you want from that object (Retrieved fields tab)
  • how you want to limit the report results (Filter terms tab)
  • how many records, and what sort order (Range and sort tab)
  • how this step (or object) is related to another step in the report (Linkages tab)

Remember–one step relates to one object. Let’s revisit the sketch we made when designing our report: Description: schema_sketch_steps.jpg As we can see from the sketch, our report spans four objects. So we’ll plan to configure four steps. Let’s get started!

Configure the First Step

In general, you start configuring steps with the top-level object, which in our case, is the Account.

  • On the Step Info tab, add a Step Name, and choose Account as the Database Object to get data from.

Description: step_info.jpg

  • Next click on the Retrieved fields tab and choose Account Name from the Add Field picklist.Description: retrieve_acct_name.jpg
  • Add the Account ID field. (No, we don’t need this field in the report output, but we’ll need it for something else a little later. For now, take it on faith, and hang tight.)

Your Retrieved fields tab looks like this: Description: retrieved_fields_acct.jpg

  • Click the Filter terms tab.

Description: filter_terms_tab_blank.jpg This is where we can restrict which records are returned. Since we decided we want all Accounts with no restrictions, we’ll move on to the next tab.

  • Click the Range and sort tab. Description: range_sort_tab.jpgThis page allows you to choose one of three ways to limit the number of records returned:
    • Limit by number range (1)
    • Limit to one record based on the criteria you pick (2)
    • Limit to the top x number of records (3)

    Again, the report requirements didn’t specify how many Account records or in what order, so we’ll move on.

  • Finally, click the Linkages tab.Description: linkages_tab.jpg Linkages are about relating steps to each other, Since we only have one step configured so far, there’s nothing to link yet.
  • Click Save.Description: click_save.jpg

Congratulations! You’ve configured your first step!

Create the Opportunity Step

Now let’s quickly create a step to get our Opportunity data:

  • Click Add Step.
  • Give the step a name, such as Top 3 Opportunities, and choose Opportunities as the database object.
  • On the Retrieved fields tab:
  • Select the Opportunity Name, Amount, and Stage fields.
  • Also add the Account field, and the Opportunity ID field. (Yes, these are more fields we don’t need in the report output … the mystery will be revealed soon!)
  • Now click the Range and sort tab. Remember, we want only the top three Opportunities (determined by Amount.)
  • Select the bottom option: The first … records when ranked by …
  • Enter 3 for the number, Amount for the field, and descending for sort order:

Description: range_and_sort_top_3_opps.jpg

  • Click the Linkages tab.

This is where we connect our two steps–Accounts and Opportunities. Remember when we added those fields on the Retrieved fields tab that we didn’t need in the report output (Account ID in the first step, Account in this step)? Well … (drum roll please) … this is why: On the Retrieved fields tab of each step, include lookup or picklist fields that relate to objects on another step. These are used on the Linkages tab to create connections between steps. In Salesforce, Opportunities and Accounts have a relationship: the Opportunity’s Account lookup field holds the related Account’s Account ID. By adding the Account field in this Opportunity step, and the Account ID field in the previous Account step, we created a way to link the two steps. (We also included the Opportunity ID field in this step. This will come in handy when we link this step to the next one in the chain.)

  • Choose the first option: Accounts Account ID matches Account.

Description: link_step1_and_2.jpg NOTE: If you forgot (or didn’t realize) you needed to include these linking fields, you would see this message when you clicked on the Linkages tab: Description: no_available_linkages.jpg If that happens, no worries — you can always edit any step to add more fields on the Retrieved fields tab.

Create the Last Two Steps

Follow the procedures above to create the last two steps for Opportunity Contact Role, and for Contact. For the Opportunity Contact Role step:

  • In the Retrieved fields tab, add the Opportunity field (to relate to the Opportunity step), and Contact field (to relate to the Contact step.)
  • On the Filter terms tab, add a filter to select Contact Role records where the Role is Business User. The Filter terms tab will look like this:

Description: filter_terms.jpg

  • Remember to save each step.

Run the Report

Now it’s time to see how all of your hard work has paid off!

  • Click Save and Run to run your report: Description: report_results1.jpgLooks great! But we can make it look even better. Those ID fields are there because we needed them to link steps, but we don’t have to include them in the report output.
  • Simply click Set Columns, and uncheck any fields you don’t want to see: Description: set_columns.jpg

Our final report looks like this: Description: report_results2.jpg

Points to Remember

  • Design your report before you start building, to understand what objects are needed and how they’re related–and save yourself time in the long run.
  • Configure one step for each object. (There is a shortcut for this … stay tuned for details in further posts!)
  • Include lookup and ID fields in the Retrieved fields tab so you can link steps.

Do you have MSR questions? Have a tip or trick to share? Let us know!

Using Logic and Conditionals When Generating Documents

Sunday, July 30th, 2017

Sending a well crafted thank you letter to your donors is an essential part of your non-profit’s fundraising efforts. Acknowledging a donor in a timely manner increases giving and also improves donor retention. And when you need to send large numbers of such letters, you would want to automate the process so that members of your staff can send them off with a couple of clicks. This is when you need Apsona’s Document Generator to set up the process seamlessly.

A use case – Consider a scenario where you need to send your donors a thank you letter after a successful campaign. In this case, the content or the body of the letter is the same for all donors, but the signatures on the letters are determined by the donation amount. Donors who have donated $500 or more get a thank-you letter signed by the President of the organization. Those who have donated between $250 and $500 get letters signed by the Executive Director, and donors who have given under $250 get a letter signed by the Philanthropy Officer.

Setup – Setting up the merge with Apsona’s Document generator is a four-step process: Create a template, creating the data sources for the template, map your data, and generate documents. As the body of the letter is the same for all the donors, the template will only need to have the required merge fields or place-holders for the relevant data to be filled in. With Apsona’s support for conditional directives (i.e., if-then-else), you can selectively include or exclude content in your document based on data conditions. In the use case we are looking at, it is the different signatures on the letters determined by the donation amount.

template1

In the above template, we have just four merge fields as place-holders: First name (Donor name), Donation amount, date the payment was received and the Household name. The data for the merge fields are available in the Contact object, and the data for the merge fields in the body are available in the Opportunity object, and the two are linked via the Primary Contact lookup in the Opportunity object. So you can create a simple Opportunity report that extracts all the necessary data fields to generate this document.

The signature area contains conditional directives to drive the logic outlined above:

«IF Opportunity_Amount>=500»John Doe
President«ELSE IF Opportunity_Amount>=250»Jane Doe
Executive Director«ELSE IF Opportunity_Amount>=100»Jill Doe
Philanthropic Officer
«ELSE»John Doe
President «ENDIF»

In the above example, we use four directives, IF, ELSE IF, ELSE and ENDIF, to provide the logic. Each directive is created as a standard Word merge field. The code in the above example looks for the template field Opportunity_Amount. The text of the signature is interspersed with the directives so that the required logic is implemented.

It is important to ensure that each IF directive must be followed at some later point by a corresponding ENDIF directive so that the text segments that produce the signatures are clearly differentiated from the main body of the document. The document generator checks for this condition, and if a violation is detected, it might produce an error message like the one below.

IF

The example here shows the If – Then – Else conditionals in the signature are of the letter but you can get really creative and use it from the address and body of the letter as well. Detailed documentation on the If -then- Else conditionals is available on the Apsona website. Here is the link.

Save Time and Clicks with Apsona Grids

Wednesday, May 17th, 2017

And make time for what really matters

Imagine this: it’s 4 pm and you’re wrapping up work to get home in time for your daughter’s t-ball game. Suddenly, in walks your supervisor … who informs you she has just discovered massive data entry errors in a large batch of Opportunities. It’s critical, she tells you, to get them all corrected before the end of the day.

The Opportunities to be updated include both donations and grants — which are different Opportunity record types. And not only do you have to update several fields on each Opportunity, you also need to create missing related Contact Role records.

If you are using standard Salesforce functionality, you might feel like this:

 

 

Thank goodness you have Apsona! You know that with Apsona Grids, you can easily and quickly accomplish your supervisor’s request.

One of the biggest time savers with Apsona Grids is that filtering records, editing, reordering of fields, and even creating new records can all be done right on the same page. And, you can even perform all of these tasks for related child and parent records in the same view!

Here’s a snapshot of how Apsona Grid functionality stacks up against standard Salesforce listviews:

Task Apsona Grids Salesforce listviews
Edit a list of records of different record types green check.png x mark.png
Add new records without leaving the list page green check.png x mark.png
View, update, and create related child and parent records from the list page green check.png x mark.png
Drag and drop to reorder columns without leaving the list page green check.png x mark.png
Create groups and subtotals from the list page green check.png x mark.png
View fields from related records in the list green check.png x mark.png
See a display of the exact record count in the list green check.png x mark.png

 

Let’s take a quick tour to see how you can easily accomplish your supervisor’s request using Apsona Grids.

Inline Editing

You know you need to update both grant and donation records. Fortunately, unlike Salesforce listviews, Apsona Grids provides an editable list of records of any record type.

From the Apsona Opportunities tab, choose Tabular View

tabular view.png

A list of all Opportunities appears. Simply click in any field to edit it.

Inline Editing.png

 

Quick and Easy Filtering

If you’d like to limit the list of Opportunities, say to Opportunities where the amount is over $500, use the handy Filter option — again, without leaving the Grid page.

filter and results annotated.png

  • Click the Filter icon (1)
  • Enter your filter criteria (2)
  • Click Search (3)
  • View your filtered results — right on the same page (4)

Need to apply a different filter? Simply update the filter criteria on the same page and click Search again.

Drag and Drop Column Reordering

With Apsona Grids it’s easy to change the column order. Simply drag and drop to place the columns in the order that’s most useful to you.

 

Rapid Data Entry

Not only can you easily update existing records, you can add new records on the fly — again without leaving the Grid.

 

add row 3.png

 

  • Click Add Row. (1)
  • Fill in the values for the new Opportunity name, amount, and any other data in the editable row that appears in the Grid. (2)
  • Click Save. (3)
  • Repeat for as many records as you need.

What a time-saver!

 

Edit and Create Related Records

Another huge time-saver with Apsona Grids is the ability to drill down to related records, and update or create new child records. Let’s see how you can add those missing Opportunity Contact Roles without navigating away from the Grid.

 

Click the arrow next to an Opportunity name.

 

Click to expand.png

 

 

In the expanded view, you see tabs for each of the related records; in this case, Contact Roles, Campaign, Organization, Tasks, and Events.

 

related records.png

 

Since you want to add a new Contact Role, click Add on the Contact Roles tab, complete the required fields, and click Save:

 

Add OCR.png

 

The newly created Contact Role appears.

 

newly created OCR.png

 

That’s it! Apsona Grids allow you to update all those Opportunity records and create related Contact Roles in a fraction of the time it would take with standard Salesforce listviews.

 

Now you can leave work on time — and be there for the things in your life that really matter.

 

IMG_2640.JPG

The Power of Apsona Single-Step Reporting

Friday, March 31st, 2017

Or, no more banging your head against the wall over standard Salesforce reports

Reports reports reports. One of the critical functions of your Salesforce database is to generate reports that will give you accurate, useful information about your constituents, products, and services.

Standard Salesforce reporting can get you only so far. Sometimes, you need a more complex analysis of your data. For example, take this real life scenario recently posed by a non profit organization:

I am trying to report on how many contacts 65 years of age and older live in households that have received services in the last year.

Example: Jim is 70 years old. His wife Franny is 72. They live with their daughter Sara, who is 50 years old and who received food services last year.

In Salesforce it looks like this:

  • Household Account with three related Contacts: Sara, Jim, and Franny
  • Case record: connected to Sara’s Contact record, and to the Household Account record.

How do I get the report to count Jim and Franny when they are not in the Case loop, so to speak?

Report Scenario.png

First Attempt with Standard Salesforce Reporting

Hmmm, could we do this with a standard Salesforce report? What if we create a report on Account, with cross filters to find Accounts with Cases, and Accounts with Contacts over 65?

Standard Acct report with x filters.png

Standard Salesforce report on Account with cross filters

When we run this report, we want to see a result of 2, since there are 2 contacts over 65 in a household with services.

Sadly, that’s not what we get. Our standard Salesforce report is counting the number of Accounts that meet our criteria, not the number of Contacts:

Standard SF report - results - not quite what we want.png

Standard Salesforce report results — gives us count of Accounts, not count of Contacts.

Darn it. What can we do?

Apsona Single-Step Reporting to the Rescue!

Fortunately, there’s Apsona. Apsona’s incredibly powerful reporting tool will give us exactly the results we want, and then some. Follow along to see how!

  • From the Apsona tab, click on Accounts, and choose Reports.

Account Reports.png

  • Click + New to create a new report

New Report.png

Part 1: Choose Your Columns

Right off the bat with Apsona reporting, we have the ability to choose fields not just from our base Account object, but from any object that has any kind of relationship to Account — without having to create a special report type. All of these related objects are provided right there for you. Think about it — this includes any objects that have a lookup or Master/Detail relationship to Account, as well as any objects that the Account has a lookup to. Powerful!

Apsona report available fields.png

Fantastic, but how do we get the count of Contacts we’re looking for?

First, we’ll need to choose the correct Contact object:

  • In the Available Objects list, choose Contact via Account. The help text shows that this is any Contact record that’s related to the Account via its Account lookup field. In other words, any Contact in the Household Account — exactly what we want.

Available Object - Contact via Account.png

Next, we choose the fields we want. Here’s where the handy “Metrics” feature of Apsona comes into play:

  • Expand the “Metrics” column at the bottom of the “Contact via Account fields” list, and choose # Records

Choose Record Count field.png

That is the magic that will make this report work: displaying the # Records field, and choosing the Contact via Account related object. Of course, we still need to add report filters to make sure we’re only getting Contacts over 65, and only those in Households that have Cases. But how great is it that we can so easily pull metrics such as record count from related objects?

As long as we’re here, why not display the Contacts’ names and ages?

  • Click in the Add column picklist to bring up the Add Column dialog.
  • In Available Objects, choose Contact via Account
  • In Contact via Account Fields, choose First Name
  • Repeat the above steps for Last Name, and Age. (NOTE: , “Age” is a custom formula field that we created on Contact.)

Now our screen looks like this:

Report fields.png

As an aside, another lovely feature of Apsona reporting is the ability to change report column names. Simply hover over the column whose name you wish to change, click on the arrow, and type in the new column name of your choice.

Change Column Label.png

Part 2: Add Filters

Now that we’ve chosen the fields to display, it’s time to filter the report. Again, we only want to see Contacts over 65 that belong to Accounts where someone has received services. Let’s start with the age filter on Contact:

  • On the Filter Terms tab, click the Add Term link.

Click Add Term link.png

  • In the Available Objects list, choose Contact via Account, and then choose Age in the Contact via Account Fields list. (Again, “Age” is a custom formula field that you can create on Contact.)

First Filter Term.png

  • Complete the filter term by adding the appropriate criteria:

Filter Term for Contacts.png

Next we want to make sure we only see Contacts in Household Accounts where at least one Contact has received services, In Salesforce terms, where there is at least one Case associated with the Household Account.

This filter is a little different from the Contact filter, since we’re not filtering on a particular field on the Case, but on whether or not there are any Case records. Apsona easily handles this scenario with Metrics:

  • Click the Add Term link again, and this time, choose Case in the Available Objects list.
  • In the Case Fields list, at the bottom, click the Metrics header

Case Metrics.png

The Metrics options open up.
Metrics options.png

  • Choose # Records, and finish the filter criteria. Since we want both conditions to be true, we’ll leave the Filter logic as is.

Complete filter criteria.png

Part 3: Run the Report!

We’ve chosen the fields to display, and set our filter terms. Let’s test it out!

  • Choose Save and Run to see your report results

Apsona report results.png

Voila! Our Apsona report gives us the results we were aiming for — number of Contacts in households that received services. And, as you can see, you can easily add other fields related to Contacts to verify your data and make your report even more useful.

So stop banging your head against the wall over standard Salesforce reports — and start making use of the power of Apsona reports today!

Take the Headache out of Data Import With Apsona

Tuesday, January 31st, 2017

Picture this: your annual auction has been a huge success. Congratulations! You have a spreadsheet chock full of donors with all their contact information, along with the items they won and additional donations they made. Some of these donors are new to you; some have been loyal fans of your organization for years. Now it’s time to get all that mission-critical data into Salesforce, so you can be sure to thank everyone and keep in touch with them for next year’s auction and other events.

The idea of data import can be daunting. Especially when you know you have duplicate rows of Contacts in your spreadsheet because of generous donors who won more than one item and donated during the Raise the Paddle session. And, some of your donors are already in your database, while some are new to you.

How can you be sure you import only one record for each unique Contact in your spreadsheet, and avoid importing Contact records for people who are already in Salesforce? And how can you match the newly imported or existing Contact records in Salesforce with their donations in your spreadsheet?

Enter Apsona’s Data Import tool. No more manual sorting and filtering — Apsona does all the heavy lifting for you, with some handy features that you’ll want to take advantage of right away. We’ll highlight three of them here:

  • Seamless handling of duplicate records. During the import process, Apsona detects duplicate rows in your spreadsheet, based on matching criteria you provide. Not only that, it checks your spreadsheet against existing records in the database to avoid creating new duplicates.
  • The Echo File: associates new record IDs with each relevant row in the spreadsheet. This means if you have three rows in your spreadsheet with David Smith as the Contact, Apsona will import just one David Smith Contact record — and then return an “echo file” with the new Contact ID filled in for each of the three David Smith rows. This makes import of related data, such as David Smith’s donations, a breeze.
  • Add columns on the fly during import. Have you ever started an import, and then realized part way through the process that you were missing a crucial field? And had to back out and start all over again? With Apsona, you can add missing columns on the fly without having to leave the import screen.

Let’s see how all of this works!

Seamless handling of duplicate records

We’ll begin by importing our donors as Contact records. As we recall, we have multiple rows in our spreadsheet with the same donor.

On the Apsona tab, click on Contacts. From the Tools menu on the right, choose Import/Update.

Apsona Take The Headache Out of Data Import_html_6be4971b9c0a7fd6

On this first import screen we’ll choose “Reject” as the option for how to handle Duplicates. This is what tells Apsona that if there are multiple rows in our spreadsheet with the same Contact, it should import just one of them. Not only that, Apsona is sophisticated enough to determine if you already have a Contact record for that donor — and not import another one. What a time-saver!

But what determines if it’s the same Contact? You can tell Apsona what your matching criteria is with the Match Fields picklist. In our Salesforce org, we uniquely identify Contacts with a combination of First Name, Last Name, and Email, so that’s what we’ll choose here:

Apsona Take The Headache Out of Data Import_html_56a664717bd4ed42

Data Import Step 1: choosing options in the Duplicates and Match fields picklists

Once we’ve chosen our spreadsheet as the Data Source, we can click Next and move on to data mapping.

Apsona will automatically detect if the column names in our spreadsheet match the Salesforce field names, and map them accordingly. You can change the mappings, of course, to import into whatever fields you like.

Since First Name, Last Name, and Email are set correctly here, we’ll leave those alone. The Donation related fields — Donation Amount and Type — are also set correctly to Do Not Import. These fields will be handled when we import the donation data in a subsequent step.

imp

Data Import Step 2: Field mapping for Contact Data

If we leave the default option of Stop and Show Errors in the On data errors picklist, this will validate our data and let us know if we have any issues:

Apsona Take The Headache Out of Data Import_html_26e36a065f894ee7

From the Data Errors dialogue, we see that Apsona, as promised, recognizes duplicate rows in our spreadsheet. Not only that, it has also determined that we already have an existing Contact record for one of our donors. Excellent!

Now that we’ve validated our data, we can choose Omit Erroneous Records in the On data errors picklist and click Next to get to the final step of Contact import.

Apsona Take The Headache Out of Data Import_html_ea285a8a5b6fe774

Choose “Omit erroneous records” for the data error handling option

The Echo File: associates new record IDs with each relevant row in the spreadsheet.

After clicking the Import button on the final import screen, we have the option to download the import results:

 

Apsona Take The Headache Out of Data Import_html_d82982d0858e5c7e

Always click the Download button here to get your import results

Always download the import results! This is your “Echo File,” which contains the Contact IDs that will make importing related Donation records a breeze.

Apsona Take The Headache Out of Data Import_html_fc93284895347962

Echo File results

Notice that two columns have been added: Import Result, and Contact ID. And notice that the Contact ID for John Smith has been included for each John Smith row — even though the second John Smith record was not inserted. We also have the Contact ID for Barbara Wells, our existing Contact. And finally, even though we didn’t insert any Donation data on this round, the Donation columns are included in the Echo File.

Since we have all of our Contact IDs included in this handy Echo File along with all of our data, associating Donation records with the right Contact will be a snap. Just use the Echo File as the Data Source to import the Donations.

Apsona Take The Headache Out of Data Import_html_3986d3786400af0Donation import: use the Contact ID provided in the Echo File to associate the Contact with the Donation.

Add columns on the fly during import

Now that all of our donation fields are mapped, we’re ready to import. But, when we click the Next button, we see this error screen:

Apsona Take The Headache Out of Data Import_html_d417939245667017

Oh dear, do we have to go back and edit the Echo File to add these columns and start all over? Never fear, Apsona is here! From the Actions menu, choose Add column:

Apsona Take The Headache Out of Data Import_html_88f3c22cb92aeccf

For each missing column, choose the field name and enter a value.

Apsona Take The Headache Out of Data Import_html_c560e98744e02562

A new column will be created on the fly, and the value you specify will be filled in for every row in the spreadsheet:

Apsona Take The Headache Out of Data Import_html_9553dd2e487ec8f6

New “stage” field has been added with “Closed Won” as the value for each row

But wait, what about the Opportunity Name field? We don’t want the same value for every row for this field. Apsona has taken care of this too with another handy feature, which allows you to reference the values of other fields in the new field value.

Our organization’s Opportunity naming convention is Donor First Name, Donor Last Name, Amount, and Record Type. For example, the Donation record for the first row would be named John Smith 100 Auction Item.

Simply check the Replace column name references checkbox in the Add Column dialogue, and choose the column names you want to use:

 

Apsona Take The Headache Out of Data Import_html_f1e036f3047c5285

Use column name references for the Opportunity Name

Voila! Apsona creates the Opportunity Name column, with the correct values filled in.

Apsona Take The Headache Out of Data Import_html_910a7a2e45358847

As an added bonus, if you are using the Non Profit Success Pack, Opportunity Contact Role records will automatically be created when you import the new Opportunity records. The Primary Contact will be set to the Contact you designated, with the default role of Donor, and the Primary checkbox will be checked.

As you can see, Apsona’s smart Data Import tool make it easy to import spreadsheets of related data, such as donors and their donation records — even when some of the data is duplicated. And with the ability to add columns to the import on the fly without having to edit the original data file, the import is even easier.

Apsona Charts and dashboards

Friday, July 10th, 2015

Segment, slice and dice your data at will with Apsona’s Charting and dashboard add-on. This new add-on is the latest entrant to the Apsona suite of products. The question we were frequently asked was, “As Apsona reporting is so powerful, why can we not see the report data as charts and in dashboards?” Now, with Apsona’s Charting and dashboards, you can visualize and analyze your data in real time, right within your Salesforce org in your browser – there is no exchange of data with an external data warehouse.

Apsona charting supports several types of charts – horizontal and vertical bar charts, pie and line charts, KPI’s and Pivot tables. More chart types are on the way. You can create powerful charts with it – charts that cannot be constructed with native Salesforce. One example that comes to mind is a chart with multiple “layers”.  A multi-layer chart is one that contains two or more charts overlaid on each other.  For example, you can create a two-layer chart with one layer containing Campaign and expenditure information, overlaid with a second layer containing Campaign and Opportunity revenue information.  This way, you can get a side-by-side view of two related metrics (expenditure and revenue) in the same chart – even if those metrics are not available from the same report or object. You can also apply filter conditions to each layer if required. Layers can be added to vertical and horizontal bar charts.

Another powerful visualization available is the pivot table. With this device, you can use multiple dimensions in the rows and columns and metrics in the cells. You can also use multiple metrics in the cells, and essentially get all of the benefits of an Excel pivot table right in your browser.

Most organizations set goals and use metrics to measure performance towards those goals. This is where dashboards come in handy. You can now present the storyboard of your data as an Apsona dashboard. An Apsona dashboard is basically a collection of Apsona charts. It can include a combination of charts, and can easily be edited and previewed, and its charts resized and repositioned. You can add an Apsona dashboard to a Salesforce Home page and also as a related list in a Salesforce object.

demodashboard

To try out Apsona Charting and dashboards, please click the Feedback link in Apsona for Salesforce and send us a request. We look forward to feedback and suggestions on this add-on.

 

 

Fundraising Reporting with Apsona Multi-step Reporting

Friday, February 27th, 2015

Constituent reporting on individual donors and their giving histories is invaluable information for a non-profit organization. One such report requirement was posted on the Power Of Us Hub where the user wanted to find the increase or decrease in donation amounts by their donors. Here is the question:

  • My fundraising team wants to know how many donors increased their donation size from the last donation to the current one. I told them I could do Total Gifts This Year vs Total Gifts Last Year, but not Current Gift vs Previous Gift. Can you all think of a way to measure an increase (or decrease) in donation amount from one to the next?

Reading the responses it became obvious that this report is not possible to create with the native Salesforce reporting. However, with Apsona Multi-step Reporting, this report can be built in a few minutes. This report will require two query steps and one calculated step. Let’s take a look at how to build out such a report.

For the query steps, you first get the donors and filter them by campaign name or opportunity close date depending on the required criteria. Next, retrieve the opportunities to the donors, filter by close date and Role. Multi-step has the ability to get ranked data, which will need to be applied when querying for the opportunities. Limiting the 2 donations in descending order will give you the last two donation amounts to each donor. The steps will be woven together with the contact ID.

As there is no direct link between the Contacts (donors) and Opportunities (Donations), data for the two query steps will come from the Opportunity Contact Role (OCR) object. This object is best suited for this task, since it is treated as a stand-alone object in Apsona, and is also the junction between Donors and Donations. Therefore, you retrieve fields from the Donor (Contacts) as well as Donation objects (Opportunities) as they are both have lookup relationships from the OCR. Thus, you can retrieve the contact ID in both the steps and weave them together. Unlike in Salesforce reporting, you can use the same object more than once in Apsona Multi-step with different filter criteria and is ideal for this use case.

Now that you have the donors and their last two donations, you will need to find the difference between the amounts. For this you use a calculated step and add a formula as follows – {!Last 2 Donations 1.Amount} – {!Last 2 Donations 2.Amount}. Once you save and run the report will see the donor information, the last 2 donations amounts to the donor and the difference between the donation amounts.

When you run the report, you will also want to see the two donation amounts to each donor in a single row to make the comparison.

In a multi-step report, you have the option to display the output of a step as a sequence of blocks of columns instead of the usual row layout. Below is an image of the final report.

2amts

Donor Adam Dunn has donated $250,000 and $ 300, 000. You also see the difference in the Amount column as $50,000. The reason the amount is showing in parentheses is because the formula in the calculated steps is set to subtract Donation 1 from donation 2.

This shows how you can combine two important operations – transposing rows to columns, and using calculated values – to produce the result you want.

We hope you found this blog useful and try out Apsona Multi-step Reporting.

Updating data without VLOOKUP with Apsona

Saturday, January 31st, 2015

Apsona for Salesforce has built-in tools that can highlight data quality issues, which you can find and replace when ever the need arises. Most businesses are aware that bad data quality can be a huge drain on their productivity and profitability. If you have an active Salesforce org you will always need to update data on a regular basis. Phone numbers, email addresses or mailing addresses of contacts and leads might need updating from time to time. Such mass updating cannot be done natively in Salesforce, but can be done with Apsona.

Here is a real use case. After running a mailing campaign, a user obtains a list of mail recipients who have unsubscribed. So she needs to remove the email address from the corresponding a set of contact records and replace the field value to null. She also wants to update the “Email Opt Out” checkbox for all the mail recipients. The recipient list is a CSV file with just the email addresses that have to be removed. Contact ID’s or even a First and Last name are not available.

With Apsona, you can match such a set of data records in a CSV format against your Salesforce data, and obtain the record ID for each record, if one is available. In this case, you use the email addresses CSV file as the data source and take advantage of Apsona’s import tool. You will be able to match the CSV data records against Salesforce data in steps 1 and 2 of the import/update process. In step 3, instead of clicking the Import button, you will need to click the ‘Download match results’ link. The resulting download will contain your data (the email addresses) with an additional first (leftmost) column containing the unique record IDs of the matched records. So we already not have the ID’s with just a few clicks. Half way there already.

Now for the replace part. In the downloaded file add another column called Email (Null values) and leave the cell values blank. Use the import tool again and choose the update action. Use the Contact ID as the look up field in step 1 and map the Email (null values) columns to Email in step 2. Complete the update in step 3 and you are done.

In the example, we saw how to find the unique ID’s and replace the field data values with a few clicks without having to go though the tedious VLOOKUP function in Excel. http://apsona.com/blog/updating-data-without-vlook-up-with-apsona/ http://apsona.com/blog/updating-data-without-vlook-up-with-apsona/ http://apsona.com/blog/updating-data-without-vlook-up-with-apsona/ http://apsona.com/blog/updating-data-without-vlook-up-with-apsona/ http://apsona.com/blog/updating-data-without-vlook-up-with-apsona/

Download Apsona for Salesforce from the AppExchange and try out your use cases. The app had a 30 day free trial.