We are a group of users of Blackbaud products and are not affiliated with Blackbaud. We'd love to have you join our community to help and be helped in getting the most from your Blackbaud software.
Register now to join us to get independant advice on your system, connect with 3rd party consultants to help you maximize your database and have a real alternative to the official Blackbaud website.
We are thinking of using the constituent import id as a foreign key in an accompanying system. Would there be any reason why an individual import id would change during the lifetime of the individual record in RE? (other then deleting and recreating the record)
We thought of using the constituent ID but that may be NULL depending on the record; we then thought of using the record ID but that would not work for new people. Our outside system would generate a new import ID for the new individual to be imported into RE but since we would not be able to import a manually created record ID we need another way to track the individual across systems.
We are thinking of using the constituent import id as a foreign key in an accompanying system. Would there be any reason why an individual import id would change during the lifetime of the individual record in RE? (other then deleting and recreating the record)
We thought of using the constituent ID but that may be NULL depending on the record; we then thought of using the record ID but that would not work for new people. Our outside system would generate a new import ID for the new individual to be imported into RE but since we would not be able to import a manually created record ID we need another way to track the individual across systems.
Are there any flaws in this thinking?
Thanks,
Matt
Matt, What do you mean by "we would not be able to import a manually created record ID..."?
We too have an external 3rd party system, and I'm able to get the import ID out of RE & assign it to the record in the 3rd party system.
I've started importing in all my new students & parents with specific import ID's (class year & a 4 digit random number for students & the ascending id 9000#### for parents)
This works really well; I'm hesitant to provide specific feedback until I understand your comment about manually created id's...
Thanks,
Elaine
__________________ Elaine Tucker Stewardship Coordinator St. Mark's School of Texas USA www.smtexas.org
From what I understand your analysis is spot on. You cannot use constituent id as it is not enforced, it may be null. You cannot use record id as you are not able to specify what it is (it is wholly generated internally by RE) so you should use import id.
You are able to specify the import id yourselves and it cannot be changed once it has been set.
[quote=Elaine Tucker;14739]Matt, What do you mean by "we would not be able to import a manually created record ID..."?
Hi Elaine,
Our outside system will be creating and eventually importing new users into Raisers Edge. We need some value to link the records together across the two systems.
We can create an import ID for the new record in the new system for import into RE but we can not create a new record ID to import into RE. As David stated above, RE does not allow an imported record ID since RE creates it automatically on Import. Once RE creates the new record ID, there is no easy way to get it back to the outside system to link the records / systems together.
The import ID is the value that unique to the record and is available in both systems since we created it manually in one and imported it into the other (RE). As David states, thankfully, it will not change. Thus we can use it as a primary / foreign key for the record across both systems.
There are some potential drawbacks to this system, but that doesn't mean you shouldn't use it. How are you handling duplicates? The most problematic would be the situation where both systems have a record, but the import ID in RE does not match the other system's ID.
The other issue is that the Import ID is not one of the search fields on the standard RE search screen. We actually use an Alias, so that we can look up records from the search screen. We have the problem that our patients are children, so we're soliciting the parents and the parents could potentially have multiple children as patients so we need to link multiple records in our patient data to one record in RE.
Drew
__________________ J. Drew Allen
Children's Hospital of Philadelphia
Crystal Reports and SQL Server Consultant
It is better to live your destiny imperfectly than to live an imitation of somebody else's life with perfection.
I agree that the major problem with using import ids as your primary key for constituents is that you cannot look them up in the same way as you can with constituent ids. One organisation that I worked with used the SSN field (NiN in UK) as the field that linked their two systems. That way they were able to look up the constituent from the search screen. The problem with that of course is the same as the constituent id. It could be changed and removed.
If you did want to go down that route (and have the VBA module) then a simple VBA routine could check that the value has not been changed.
That being said if you do not already own the VBA module I would not buy it just for that one control and would use the import id as you have outlined.
That is a valid point about searching on the import IDs. Our idea is to still use the Constituent ID as a way to track people through Raisers Edge. If the constituent ID ever changes in Raisers, an update process would change it in the 3rd party system. The same goes for the reverse. That way the records would always be linked up through the import ID, invisible to the user.
Part of the update process will check for the last update date in both systems and move the data accordingly.
To the point about duplicate constituent IDs, we plan on moving all the data over to the 3rd party system; and for new ID's in the 3rd party system start it with a new series numeral or letter that would be independent of what is currently in Raisers Edge. That way when the update process occurs across both systems there will not be a duplicate ID error.
At the same time new people are created a duplicate person process will take place to make sure they are not already in the system.
One workaround is to make the ConsID a required field, and set to autocomplete within RE to a specified format, alternatively prior to importing into the other database system, perform a query on the records with a null conID, export the Import ID & ConsID and populate the ConsID field with a sequence different to any that your systems use to be recognisable, then re-import based off the Import ID.
Constituent Import ID Question 
Linear Mode
