Start a new topic
Answered

Kick $0 gift rows to an exception file

I'm using IOM to import Sphere transactions.  Each row contains the constituent data and gift data.  I do a 2-pass - once to create the constituent record and the participant record (for gifts with an event ID), and a second pass to create gifts, and link gifts to constituents and participants.  Some of the registrations are $0.  I tried importing with gift amount set to $0, and that gift ended up in batch with $0 (don't want).  I then created a dictionary to convert $0 to "FREE", but that resulted in the entire batch stopping at that row, and did not process any rows after that $0 row, due to an RE exception.  We now have to manually remove the $0 entries in between Step 1 and Step 2, since IOM doesn't just send the $0 rows to exception.  How do we get IOM to treat this as an exception, instead of crashing the whole batch?

Thanks!


Best Answer

Hi Krista,

Sure, all you need to do is modify the code slightly.  Right now it's testing for values of exactly zero.  What you want is to test for values less than or equal to zero.  You might also want to modify the cancelled message slightly from "Zero Amount Gift" to something like "Gift Amount less than or equal to zero".  


Here is the modified code:


Public Overrides Sub BeforeComputedColumns(Cancel as ImportOM.API.iCancel)

              If import.Fields.GetByName("GiftAmount").Value <= 0 Then

                         cancel.CancelRow("Gift Amount less than or equal to zero")

              End If

End Sub


1 person has this question

Hi Art, This is no problem with the IOM API. As a matter of fact, the code is very simple. To use this code: 

1. Open your IOM gift profile and click Virtual Fields 
2. In the lower right hand part of that form, click Launch Code Editor 
3. when the code editor opens, there are several events listed by default. The event you need is called BeforeComputedColumns. It looks like this: 

Public Overrides Sub BeforeComputedColumns(Cancel as ImportOM.API.iCancel) 
'Raw data straight from the file 
'Use the Fields object to access the data 
'Example: Import.Fields.GetByExcelName("D").Value = "New Value for D Column" 
End Sub 

All you need to do is to replace that entire block of code with the following code: 

Public Overrides Sub BeforeComputedColumns(Cancel as ImportOM.API.iCancel) 
If import.Fields.GetByName("GiftAmount").Value = 0 Then 
cancel.CancelRow("Zero Gift Amount") 
End If 
End Sub 

This will wash all gifts with a zero dollar amount into the exception file and it will give the reason in the file as "Zero Gift Amount." This block of code assumes that your gift amount field is called "GiftAmount". If that is not the case, you can change it in the second line of code to the correct name. If you have any issues with it, I would be glad to jump on a quick call on Monday to help out, but my guess is that this will resolve your issue. 

Have a great weekend. 

Jim
Jim - YOU ARE A LIFE SAVER!!! That code did the trick! On behalf of our data processor, and the accounting department, we thank you for helping us get rid of an annoying Excel file manipulation step of an import process that's long enough as it is. Thanks again!!

Art
Art,

My pleasure. Have a great weekend.

Jim

Jim,


I used the code you provided Art and was able to kick my $0 amounts to an exception file. Is there a way to do with this negative amounts as well? Or is there an option to use a dictionary to kick out any negative amounts to an exception file?


Krista 

Answer

Hi Krista,

Sure, all you need to do is modify the code slightly.  Right now it's testing for values of exactly zero.  What you want is to test for values less than or equal to zero.  You might also want to modify the cancelled message slightly from "Zero Amount Gift" to something like "Gift Amount less than or equal to zero".  


Here is the modified code:


Public Overrides Sub BeforeComputedColumns(Cancel as ImportOM.API.iCancel)

              If import.Fields.GetByName("GiftAmount").Value <= 0 Then

                         cancel.CancelRow("Gift Amount less than or equal to zero")

              End If

End Sub


1 person likes this

Thank you, Jim! I don't know why this didn't cross my mind last night! :) I'll chock it up to lack of sleep.

Thanks again for your help!

Krista

Login or Signup to post a comment