I'm trying to write a crystal report that would return the names of physicians (or nurses, MAs) who do NOT have credential information entered. I tried using IsNull({USR_CREDENTIALEDSTATES.STATE}) but it does not return any names and there should be at least 1 name (a "test" user whose job title is "Physician" but no other info has been entered ). Can anyone help?I'm not even sure I'm looking at the right table.
Thanks
Kate
In CPS the DoctorFacility table contains pertinent fields including: StateLicenseNo, SpecialtyLicenseNo, AdditionalLicenseNo, DEA, NPI. The USR_CredentialedStates contains the states the user is licensed in and dates of licensure, and links to the DoctorFacility table via PVID field. Feel free to contact me directly.
It has been my experience when you add an account in administration, the suffix is added to the last name for the LastName field in the USR table. But it doesn't always behave.
It is important to me because the MEL symbols like User.LoginName and User.RealName appear to use data out of the USR table not doctorFacility.
I haven't checked since our upgrade to CPS 12.0.12 but User_Roles and User_Title haven't worked properly since we went from 9.5 to version 10. They both return that everyone is a physician. So, I use the MEL lastname and realname to look for suffix credentials in some of my forms and text components to check for/enable/disable items for nurses or assistants versus providers.
I also built a text component that gathers our users into drop down lists based on their credential/sufffix. I have fields all over my forms that give a drop down list of user in credtial groups like all students, all dentists, or all physicians. The drop down is handy for little things like storing the patient's inhouse social worker or behavioral health counselor in an obsterm that I can display in the patient banner. Or if you want to give the physicians a button to send a flag to their nurse with a drop down of all nurses.
In our database the USR_CREDENTIALEDSTATES table is completely empty. I don't know if that is normal. We only operate in one state so maybe if we had more than one it might have data.
I think you should be using the view USRINFO if you are writing it in crystal. The USRINFO view is the same as the USR table without the passowrd info. (crystal will have an access denied on the USR table)
Inside of the USRINFO table you will see columns for: DEANUMBER LICNUMBER PRESCRIBERID NPI UPIN DATA2000.
There is also a column called IS_EP which indicates if they are an Eligible Professional and IS_CREDENT which indicates if they are credentialed.
That's the table we use, so I think you're on the right track. My first thought is to be sure you're doing a LEFT JOIN to the USR_CredentialedStates table, not an INNER.