Backfilling US or CA addresses in your Network instance

Existing customer who enable and use the Address Lines Construction feature, might want to backfill existing locally managed addresses to ensure that duplicate entities (HCPs, HCOs) are not created.

Avoid duplicate entities

Duplicate entities might be created if locally owned entities are loaded into Network without custom keys, particularly if the subscription's match rules only consider the address_line_1__v field.

Review this example to understand how a duplicate entity might be created if your match rules only consider the address_line_1__v field.

Existing record

The address lines construction feature is not enabled in the Network customer instance. This is an HCP record in your Network instance.

entity_id first_name last_name address_id address_line_1 address_line_2 locality administrative_area postal_code country
932349918506319903 John Smith 932349918506188831 Surrey County Health Center 118 Hamby Rd Dobson US-NC 27017-8471 US

Source file added

The feature is now enabled in your Network instance.

A source file is loaded and it contains an update to the existing HCP (a middle name) but custom keys for the entity and address are not included in the file.

first_name middle_name last_name address_line_1 address_line_2 locality administrative_area postal_code country
John Franklin Smith Surrey County Health Center 118 Hamby Rd Dobson US-NC 27017-8471 US

Duplicate entity created

After the address line rules ran and the address in the source file was reconstructed, the match rules could not match the entities using address_line_1__v. Instead of updating the existing entity, a new entity is created

first_name middle_name last_name address_line_1 address_line_2 locality administrative_area postal_code country
John Franklin Smith 118 Hamby Rd Surrey County Health Center Dobson US-NC 27017-8471 US

Update local addresses

To update the existing US or Canadian local addresses in your Network instance, use reporting to find the affected addresses and then load them into your Network instance using a source subscription so the address lines construction rules will run and update them.

Create a report

Create a report to find the locally owned addresses in your Network instance that have a building or organization in Address Line 1 or Address Line 2.

The report must include the following data:

  • Entity Network IDs and Address Network IDs - Including the IDs prevents duplicate entities from being created.
  • Full address (Address lines 1, 2, and 3; city; state; ZIP; and country) - This ensures that the rules are applied to the existing addresses.

The downloaded results (.csv file) must include the following column headers:

  • entity VID
  • address VID
  • address_line_1__v
  • address_line_2__v
  • address_line_3__v
  • locality__v
  • administrative_area__v
  • postal_code__v
  • country__v

Example

To create a report that meets the data and column header requirements:

  1. On the Network menu bar, click Reports > SQL Query Editor.

  2. In the text field, paste the following SQL query to retrieve all locally owned valid addresses:

    SELECT address.vid__v,
       address.address_line_1__v,
       address.address_line_2__v,
       address.address_line_3__v,
       address.locality__v,
       address.administrative_area__v,
       address.postal_code__v,
       address.country__v,
       address.organization__v,
       address.building__v,
       address.sub_building__v,
       address.premise__v,
       address.thoroughfare__v,
       address.address_verification_status__v,
       address.entity_type__v,
       address.entity_vid__v
FROM address
  INNER JOIN hcp ON address.entity_vid__v = hcp.vid__v
WHERE address.record_state__v = 'VALID'
AND   hcp.record_state__v = 'VALID'
AND   address.is_veeva_master__v = 'f'
AND   (address.address_line_1__v = address.building__v OR address.address_line_1__v = address.organization__v)
UNION
SELECT address.vid__v,
       address.address_line_1__v,
       address.address_line_2__v,
       address.address_line_3__v,
       address.locality__v,
       address.administrative_area__v,
       address.postal_code__v,
       address.country__v,
       address.organization__v,
       address.building__v,
       address.sub_building__v,
       address.premise__v,
       address.thoroughfare__v,
       address.address_verification_status__v,
       address.entity_type__v,
       address.entity_vid__v
FROM address
  INNER JOIN hco ON address.entity_vid__v = hco.vid__v
WHERE address.record_state__v = 'VALID'
AND   hco.record_state__v = 'VALID'
AND   address.is_veeva_master__v = 'f'
AND   (address.address_line_1__v = address.building__v OR address.address_line_1__v = address.organization__v)
  3. Click Run.
  4. In the Results section, review the returned addresses.
  5. To save the report on your local computer, click Download Report. (The .file that is created will be referred to here as "File A".)

Load the addresses into Network

Follow these steps to update the addresses in File A (if required) and load them into Network to trigger the address lines construction rules.

  1. Optional. The address cleansing tool cannot always parse building, organization, premise, thoroughfare, and sub building when they are placed in separate address lines. For example, address line 1 = building, address line 2 = organization, and address line 3 = premise, thoroughfare, and sub building.

    For these addresses, try updating the address fields to the following format in File A:

    • address_line_1__v = building
    • address_line_2__v = street number <space> street name <space> suite number <space> organization

    If the third-party address cleansing process is able to validate the address, it will parse the data into the correct fields. After the address is cleansed, the address lines construction rules can be applied.

  2. Optional. In the Admin console, click System Interfaces > Systems and add a new system called AddressLinesConstruction.

    Note: You can use an existing system for the addresses, but using a specially named system enables you to later find the records that were updated by filtering on that system.

  3. Configure a source subscription to use the exported address file (File A) as the source file. An existing subscription can be used. If you created a new system, ensure that you change the System to the one that you just created.
  4. Run the source subscription to trigger the address lines construction rules.

The addresses will be updated so that address lines are reconstructed.

Compare updated addresses

These steps are optional.

Create a new file so that you can compare the source addresses with the newly reconstructed addresses in a database. If you see unexpected results, you can manually override the addresses.

  1. Use reporting or a target subscription to export the addresses that were processed by the address lines construction rules. Add the filter, System = Address Lines Construction. (This will be referred to as "File B").

  2. In the exported file, update the address lines column names with the following changes:

    • address_line_1__v to reconstructed_address_line_1__v
    • address_line_2__v to reconstructed_address_line_2__v
    • address_line_3__v to reconstructed_address_line_3__v
  3. Import File A and File B into a database.

  4. Create another file, File C, by cross-referencing the entity and address Network IDs in the two files (Files A and B).

    The column headers in File C should be:

    • entity_VID
    • address_VID
    • address_line_1__v
    • address_line_2__v
    • address_line_3__v
    • reconstructed_address_line_1__v
    • reconstructed_address_line_2__v
    • reconstructed_address_line_3__v
  5. Compare the source address line fields with the Network reconstructed address line fields.

If the reconstructed address lines are in an unexpected format, use the address Network IDs to find the addresses in the Network UI and manually override them.