Duplicate record validation in Address Book
Before you can use other JD Edwards EnterpriseOne systems, you must enter address book information in the Address Book system for your customers, suppliers, and so on. Other JD Edwards EnterpriseOne systems retrieve up-to-date name and address information, based on the address book information that you enter. Use the Address Book Revisions program (P01012) to enter address book records. The system stores information for address book records in the Address Book Master table (F0101).
In addition to basic address information, you can enter an unlimited number of phone numbers for each address book record and specify whether the phone number is a business, cellular, fax number, and so on. Phone numbers are stored in the Address Book – Phone Numbers table (F0115).
Duplicate Entry Validation
To alleviate the potential for duplicate records when you enter, revise, or upload address book records, you can activate duplicate record validation in the Address Book system. You specify whether you want the system to issue a warning or error message when it finds records with the same alpha name, city, and zip code combination or the same tax ID.
The Address Book MBF (P0100041) has two processing options that specify whether to search for duplicate records:
• Validate for Duplicate Data Entry:
This validation identifies records with the same alpha name, city, and postal code. Records with abbreviations are not identified. For example, Saint Petersburg and St. Petersburg are not identified as duplicate records.
• Validate for Duplicate Tax ID Entry:
This validation identifies records with the same tax ID in the same country. Records with a blank tax ID are not identified. You can set either or both of these processing options to issue a warning or error message. The system allows you to accept entries with warning messages, but not with error messages.
For duplicate data entry validation, the system searches the F0101 and F0116 tables for a matching alpha name, city, and zip code. For duplicate tax ID entry, the system searches the F0101 table for a matching tax ID and the F0116 table for a matching country.
The processing options of the program P0100041 are accessible by typing IV in the fast path. The application Work with Interactive Versions will be opened where you can search for and select the application P0100041. A list of all available versions will be shown. The processing option of each version can be accessed via the Row exit.
These duplicate validation processing options are stored in the Edit tab.
Some examples will be given to show the effect of the different setting of the process option when creating duplicate records in the address book:
4 Customer records will be created with the following details:
|Record 1||Record 2||Record 3||Record 4|
|Alpha Name – ALPH||Customer 1 nv||Customer 1 NV||Customer 1, nv||Customer 1 nv|
|Tax ID – TAX||BE123123123||BE123123123||BE123123123||BE123123123|
|City – CTY1||LEUVEN||LEUVEN||LEUVEN||BRUSSEL|
|Postal code – ADD2||3000||3000||3000||1000|
Test 1 – settings
In our first attempt the processing options are set on the highest level of security, so we don’t accept any duplicate VAT number entry or duplicate alpha name entry in the same city:
Test 1 – results
Only record 1 can be created. When attempting to create records 2 and 3 we always get duplicate key error messages. For these 2 records we get 2 error messages because we are validating on the 2 levels.
As you can see even when the name is not exactly the same (one in capital letters – record 2 – and the other name with small letters – record 1 or when one name even has reading signs include – record 3) the system still considers them to be equal.
When attempting to enter record 4 we only get 1 error message because the record only fails on the VAT validation. It passes the Duplicate Data Entry validation because although it has the same name as record 1 it does have different values in the city and the postal code fields.
Test 2 – settings
In our second attempt the processing option are set on medium security, so when entering duplicated data (address records or VAT numbers) only a warning message is issue but if wanted the record can be saved.
Test 2 – results
All records can be saved now if wanted. When clicking for the first time on the OK button to save your record you will be prompted with a warning message. It is up to the user now to decide if he wants to save the record yes or no. Clicking for a second time on the OK button the record will be saved and if the user clicks on cancel now nothing will be written do the address book file.
So in principle with these settings all records could be created if you neglect the warning messages and save all records.
Also now when attempting to enter record 4 we only get 1 warning message because the record only fails on the VAT validation. It passes the Duplicate Data Entry validation because although it has the same name as record 1 it does have different values in the city and the postal code fields.
Test 3 – settings
In our last test we set the security to the lowest level. No validation is done on duplicate records or the use of duplicate VAT numbers.
Test 3 – results
All records can be saved now if wanted, but since no warning message is projected first with this security level the records are saved immediately when clicking for the first time on the OK button.
Even exactly the same records can be created without a warning.
Recommended is to set for the VAT validation the security on 1 and for the validation on creation of duplicate records on 2. Then the system will give a warning message when records with the same VAT number are created, which is required sometimes (customers with 1 VAT number but with different locations) and doesn’t allow records with the same name and the same city and postal code values.
If a record with a similar name needs to be created in the same town (2 locations within one city) then an extra reference word can be added to the Alpha Name to allow the created of both records.
In the above example you could write in the Alpha Name an additional word like Warehouse and Office for instance with will pass the validation security.
The search type won’t change anything to the duplicate record validation. So creating the same alpha name with the same city and postal code but with difference search type (for instance C for customer and V for suppliers) will generate an error message and won’t be saved. So also here you have to add an extra reference to the Alpha name field.
You can of course also create one address book record with both AP master details (for the supplier) and AR master details (for the customer).
Although you will have to add extra reference words in the Alpha name field for duplicate address records in the same city you can use the same name in the Mailing name (used for you outgoing correspondence: invoices, reminders, …) because no validation is done on that field.