How to clean up your references to meet reference rules
Reference codes must be 12 characters long and end in subnationCode followed by nationCode. For example, if your program is NY, in order to save a reference record your reference code will need to end in NYUS. Also, references in your database cannot have another program's UID, which means the reference_ou_uid must refer to your program or to Central. The queries below find references that break these rules.
Unless you have already gone through this cleanup process with Central, you are certain to have invalid references due to past unintentional data exchange behavior.
Unused references can be deleted or fixed. But reference records that are used in your database (in an EO or EST, for example), need to be fixed or deleted after replacing with another record for the same reference wherever they're used.
To see where a reference is used see How can I tell where a reference record is used?
Some cleanup you can do yourself, and other cleanup requires help from Central, as described below. Please contact Kristin Snow (email@example.com) or Margaret Ormes (firstname.lastname@example.org) for assistance. "Central" in the text below means Margaret and Kristin, for now.
A. Queries for each category of invalid reference. Each query is in a different attachment named with the bullet number.
A1. References with your program's UID but an invalid reference code suffix, locally-maintained
--You can fix these without assistance.
To fix: modify the reference code so that characters 9-10 are your program's 2-character code and characters 11-12 are "US" or "CA". Or you can delete the reference if it's not used.
A2. References with your program's UID but an invalid reference code suffix, NOT locally-maintained
--These are the same as #1 except that Central needs to change them to locally-maintained before you can edit them.
To fix: For any references you want to keep (and fix), ask Central to set the reference to locally-maintained. You can then modify the reference code so that characters 9-10 are your program's 2-character code and characters 11-12 are "US" or "CA". The rest should be deleted; you can ask Central to delete them for you.
A3. Other programs' references, used in your database
--These are references you erroneously received during old data exchanges and then used.
To fix: a) If you have a valid local record for the same reference, you can replace the invalid reference with the valid duplicate wherever the invalid reference is used. (See How can I tell where a reference record is used?. Replacing with a valid central reference is also an option, but we don't recommend that solution; see Use of Central references in program databases) Once a record is no longer used, it will show up in query A4 as an unused reference to be deleted. Note: Any of these references used in Central records have been fixed in Central, but you cannot edit Central records in your database. Central can help you with these, and some may be corrected by your next data exchange.
b) A faster solution is to ask Central to convert the reference to a valid local reference. To do this we'll update the reference code suffix and UID, and set the record to locally maintained.
A4. Other programs' references, NOT used in your database
--These are references you erroneously received during old data exchanges and never used.
To fix: These should all be deleted, but you will not be able to do so through the interface. Please send this list to Central staff and we can delete them for you.
B (all). One query for all invalid references at once. It's a good idea to run this to make sure all invalid references were cleaned up in previous steps. Some may have been missed, or some may be in a miscellaneous category not caught by the other queries.