Start a new topic

Method for adding From Date to existing Constituent Code

I have a source file that provides Constituent ID, Constituent Code Name, and either "Date From" or "Date To" on each row (sorted oldest to newest). 4-10 varieties of code name.

 

IOM will only update the first instance of the a given code on a given const record.

 

So, how can I maintain my constituent code from and to dates without deleting the specific code and reimporting the entire history, every week?

 

1- Could I easily map a RE export of the existing constituent code import IDs to my weekly import sheet? [sure, but what about multiple instances; and, doesn't this excel work introduce risk, perhaps unnecessarily?]

 

2- Could I develop a constituent code import ID system that can be "predicted" by the import sheet or IOM profile, and easily applied? [I'm imagining some combination of constituent ID and an identifier (year month week?) for the last import, but I can't see how to do this in a way that would not require some level of Export for those constituents who do not have activity in the current month but still need their CC import ID "updated" so they would be updated next month, should there be some status change (not to mention some level of deleting prior instances of the code?)].

 

Mostly just wondering: how folks do accomplish this task?

 

Also considered exporting all codes that are on the import sheet, global delete, and reimport to ensure that every instances of a code with a blank "Date To" is first on the record? Sounds like a lot of mess. Using "primary" const code for this also doesn't do much for me.

 

Thanks for the input!

 

Looked at attributes, but short of having a unique attribute for every code for every period needed (ex: "CodeX 2015.2 - Yes") I just don't see how Query can accurately identify constituents with that code in any given range... Const Codes seem the obvious choice for this information with to and from dates linked in one instance.


Hi Patrick

Are u running the latest IOM? The newest verions allows the import of multiple cons codes. we use multiple cons codes, but i have imported them at the same time, only updating for newer ones.

Previous to the current version, we used to just do an RE import for people we knew had more than 1 cons code, as RE Import allows you to do that.

Ta
Erin
Hi Patrick

Are u running the latest IOM? The newest verions allows the import of multiple cons codes. we use multiple cons codes, but i have imported them at the same time, only updating for newer ones.

Previous to the current version, we used to just do an RE import for people we knew had more than 1 cons code, as RE Import allows you to do that.

Ta
Erin
Erin, thanks for your reply!

We are using the latest version of IOM. However, by multiple constituent codes I assume you mean "more than one type of const code" in one import? Yes, I have no problem performing that task with regards to creating the code and writing the 'From Date'.

The question is, when there are multiple instances of one constituent code, how can I add 'To Date' to only one of those codes without providing the const code import ID? Ideally, the codes would be on the record in the order they were imported. And, ideally, when writing a 'To Date' I would have the option to write it to the first instances of a BLANK 'To Date'. So that, as long as I perform the import in order from oldest to newest, the appropriate code would be updated with a 'To Date' and would properly show a complete history of the constituent having that exact code. But IOM doesn't work that way, it either: 1- writes the end date on the first instance of that exact code, regardless of whether there is an end date, or 2- produces an exception. In order to pinpoint the exact code, I'd have to include a const code ID in the import file, but that presents challenges in itself since my import file is not sourced from RE.

Or, perhaps I'm not understanding your method? Are you saying that when adding a code via IOM you never leave a blank 'To date'? So that the codes become "self expiring" but then you overwrite a later 'To date' whenever that code should remain valid until the next import? I could see value to this method in certain cases, but don't see how it gets around the issue of multiple instances of the exact same code.

Appreciate your input.
Patrick
Hi Patrick

Now I get what you mean, that's very annoying!

We only ever import Cons code with a date From, we manually add Date To when relevant, i.e. a Regular Giver cancels their gifts

The only suggestion I can think of is exporting all the Cons Codes, Cons Import ID and Cons ID from RE and doing a VLookup in Excel - IOM people may have a better solution, but i used to do something similar when updating a Regular Gift, we didn't have the Import ID for the RG in the file so I had to do a query based on the Cons ID to export the RG Import ID so I could update it.


Goodluck
Ive learnt that RE doesnt do anything in a 'logical' order when searching on Fields where they might be more than one.
Erin,

Thanks again!

Since sending my last reply I was able to do exactly what you have suggested: write a few vlookups to "map" the CC ID (provided by export from RE) to the source file.

The trick, however, was ensuring that both data sets were in date order (old to new) and assigning on both (to each row) an "Instance ID" (ID unique to each constituent ID and instance of that exact CC on the sheet... by counting 1st in order, 2nd in order, etc). I was then able to pull the CC ID to the source sheet by looking up the instance ID from the RE export.

The assumption there, of course, is that at any given time for any given constituent code, there will be only one instance that has an open date that must remain open, the "odd instance out" so to speak. By counting instances and creating appropriate unique IDs, the code dates From and dates To should "line up" in the correct sequence and "close the code" appropriately for each period.

I don't have any other methods for 1- ignoring rows exported from RE which are not present in my source file, and 2- making sure the right CC ID gets assigned to the row with a To date which is first to follow the earliest provided From date. Perhaps there are better ways? Because this is not a terrible reworking of the data, but certainly not without risk.

Still convinced there must be a better way!! 

 

Anyone at IOM or in the community with other ideas?


I think that you are doing a great job working with what you have here!

The only other way that I can think of doing this would be a custom function through the API. We (or you depending on your programming knowledge) could build a custom function that could mimic exactly your business logic.

For example it could pick up the incoming code, look through all of the matching codes on the record and fill in the To date with today's date if it is blank. Then it would let the import do the normal adding of the new constituent code.

Login or Signup to post a comment