Attachment K - SDMS Provider Management Import Tool User Guide

Bureau of Health Workforce
SDMS Provider Management User Guide

Shortage Designation
Management System (SDMS)

Provider Management Import Tool User
Guide
Associated Resources:
Facility Form User Guide
PCO Portal User Guide
Mapping Tool User Guide
Provider Management

January 2016

PROPERTY OF HRSA |BHW
1

Bureau of Health Workforce
SDMS Provider Management User Guide

TABLE OF CONTENTS
1.

Import Tool Introduction................................................................................................................................ 4

2.

Download....................................................................................................................................................... 4
2.1

3.

Export File ....................................................................................................................................................5

2.1.1

Activating Built-in Macro .........................................................................................................................7

2.1.2

Add New Location ....................................................................................................................................8

2.1.3

Update Existing Location .........................................................................................................................9

2.1.4

Deactivate Existing Location ....................................................................................................................9

Upload ........................................................................................................................................................... 9
3.1

Exceptions Report ......................................................................................................................................11

4.

System and Business Rules ........................................................................................................................... 13

5.

Appendices .................................................................................................................................................. 14
5.1

FAQs ...........................................................................................................................................................14

5.2

Best Practices .............................................................................................................................................15

5.3

Modifying Provider Data Examples ............................................................................................................15

5.3.1

Add .........................................................................................................................................................15

5.3.2

Update ...................................................................................................................................................16

5.3.3

Deactivate ..............................................................................................................................................17

PROPERTY OF HRSA |BHW
2

Bureau of Health Workforce
SDMS Provider Management User Guide

TABLE OF FIGURES
Figure 1 - Provider Management Landing Page.............................................................................................................4
Figure 2 – Download: Select State .................................................................................................................................5
Figure 3 – Download: Download Button .......................................................................................................................5
Figure 4 – Export File Tabs .............................................................................................................................................6
Figure 5 – Enabling Editing Capability ............................................................................................................................7
Figure 6 – Enabling Macro Capability ............................................................................................................................7
Figure 7 – Populated Action Column .............................................................................................................................8
Figure 8 – Populated Reason Code ................................................................................................................................8
Figure 9 – Accessing Upload Page ...............................................................................................................................10
Figure 10 – Upload Page ..............................................................................................................................................10
Figure 11 – Uploaded File ............................................................................................................................................11
Figure 12 – Exceptions Report: Invalid File ..................................................................................................................12
Figure 13 – Exceptions Report: Valid File ....................................................................................................................12
Figure 14 – Exceptions Report: Report Detail ..............................................................................................................13

PROPERTY OF HRSA |BHW
3

Bureau of Health Workforce
SDMS Provider Management User Guide

1. IMPORT TOOL INTRODUC TION
The import tool will provide PCOs the capability to add, update, or deactivate Provider location data in bulk by
modifying system generated excel files. The modified files can then be uploaded through the User Interface where
the system will validate the attempted modifications. The system will generate files with the most current provider
data for each state on a nightly basis at 12:00 AM EST.
Figure 1 illustrates the Provider Management landing page, where users will be able to reach the Search, Upload,
or Download pages by clicking on the desired tile. Clicking on the “Download” button will direct the user to the
Excel file repository where users can download the desired state’s data.
Figure 1 - Provider Management Landing Page

2. DOWNLOAD
The first step in modifying provider data in bulk is to download the system generated file. The system will provide a
dropdown listing all states available for download. PCOs will have the ability to download any state’s data, but the
system will only accept records with addresses that are in the same state as the PCO who is logged into the
system.

PROPERTY OF HRSA |BHW
4

Bureau of Health Workforce
SDMS Provider Management User Guide

To download the file, click on the ‘Select a State’ dropdown, select the desired state, and click download as
illustrated in Figure 2 and Figure 3 below.
Figure 2 – Download: Select State

Figure 3 – Download: Download Button

The file will be downloaded as a zipped folder that will contain the downloaded states’ provider information.
2.1 EXPORT FILE
The system generated provider data file, Export file, will only have provider locations with an “Included” status.
The file will have a Provider Locations tab and a Metadata tab, as illustrated in Figure 4. The Provider Locations tab
has all providers’ information which can be edited to modify provider data through the Import tool. The Metadata
tab has a creation date that informs the system when the file was generated. If an update is made to a record
through the user interface after the file was generated and an update is being attempted to that same record
through the Export file, the system will reject the attempted update. The Metadata tab also provides the reason
code values. The Metadata tab should not be modified or deleted, otherwise the system will reject the file when
attempting to upload and throw an “Invalid File” error message.

PROPERTY OF HRSA |BHW
5

Bureau of Health Workforce
SDMS Provider Management User Guide

Figure 4 – Export File Tabs

The Export file will contain the following columns and be populated with data for each location if the data exists
for those records:
1.
2.
3.
4.
5.
6.
7.
8.
9.

Action
NPI
First Name
Middle Name
Last Name
Suffix
Discipline
Specialty
Status

11.
12.
13.
14.
15.
16.
17.
18.
19.

Address Line 2
City
State
Postal Code
County Name
Latitude
Longitude
Age
Dental Auxiliaries

21.
22.
23.
24.
25.
26.
27.
28.
29.

Reason Code
Serves at Correctional Facility?
Serves at State/County Mental Hospital?
Annual Medicaid Claims
Medicaid Patient %
Homeless %
Migrant Farmworker %
Native American %
Sliding Fee %

31.
32.
33.
34.
35.
36.
37.
38.

Resident/Intern
J1 Visa Holder
Federal Provider?
NHSC Provider?
SDMS Last Modified Date
SDMS Last Modified By
*License Number
*License State

10. Address Line 1 20. Direct Tour Hours 30. Migrant Seasonal Farmworker %
*License Number and License State will have 15 columns each to accommodate providers with multiple licenses.
Users will have the ability to add additional columns, the system will ignore all but the original columns. Should any
of the original columns be modified or deleted, the system will reject the file and throw an “Invalid file” error
message under the Report Detail portion of the upload page and no updates will be made to provider data.
The first time the file is opened, users may encounter one or both of the following firewall warnings (depending on
the excel version and firewall settings) as illustrated in Figure 5 and Figure 6 below.

PROPERTY OF HRSA |BHW
6

Bureau of Health Workforce
SDMS Provider Management User Guide

Figure 5 – Enabling Editing Capability

Figure 6 – Enabling Macro Capability

To use the Export file, users may need to click Enable Editing and Enable Content buttons if prompted by Excel.
2.1.1

ACTIVATING BUILT-IN MACRO

In order for the system to know the action the user is attempting to make, the user will need to select one of the
options (Add, Update, or Deactivate) in the dropdown under the ‘Action’ column. These options have been built
into a macro in the Export file to facilitate users’ ability to modify the data. The macro can be activated by pressing
(Ctrl + Shift + I) on the keyboard. Activating the macro will populate the Action,
Dental Auxiliaries, Reason Code, Facility Information, and Employment Status columns’ options. Once the macro
has been activated, all the necessary dropdowns in the file will be populated with different options as illustrated in
Figure 7 and Figure 8 below.

PROPERTY OF HRSA |BHW
7

Bureau of Health Workforce
SDMS Provider Management User Guide

Figure 7 – Populated Action Column

Figure 8 – Populated Reason Code

Note: The system will only accept values from the provided options for all columns that were prepopulated by the
macro. Entering any other value will cause the record to be rejected and no updates will be made to that particular
record. The system will inform the user why the attempted action was rejected through the detailed report, which
can be viewed through the user interface. Section 3.1 explains the Exceptions Report in more detail.
The second step in modifying provider data in bulk is to update all the necessary records by selecting one of the
provided action types which are covered in sections 2.1.2, 2.1.3, and 2.1.4.
2.1.2

ADD NEW LOCATION

The Import tool will allow users to add new locations for existing providers within SDMS by selecting the ‘Add’
option from the dropdown under the Action column. The system will accept the following information when the
Add action is selected:











Address Line 1
Address Line 2
City
State
Zip
Postal Code
Dental Auxiliaries (If DH provider)
Direct Tour Hours
Facility Information (e.g. Serves at Correctional Facility)
Clinical Information (e.g. Annual Medicaid Claims)

PROPERTY OF HRSA |BHW
8

Bureau of Health Workforce
SDMS Provider Management User Guide



Employment Status (e.g. Resident/Intern)

Note: There can only be one address with an “Included” address. If a user attempts to add a new location with the
exact same address as an existing location, the system will throw an error message.
2.1.3

UPDATE EXISTING LOCA TION

The Import tool will give users the ability to modify any provider’s location information that already has an address
with the logged in PCO’s state. When attempting to update an existing location’s information, the system will
accept the following information when this action is selected:






Dental Auxiliaries (If DH provider)
Direct Tour Hours
Facility Information (e.g. Serves at Correctional Facility)
Clinical Information (e.g. Annual Medicaid Claims)
Employment Status (e.g. Resident/Intern)

Note: The system will only consider records that have an action type selected, if the selected option in the
dropdown is Select, the system will ignore that row’s record and move on to the next record.
2.1.4

DEACTIVATE EXISTING LOCATION

The Import tool allows users to deactivate any provider location belonging to the logged in PCO’s state. To
deactivate providers, users will need to select the ‘Deactivate’ option from the dropdown under the Action
column. The user must select an option from the dropdown under the Reason Code column or the system will
reject the deactivation attempt.
Note: When attempting to Update or Deactivate records using the Export file, NPI and Address information must
match the system’s records 100% with the following fields:





Address Line 1
City
State
Postal Code (At least the first 5 digits)

Should there be any difference with any of those fields, the system will reject the attempted update/deactivation
and throw an error message.
For examples of adding, updating, and deactivating records, please navigate to Section 5.3 in the Index.

3. UPLOAD
The last step to making bulk updates is to upload the modified file by accessing the upload page as illustrated in
Figure 9 and Figure 10 below.

PROPERTY OF HRSA |BHW
9

Bureau of Health Workforce
SDMS Provider Management User Guide

Figure 9 – Accessing Upload Page

Figure 10 – Upload Page

Users will not be restricted on the number of files that can be uploaded per user, but each file should contain no
more than 50,000 records. The system will notify users of a successful upload through the percentage counter
under the Browse button and the green banner at the top of the page. Depending the on the size of the file, the

PROPERTY OF HRSA |BHW
10

Bureau of Health Workforce
SDMS Provider Management User Guide

file may be reflected immediately under the Upload History section of the page, or the user may need to click the
Refresh button after a few minutes to see the file and its status.

3.1 EXCEPTIONS REPORT
Once the file makes it to the Upload History section of the page, as illustrated in Figure 11, it will display its status
and provide a hyperlink under the report column. Clicking on the hyperlink will open the Report Summary section
of the page that summarizes the actions attempted in the file. If a large file is uploaded, the file may remain in
“Processing” status for a while. Users will have the ability to see the progress the system is making with the file by
first clicking on Refresh button and then the hyperlink again. Each time the Refresh button is clicked, the system
updates the number of accepted and rejected records.
Figure 11 – Uploaded File

Once the file has been successfully uploaded, the system will run the file through two validation checks prior to
updating any provider records. The first system check will look at the file as a whole and the second check will
validate each record’s attempted modifications against the business and system rules.
The uploaded document will fail the first system check if it encounters one of the following issues:






The metadata tab has been deleted or modified.
An unsupported file format has been uploaded.
A file larger than 50 MB has been uploaded.
One of the original columns has been deleted.
A row has been added on top of the column headers.
PROPERTY OF HRSA |BHW
11

Bureau of Health Workforce
SDMS Provider Management User Guide

 An identical file has been uploaded.
If the system encountered any of the aforementioned criteria, the system will display 0 processed records in the
Report Summary section, and display an invalid file format error message in the Report Detail section of the page,
as illustrated in Figure 12 below.
Figure 12 – Exceptions Report: Invalid File

If the uploaded file passed the first system check, individual records would then start going through the validation
process. Figure 13 illustrates the Report Summary page where the file that was uploaded had a combined total of
654 add, update, and deactivate attempts. The system rejected 9 out of the 654 attempted actions because those
9 records did not meet the system or business rules.
Figure 13 – Exceptions Report: Valid File

All values under the Rejected column of the report summary will have a hyperlink if the value is greater than 0
(except for total, this will always have a hyperlink). Clicking on the 9 under rejected column will prompt the user to
the Report Detail page which will provide the error messages of the rejected records as illustrated in Figure 14. The
Report Detail section of the page provides the user useful information about the records that were rejected. It
displays the NPI, Row Number in the file, Action type, and the Error Message (describing why an attempted action
was rejected).

PROPERTY OF HRSA |BHW
12

Bureau of Health Workforce
SDMS Provider Management User Guide

Figure 14 – Exceptions Report: Report Detail

As mentioned before, an individual record will fail the second system check if it violates one of the system or
business rules. An uploaded file can have both accepted and rejected records in one file, it can be partially
successful. Section 4 covers the system and business rules in more detail.

4. SYSTEM AND BUSINESS RULES
The system will process updates to the Importer file according to the rules depicted in the table below, which
describes the editable columns when certain actions are selected.

Action

Column

Business Rule

File Rule

-Add
-Update
-Deactivate
-Add
-Update
-Deactivate
-Add

Action

NPI

An option must be selected if a record requires a
modification. All records with the “Select” option will be
ignored by the system.
NPI must exist in the system’s database.

Address Line 1

Must be within the state of the PCO modifying the record.

-Add
-Add

Address Line 2
City

May remain blank
Must be within the state of the PCO modifying the record.

-Add

State

Must be within the state of the PCO modifying the record.

-Add

Postal Code

Must be within the state of the PCO modifying the record.

-Add
-Update
-Add

Dental
Auxiliaries
Direct Tour

May only be selected for DH providers

Only values from the
provided dropdown can be
selected.
Cannot be blank. Must be a
numeric value containing 10
characters.
Cannot be blank. Can be
alphanumeric.
Can be alphanumeric.
Cannot be blank. Must
contain alphabetical
characters.
Cannot be blank. Must be a
valid state/territory
abbreviation.
Cannot be blank. Must be 5
to 9 numeric characters.
A value must be selected
from the options provided.
Must be a numeric value.

Must be a number between 1 and 168.

PROPERTY OF HRSA |BHW
13

Bureau of Health Workforce
SDMS Provider Management User Guide

Action

Column

Business Rule

File Rule

-Update
-Deactivate

Hours
Reason Code

A reason must be selected only if Deactivating a location.

-Add
-Update

Serves at
Correctional
Facility?
Serves at
State/County
Mental
Hospital?
Annual
Medicaid Claims
Medicaid
Patient %
Homeless %

Select if provider serves at a correctional facility

A value must be selected
from the options provided.
Only values from the
provided dropdown can be
selected.
Only values from the
provided dropdown can be
selected.

-Add
-Update

-Add
-Update
-Add
-Update
-Add
-Update
-Add
-Update
-Add
-Update
-Add
-Update
-Add
-Update

May only be selected for MH providers. If the selected value
is ‘Yes’, the value for Correctional Facility must be No.

Can be any number greater than or equal to 0.

Must be a numeric value

Can be any number ranging from 0 to 100.

Must be a numeric value

Can be any number ranging from 0 to 100.

Must be a numeric value

Migrant
Farmworker %
Native American
%
Sliding Fee %

Can be any number ranging from 0 to 100.

Must be a numeric value

Can be any number ranging from 0 to 100.

Must be a numeric value

Can be any number ranging from 0 to 100.

Must be a numeric value

Migrant
Seasonal
Farmworker %
Resident/Intern

Can be any number ranging from 0 to 100.

Must be a numeric value

Select if provider is a Resident or Intern.

-Add
-Update

J1 Visa Holder

Select if provider is a J1 Visa Holder

-Add
-Update

Federal
Provider?

Select if provider is a Federal Provider

-Add
-Update

NHSC Provider?

Select if provider is part of NHSC

Only values from the
provided dropdown can be
selected.
Only values from the
provided dropdown can be
selected.
Only values from the
provided dropdown can be
selected.
Only values from the
provided dropdown can be
selected.

-Add
-Update

Note: Only the fields mentioned in the table above will be considered by the system when files are uploaded, all
others will be ignored if modifications are made to the existing data.

5. APPENDICES
5.1 FAQS
PROPERTY OF HRSA |BHW
14

Bureau of Health Workforce
SDMS Provider Management User Guide

Q. How do I activate the macro or populate the dropdowns?
A. Press Ctrl+Shift+I

Q. Can I update an address by using the update action?

Q.
Q.

Q.

Q.
Q.
Q.
Q.

A. No, an actual address cannot be modified. If an address needs to be modified, the user will need to
first deactivate the address that requires address update, and then add a new location with the
updated address.
How can I add a new location?
A. By selecting the Add option under the Action column and adding a new address for the record.
Can I add additional columns and/or change the order of columns?
A. Yes, the system will only check that all original columns exist in the file. The only restriction is to not
modify original column titles or add new columns with the same title.
Can I modify/add different types of formatting to the file (e.g. data formatting/highlighting
rows/columns)?
A. Yes, the system will ignore any formatting done to the file, but still accept the value.
Can I hide rows?
A. Yes, the system will find any data in the file as long as it has a selected action type.
Can I delete rows?
A. Yes, it is actually encouraged to delete unnecessary rows.
Do I need to upload the whole original file?
A. No, it is actually recommended that only those records that need to be updated be uploaded.
Why do most providers in the Export file have 40 hours?
A. Those are the defaulted hours provided by SDMS to all providers. If 40 hours are not accurate for all
locations, then the user is encouraged to update them to reflect the correct number of hours.

5.2 BEST PRACTICES





Save an original copy and working copy in case something goes wrong with the working copy.
Split uploads by ‘Action’ type, if all action types are required, consider uploading all ‘Deactivate’ first,
‘Add’ second, and ‘Update’ last.
Try to keep files smaller than 50,000 records.
Consider deleting all unnecessary rows.

5.3 MODIFYING PROVIDER DATA EXAMPLES
The following sections will cover three basic scenarios on how to Add, Update, and Deactivate provider data using
the export file.

5.3.1 ADD
To add a new location, users could either type or copy the row of an existing provider’s location and update all the
necessary fields. The necessary steps to adding a new location are described below:
Step 1)
Step 2)
Step 3)

(Optional) Copy an existing row, then right click and insert the copied row as a new row
Select ‘Add’ from the Action column
Enter new location’s address information with all the required fields:
i. Address Line 1
PROPERTY OF HRSA |BHW
15

Bureau of Health Workforce
SDMS Provider Management User Guide

ii.
iii.
iv.
v.

City
State (Must be within PCO’s state)
Zip Code
Direct Tour Hours (Must be greater than 0)

Should one of the required fields be left empty, the system will reject the request to add the new location.
The example below illustrates the process of adding a new location by copying and inserting an existing location.

Note: The Importer File will only accept NPIs known to SDMS.

5.3.2 UPDATE
The steps to updating existing locations are described below:
Step 1)
Select ‘Update’ from the Action column
Step 2)
Update all the necessary information
Step 3)
Make sure all business and file rules have been followed which are described in Section 4 above.
The example below illustrates the process of updating Direct Tour Hours and Correctional Facility.
PROPERTY OF HRSA |BHW
16

Bureau of Health Workforce
SDMS Provider Management User Guide

Note: NPI and Address information must match the system’s records or the attempted updates will be rejected.
Should an update be required to an existing address, a new location would need to be added. If the existing
address is no longer valid a deactivation action would be required.

5.3.3 DEACTIVATE
The steps to deactivating existing locations are described below:
Step 1)
Select ‘Deactivate’ from the Action column
Step 2)
Select an option from the ‘Reason Code’ column
The example below illustrates the process of deactivating an existing location.

PROPERTY OF HRSA |BHW
17