Do you know if the dashes can be removed? So, when a search is done, it can be done without using the major key........ If you know how to do this, please share!!!
Thanks!!!
Does anyone use major key (-) on the Customer Profile?
Routing Number Field----Custom Field in Customer Module
On September 1st, 2011 lurton says:
Bruce,
Thanks soooo much for your response!!! I will try to explain it to you.....I think we will have to write a script to remove them all from the database. Let me see if I can try to explain things better since I am new to all of this IT stuff!!!!
In iMIS in the Customer module when you click find, a drop down menu pops up and then you choose from this drop down menu how you would like to search for a company/financial institution or an individual. Currently when we select Routing Transit Number from this drop down menu we have to put the dashes in the routing number to search because that is the way they have all been set up. Now, we have a department internally that wants to know if we can remove all of those dashes and be able to do the search without the dashes in there......I hope this makes since....
Let me know if it doesn't. Thanks!!!
Laticia ; 0 )
Possible
On September 1st, 2011 Bruce Wilson says:
It's certainly possible. Anything iMIS can do can be done through direct SQL.
Before you change anything, you need to determine if removing these dashes will cause other problems. Removing them from the data means they won't be printed with the dashes either. You can get around *that* by customizing your reports to put the dashes back in (assuming they always go in the same place), but that's complicated too.
This script assumes you want to remove all the dashes, not just the first one. It also assumes that there isn't some automatic process in place importing new records with the dashes included, or reformatting the fields with dashes.
Assuming you really, REALLY want to make the change in data, this script will probably do it for you... BUT!
- DON'T run this in your live database first. Try it in your test database and then exercise it thoroughly to make sure it didn't cause other problems. (If you don't have a test database, you need to ask someone like your AiSP to help you set one up.) Don't just see if things work, try to make them fail. Run reports that use this field.
- DON'T run this in your database unless you have a good backup and know you can restore it because you have tried. If this spams your members, deletes your database and puts snakes in your laundry, I'm not responsible.
- DON'T edit this script to update other fields. MAJOR_KEY is (probably) safe to make this change, but other Find options require a rebuild script, which is more complicated to write.
- Ditto for people reading this later -- this script is probably the right thing in her database. It is probably the wrong thing in yours.
Anyway, the script you probably (maybe) need is:
update Name
set MAJOR_KEY = replace(MAJOR_KEY, '-', '')
where MAJOR_KEY like '%-%'
--
Bruce Wilson
Director, Technical Services
RSM McGladrey, Inc.
Major Key Customer Profile
On September 2nd, 2011 lurton says:
Bruce,
I just wanted to say thank you again for all of your help!!! No worries, I won't hold you responsible. LOL I will discuss this with my manager before moving forward. However, I really appreciate you breaking down everything for me step by step!!
Have a good holiday!!
Laticia ; 0 )
Sounds very specific
Are you referring to dashes in the data? If so, this sounds very specific to how your copy of iMIS is configured and used. You should probably check with someone who was part of the original implementation to find out why this is as it is, and whether it will affect other processes.
If this isn't data in the field itself then I don't understand your question. Could you explain a little more of what you're seeing, what it does and what you'd like it to do instead?
--
Bruce Wilson
Director, Technical Services
RSM McGladrey, Inc.