Hi! I have a gift import which currently adds the same constituent code to everyone in the file - "Direct Debit Donor", with a start date of the current system date. Some of the people in the file are giving to a certain appeal (LWCAng-INT), and I would like them to have an additional constituent code of "LWC Angel", again with the current system date as start date. This is as well as "Direct Debit Donor". I think this is possible with the API, but I'm not sure how. Do I need to create a virtual field first of all, with the appeal code and a dictionary to translate LWCAng-INT into LWC Angel? And then...? Any words of wisdom much appreciated!

Hi Amanda,

Thanks for the speedy reply!

In the meantime, I saw this post of your in reply to another question last year (http://www.omaticsoftware.com/Forums/tabid/108/aft/997/Default.aspx#1878):

04 Apr 2013 06:04 PM Quote Reply Alert
Hi Wayne,

In your dictionary you'll have these two entries:

Replacement Value 1: recurring
Value to match on 1: RD101
Regex OFF

Replacement Value 2: --BLANK--
Value to match on 2: \b(?(\brecurring\b)MATCHED|.*)\b
Regex ON

Do you think I could create a dictionary like this to use with the virtual field, to avoid having to list every appeal separately? There are a few other appeals - not dozens, but they change regularly, so it would be easier to maintain a dictionary that had an option for "anything else".

Kind regards,

Sophia

Thanks, Amanda!

In the end, I did it with the API, just because I'd never used the API before so I thought I'd try it out!

This is the code I used, which adds both the LWC Angel constituency code, and also a start date for the constituency code which is based on the gift date field in the file:

Public Overrides Sub AfterDictionaries(Cancel as ImportOM.API.iCancel)
'The data row at this point has gone through dictionaries
'Use the Fields object to access the data
'Example: Import.Fields.GetByExcelName("D").Value = "New Value for D Column"
If Import.Fields.GetByExcelName("N").Value = "LWCAng-INT" Then
Import.Fields.GetByExcelName("AN").Value= "LWC Angel"
Import.Fields.GetByExcelName("AO").Value= Import.Fields.GetByExcelName("O").Value
End If

End Sub

This seems to be working - I've tested it with two sample rows, one of which had LWCAng-INT as the appeal, and the other of which had another appeal. I will be importing real data with this profile on Monday, so I will keep a close eye on it then as well, and make sure it's doing what I want it to. (The file should be pretty small, so it's not impractical to go through the results one by one).

Yay! I hope this is the start of a beautiful journey into the world of API!

Thanks again,

Sophia
P.S. I also found this thread useful, if anyone's in a similar situation:
https://www.omaticsoftware.com/Forums/tabid/108/aft/569/Default.aspx

Your code in the function would look like:

  If oField.Value = "LWC Ang-INT" Then
    sReturn "LWC Angel"
  Else
    sReturn ""
  End If
  Return sReturn

Then you would still use your new virtual column in AN for the new constituent code but in the "function" column on the right you would select the new function you made "LWCAngel" and in the Seed column you would select the column with your input data (N in this case) which gets automatically set to oField in the custom function.

Notice that I did not set the date here, that is because you can use the option "Default new constituent code dates to process date" under the General Settings tab in your profile.  That way the date will be automatically set!

Wow, thanks, Wayne - I didn't even know you could create custom functions! I will have to try that out some time soon - looks handy!

Sophia

So... this is working really well, and I now want to add to it.

At the moment, it is adding a certain constituent code, if the file contains a certain appeal code.

I now want to tell it to add a different constituent code if the file contains one of three other appeal codes.

I tried this:

...

End Sub
Public Overrides Sub AfterDictionaries(Cancel as ImportOM.API.iCancel)
'The data row at this point has gone through dictionaries
'Use the Fields object to access the data
'Example: Import.Fields.GetByExcelName("D").Value = "New Value for D Column"
If Import.Fields.GetByExcelName("N").Value = "LWCAng-INT" Then
Import.Fields.GetByExcelName("AT").Value= "LWC Angel"
Import.Fields.GetByExcelName("AU").Value= Import.Fields.GetByExcelName("O").Value
Else If Import.Fields.GetByExcelName("N").Value = "CCONL01" Or Import.Fields.GetByExcelName("N").Value = "CCONL02" Or Import.Fields.GetByExcelName("N").Value = "CCONL03" Then
Import.Fields.GetByExcelName("AT").Value= "care child"
Import.Fields.GetByExcelName("AU").Value= Import.Fields.GetByExcelName("O").Value
End If

...

That brought up a couple of errors - "expression expected" and "syntax error". I'm a total novice at this, really, and I've run out of guesses! Could anyone give me a hint? It would be very much appreciated!

Many thanks,

Sophia

Hi Sophia,

VB has a control structure called Select/Case that would help clarify this.
http://msdn.microsoft.com/en-us/library/cy37 14y.aspx

You would do something like
Select Case Import.Fields.GetByExcelName("N").Value
Case "LWCAng-INT"
sConsCode = "LWC Angel"
Case "CCONL01", "CCONL02", "CCONL03"
sConsCode = "care child"
End Select
Import.Fields.GetByExcelName("AT").Value= sConsCode
Import.Fields.GetByExcelName("AU").Value= Import.Fields.GetByExcelName("O").Value


BUT I think that it would be easier at this point to just use a dictionary to do this since you are converting several codes. Also, there is a build in function in IOM called "Current System Date" that you can apply to a column to set it as the current date, you don't need to copy the value like you are doing (I think that's what you are doing right?)

Hi Wayne,

Thanks for this!

The reason I'm not using a dictionary is because most donations do not need a second constituent code adding. There is one constituent code common to them all (Direct Debit Donor, which is in the individual default set associated with the profile), but it is only the "LWC Angel" and "care child" ones that also need a second constituent code. Then again, as I type this out, I am starting to question my logic. I was avoiding a dictionary because there is no option for "anything else", but I suppose I could just list the other appeal codes and then leave the replacement values blank. The disadvantage is that I would have to keep updating the dictionary as we added new appeal codes, but I suppose I am having to update the API right now anyway.

I will try the VB code you very kindly suggested, and see how that goes - I am really keen to hold onto the only little bit of API code I have ever done! :-) If that doesn't work out, I may try a dictionary.

Re: the date, I am copying the gift date because that is the day that they actually signed up, though you are right that I could just use the current system date default and it probably wouldn't make much difference!

Thanks again, Wayne! Have a brilliant day.

Sophia

The post that you referenced originally about dictionaries should let you set up an "anything else" scenario so you would only have to add new appeals if they translated to a constituent code.