Product: Omatic Cloud

Description: This solution describes how to exclude records from being processed by Omatic Cloud

Environment: All

Versions: All

Answer:


There may come a time when your source system is including a subset of records you wish could be filtered out. In most scenarios, this data can be filtered in the data source (i.e. query, report, etc.). In addition, Omatic Cloud has built in settings to handle the most common filtering needs. However, what do you do when your unique data set includes a set of records that you wish could always be excluded? 


Here are steps you can take to intentionally send a record to the exceptions bucket, where you can subsequently delete the record:


  • Step 1: Determine the source field that will identify the records you would like to send to exceptions
  • Step 2: Select a field that has a requirement regarding the type of value it contains. For example, a numerical field requires the value to be a number, not text. Entering text into a number field would trigger an exception. Select a field with a specific type of value requirement that you currently don't need to populate.
  • Step 3: Map the field that will identify the records you would like to send to exceptions to the field that has a required value in the destination
  • Step 4: Set up a Transformation to convert the value in step 1, to a value that is not valid for the field in step 2
  • Step 5: That invalid value will trigger an exception


Let's dive into some examples:


Example of skipping specific gifts: I want to exclude any gifts that don't have a campaign value of "Virtual Events", because our organization's other campaigns go through other processes.


  • Step 1Determine the source field that will identify the records you would like to send to exceptions

The "Donation- Campaign ID" field identifies which records should go to an exception. If the Campaign ID is "Virtual Event," I want to include those gifts. If the value is not "Virtual Event," I want to send those to exceptions


  • Step 2Select a field that has a required value in the destination, but that you currently don't need to populate

The Reference Date field is required to be numerical and I am not mapping that field in this import.


  • Step 3: Map the field that will identify the records you would like to send to exceptions to the field that has a required value in the destination

I will map Donation-CampaignID to the Reference Date field:

    

  • Step 4: Set up a Transformation to convert the value in step 1, to a value that is not valid for the field in step 2 

Apply a transformation to convert everything except "Virtual Event" to the value ^%&*(DELETE)*&%^ 

You can use any value here that will create an exception. It is helpful to include some indicator that you intended to delete this record from the import, so that you can easily understand why that record is in the exception bucket when reviewing exceptions. The transformation will then set the value of "Virtual Event" to blank, to ensure that data is imported correctly. 

    

  • Step 5: That invalid value will trigger an exception

You can now test this transformation to confirm the Virtual Event gift records import successfully


Example of skipping Constituent Records: The Source Data includes both Individual and Organization donors, but you only want to import Individuals. We attempted to filter the data in the source, but that isn't an option, so instead you can send the Organization records to an exception.

 

  • Step 1Determine the source field that will identify the records you would like to send to exceptions

First we need to understand which field indicates if a record is an Individual or an Organization. We determine "Payment - DonorType" has a value for "I" for Individual or "O" for Organization.

Tip: The field you use depends on the source of your data. You may be able to use another field scenario like "Organization Name is not blank."   


  • Step 2Select a field that has a required value in the destination, but that you currently don't need to populate

In our sample formula, we  know "birthday" is a field that requires a specific numerical value, however we don't need to import birthdays in this import. Therefore, I will map "Payment-DonorType" to the Birthday field


  • Step 3: Map the field that will identify the records you would like to send to exceptions to the field that has a required value in the destination



  • Step 4: Set up a Transformation to convert the value in step 1, to a value that is not valid for the field in step 2 

Since any record with an invalid value in the Birthday field will create an exception, we can set up a transformation to map the invalid value for Organization records, but leave the value blank for Individual records. In this example, the invalid value is "^%&*(DELETE)*&%^" (As mentioned above, you can use any invalid value here)

    

    

    

  • Step 5: That invalid value will trigger an exception

You can now test this transformation, by processing a set of records, and confirming the Organization records all go to the destination exception bucket.



Adding a new formula to import the records that went to exceptions: If instead of deleting the Organization records you still wanted to import them, we can handle that too by setting up a complimentary formula to do the reverse process. In this case, we will:

  •     Create a formula for sending Organization Records. 
  •     Update all field mapping for the Organization data

Now you will need to send the individual records to exceptions. You will repeat Steps 1-3 from the Individual Formula, mapping the Payment-Donor Type to Birthday.

  • Step 4: Apply the transformation, which will be slightly different since we need to send Individual records to exceptions this time:

    

  • Step 5: You can now test this transformation. 

    

Always make sure to test with a small set of records, prior to implementing this change on all records in your formula. If you have any questions, please reach out to our support team.