Location Import Tool

Audience:

Labels:

None assigned

"Location Update" allows for the bulk change of location for assets in the statuses of “In Repair,” “In Use,” and “Available” to a different “Room,” “Staff,” or “Student.”

  1.  Navigation:
  2. Admin View
  3. Quick Links
  4. Location Update

"Location Update" is performed through an Excel file import to mass collect or distribute assets.
This feature is only accessible for "Admins" in the "Admin" view. All other user roles will not have access to "Location Import" to collect or distribute assets in bulk.

Review this entire article prior to attempting to use the "Location Update" tool.

Navigating to the Location Import Tool

"Admin Admin" users can update tag location in bulk with the “Location Import” too, located in the left navigation panel under "Admin Tools." No other user roles will see this additional menu option.

location

Clicking Location Import will open a modal pop-up to start the import process.

location

"Import Details" on the left of the modal describes important details of this feature.

  • Import Type: Will always be set to "Location Import"
  • Required Fields:

import

  • Support Status Changes: Will accept changes in status for “In Repair,” “In Use,” and “Available”
  • Tags in the following statuses are not allowed to be bulk location updated: “Lost,” “Stolen,” “Pending Lost,” “Pending Stolen,” and all “Transfer”

Preparing your .XLS or .XLSX Import File

A template file is available from the modal to download and fill with the location change information. Access the template by clicking on the here hyperlink.

import

The template file includes columns A-F, and row 1 includes headers of the required fields for "Location Import."

import

Note: If you are using your own spreadsheet, field names and sequence must be as show in the example above.

To import your prepared Excel file to Asset Management, use the following steps:

  1. Select Browse to locate and select your file.
  2. Click Import.
    1. System validations are run. If there is an error in your file, you will receive a red validation error. All errors must be addressed before the system can start to process the import.
    2. The file is added to the "Import History" grid, with the most recent file added at the top.
Note: The import time varies depending on the number of records, complexity of the updates, and other processes occurring in the database at that time. The file processes in the background so you can continue to work on other tasks in the system.
Validation Type Action

File length > 100 characters

  • Includes file name + file extension
  • e.g., 95 character file length + .xlsx = 100 characters
Error message: "File name maximum length is 100 characters long."
File length <= 100 characters File is processed.

File with only columns and no records

file

Error message: "No data found in the 'SY Location' sheet."

File with columns and no record with format

file

Error message: "No data found in the 'Sheet1' sheet."
File with non-matching column titles Error message: "The columns are not named correctly or are in the wrong order."

File with invalid formats

  • e.g., .txt, .ppt, .docx, .jpg, .pdf, .csv, .png, .doc
Error message: "Invalid file format."

Import History: Error and Success Files

As an "Admin," you have access to detailed error reports and a successful task file. These files provide valuable insights into the outcome of the "Location Import" process, enabling you to address any errors and providing a record of successful task completion.

import

Error Report

At the completion of the "Location Import" process, if any records are an issue, the server generates an error report in the same format as the import file This report will contain an additional column G, “Exception Reason(s)”. "Exception Reasons" are defined in detail later in this article.

Note: Each validation error will be reported individually, following the same format as the data import tool. If multiple validation errors occur for a single record, each error will be comma-separated in the error message. For example: "Location ID does not exist, Tag does not exist, Site ID is not active."

validation

Correction and Re-upload

With the error report in hand, you can easily identify the specific errors that occurred during the import. This allows you to make the necessary corrections to the file and repeat the upload process, ensuring accurate and successful bulk location updates. Please remove the last column of the error file before retrying, and make sure your file is still in .xls format.

Successful File

Clicking the Green Page Icon, located under "Actions," opens the "Successful Task File." This file serves as confirmation of a successfully completed location update file import. It provides you with a record of the asset details, serving as a reference for future tracking and verification purposes. If there have been no successful location changes, a "Success" file will not appear, only an error file.

successful

Exception Reasons

Tag and/or Serial (required column in file)

Validation Type File Type Error Message
Blank/missing Error file Tag or Serial must be present.
Invalid
  • Tag is null and serial is invalid
  • Tag is invalid and serial is valid
  • Tag is invalid and serial is invalid
Error file Tag does not exist.
With special character "
  • Error file - Tag
  • Error file - Serial
  • Location Import does not support double quotes for Tag.
  • Location Import does not support double quotes for Serial.
Same "Serial" for two "Tags" in Asset Management
  • Tag is null and serial is valid
Error file Serial must be assigned to an active tag.
"Tag"/"Serial" is archived Error file Tag is archived.
"Tag"/"Serial" with "Component," with and without accessories Error file Tag is a component of Parent Tag (####).

"Tag"/"Serial" with restricted funding source

Error file Tag has restricted funding source.
"Tag"/"Serial" duplicate within file
  • Error file - Tag
  • Error file - Serial
  • Tag (####) is duplicated.
  • Serial (###) Serial is duplicated.
Duplicate "Tag" and "Serial" in different rows of same "Tag" Error tag The asset for this Tag or Serial Number is found more than once.
With valid "Tag"/"Serial"
  • Both tag and serial are valid
  • Tag is valid and serial is null
  • Tag is valid and serial is invalid
  • Tag is invalid and serial is valid
Success file
  • Record is processed.
  • "Status," "Location Type," and "Location ID" is updated as provided in imported sheet.

Location ID (required column in file)

Validation Type File Type Error Message
Blank/missing Error file Location ID is missing from file.
Invalid/Does not exist at any "Site" Error file
  • Room - Room Number (###) does not exist for site (###).
  • Staff - Location type: Staff ID (###) not found.
  • Student – Location type: Student ID (###) not found.
With special character " Error file
  • Room - Location Import does not support double quotes for (Location ID).
  • Staff - Location Import does not support double quotes for (Location ID).
  • Student - Location Import does not support double quotes for L(Location ID).
Inactive – Same "Site"/"Warehouse" Error file
  • Staff ID (###) is not active.
  • Student ID (###) is not active.
  • Room Number (###) is not active.
Inactive – Different "Site" Error file
  • Room Number (###) does not exist for site (###).
  • Staff ID (###) is not active.
  • Student ID (###) is not active.
Inactive – Different "Warehouse" Error file
  • Room Number (###) does not exist for warehouse (###).
  • Staff ID (###) is not active.
"Staff"/"Student" only at Site A, not at Site B Success file
  • Staff/Student from (Site #) is created at (Site #2).
  • Tag imported will be reflected in both Sites A and B -Staff/Student.
  • If Tag imported from (Site #2), tag appears in (Site #) & no longer remains within (Site #2).
  • If Tag imported from (Site #), tag remains within (Site #2)

"Staff"/"Student" common in two Sites, A and B

  • "Staff"/"Student" at both Site A and Site B, "Tag" from Site A or B
Success file Tag from Site A or B is displayed at common Staff/Student present at both Site A and B.

Site ID (required column in file)

Validation Type File Type Error Message
Blank/missing Error file Site ID is missing from file.
Invalid Error file Site ID does not exist.
With special character " Error file Location Import does not support double quotes for Site ID.
Inactive – Site Error file Site ID is not active.
Inactive – Warehouse Error file Warehouse ID is not active.

Location Type (required column in file)

Validation Type File Type Error Message
Blank/missing Error file Location Type is missing from file.
Invalid – Site/Warehouse
  • Error file – Site
  • Error file – Warehouse
  • Location Type is not supported. Supported Location Types are "Room," "Staff," and "Student."
  • Students cannot be assigned to a warehouse.
With special character " Error file Location Import does not support double quotes for Location Type.

Status (required column in file)

Validation Type File Type Error Message
Blank/missing Error file Status is missing from file.
Invalid Error file Tag Status is not supported. Supported Status are "In Use," "Available," and "In Repair."

With any special character

  • Case 1: Available~'"!@#$%^&*()_+{}:"_;',./<>?`[]
  • Case 2: Avail"!@#$%^&*()_+{ab}:"_;',./<>?`le[]
  • Case 3: Availab~'"!@#$%^&*()_+{}:"_;',./<>?`[]lee
  • Case 4: InValidRemove~'"!@#$%^&*()_lll+{}:"_;',./<>?`[]g

1, 2, and 3: Success File

4: Error File

1 and 2: Special characters will be removed first and if status is valid, record is processed

3: Record is processed

4: Tag Status is not supported. Supported Status are "In Use," "Available," and "In Repair."

Not Allowed Status for current location import scope

  • Not allowed status -> Auctioned
  • Not allowed status -> Disposed
  • Not allowed status -> Lost
  • Not allowed status -> Recycled
  • Not allowed status -> Return to Vendor
  • Not allowed status -> Sold
  • Not allowed status -> Stolen
  • Not allowed status -> Surplus
  • Not allowed status -> Used for Parts

Below statuses will never be part of Location import scope

  • Not allowed status -> Pending Transfer
  • Not allowed status -> In Transit
  • Not allowed status -> Pending Lost
  • Not allowed status -> Pending Stolen
Error file Same exception for all: Tag Status is not supported. Supported Status are "In Use," "Available," and "In Repair"; Tag does not exist.

Tag already in not allowed status

  • Tags in status Auctioned.
  • Tags in status Disposed.
  • Tags in status Lost.
  • Tags in status Recycled.
  • Tags in status Return to Vendor.
  • Tags in status Sold.
  • Tags in status Stolen.
  • Tag in status Surplus.
  • Tag in status Used for Parts.
  • Tags in Pending Transfer.
  • Tags in In Transit.
  • Tags in Pending Lost.
  • Tags in Pending Stolen.
Error file
  • Tag is in status of Auctioned.
  • Tag is in status of Disposed.
  • Tag is in status of Lost.
  • Tag is in status of Recycled.
  • Tag is in status of Returned to Vendor.
  • Tag is in status of Sold.
  • Tag is in status of Stolen.
  • Tag is in status of Surplus.
  • Tag is in status of Used for Parts.
  • Tag is in status of Pending Transfer.
  • Tag is in status of In Transit.
  • Tag is in status of Pending Lost.
  • Tag is in status of Pending Stolen.

Tag status is "Available" for "Staff"/"Student"

  • Staff to Student
  • Student to Staff
  • Staff to Staff
  • Student to Student
Error file
  • Tag Status is not supported for Location Type of Staff.
  • Tag Status is not supported for Location Type of Student.

Overall Record Validation

Validation Type File Type Error Message
All columns blank/missing Error file
  • Tag or Serial must be present.
  • Site ID is missing from file.
  • Location ID is missing from file.
  • Location Type is missing from file; Status is missing from file.
Invalid Error file
  • Location Type is not supported. Supported Location Types are "Room," "Staff," and "Student."
  • Tag Status is not supported. Supported Status are "In Use," "Available," and "In Repair."
  • Tag does not exist.
  • Site ID does not exist.
With special character " Error file

Location Import does not support double quotes for Tag.

Location Import does not support double quotes for Serial.

Location Import does not support double quotes for Site ID.

Location Import does not support double quotes for Location ID.

Location Import does not support double quotes for Location Type.

Location Type is not supported. Supported Location Types are "Room," "Staff," and "Student."

Tag Status is not supported. Supported Status are "In Use," "Available," and "In Repair."

Tag does not exist.

Site ID does not exist.

Note: First quotes are removed from the columns and then the exception is displayed within error file.
Duplicate entire record in file Error file Record is duplicated.

Record already exists in database (or) was already imported earlier

i.e., A "Tag" or "Serial" already exists within Asset Management with the same status, location ID, site ID, and location type.

Success file Record is skipped for validation and appears in success file.

Tag and Serial Validations

"Tags" and "Serials" must be linked to a single, active asset. At least one of the "Tag" or "Serial" columns must contain a value, although both can contain values. If "Tag" is null and "Serial" is specified, the row must be marked as an exception if that "Serial" is linked to multiple assets.

Note: Serial numbers for assets are allowed to be duplicated and there is no validation requiring them to be unique. If only the serial number is used in the "Location Import" file and there are multiple matching assets in your data, an error message will appear.

Duplicate Detection

If a "Tag" or "Serial" is present multiple times in the import file, all occurrences of those records will be marked as duplicates. However, if the entire row is identical, only the first occurrence will be retained, and the subsequent fully identical rows will be marked as duplicates.

Parent Tag and Component Tag Handling

When the location of a parent tag is changed, the component tags will be assigned accordingly. However, the location of a component tag cannot be changed in the bulk upload, and the error message will indicate: "Tag is a component of Parent Tag (###)."

Location and Site Validations

The "Location ID" and "Site ID" must be linked to each other in the database and must be active. Additionally, the status specified must be valid for the "Location Type" (e.g., "Room," "Staff," or "Student").

Status Change Error Handling

If the asset's current status is "Pending Transfer," "In Transit," "Pending Lost," or "Pending Stolen," the status change error will display as: "Unable to change status for tag in status of [status name]. Row [Row Number]."

Warehouse Location Handling

The bulk location change feature is not applicable to warehouse containers. Warehouse locations can only be assigned to staff and rooms, not to students. Untagged inventory management is not included in this import process.

Department Tag Assignment

"Admin Admin" users, who have access to all departments and tags, can assign department tags without any restrictions.

Component & Parent Tags

  • Parent Tag: When the location of a parent tag is changed, the system will automatically assign the new location to all associated component tags. This streamlined process eliminates the need for manual adjustments, allowing for efficient management of parent and component tags.
  • Component Tag: In the bulk upload process, the location of a component tag cannot directly be changed. If an attempt is made to change the location of a component tag, an error message will be displayed to the client: "Tag is a component of Parent Tag (###)." This message indicates that the location change should be performed on the parent tag instead.

Restricted Funding Validations

When performing the bulk location import, if a tag has a restricted funding source that prohibits its movement to a particular site, the system will generate an error.

The error message displayed for such case will read: "Tag has restricted funding source."

The process for validating the tags against the funding source and site matches the process for "Quick Transfers."

This validation enhancement helps maintain data accuracy and adherence to funding restrictions while performing bulk location changes. By preventing tags with restricted funding from being moved to incompatible sites, you can ensure compliance and avoid potential complications.

Note: Missing leading zeros will cause an error. When working with Excel files, it is important to note that leading zeros may be dropped by Excel. This behavior is not specific to our software but rather an inherent characteristic of Excel. To ensure the preservation of leading zeros in your "Tag," "Serial," or "ID" fields, we recommend following the steps outlined below:

  • Using Custom Number Format: Excel provides a custom number format feature that allows you to display leading zeros. You can apply this format to the specific cells or columns where leading zeros are required. Microsoft provides detailed instructions on using a custom number format to display leading zeros in Excel, which can be found in their support documentation, "Using a Custom Number Format to Display Leading Zeros."
  • Opening Files with Leading Zeros: If you need to open a file that contains leading zeros, such as a downloaded file from our software, you can follow these steps:
  1. Save the downloaded file in .txt file format.
  2. Open Excel and choose the File menu.
  3. Select Open and navigate to the location where you saved the .txt file.
  4. In the "Open" dialog, select the file and choose the Open button.
  5. The Text Import Wizard will appear. Select the appropriate options, ensuring that you choose Tab as the delimiter if the file is tab delimited.
  6. Click Finish to import the file into Excel. This process will help retain the leading zeros.
  • Please keep in mind that whenever you export data from our software into an Excel or CSV file, the leading zeros may no longer be present due to Excel's default behavior. To preserve the leading zeros, it is crucial to follow the steps outlined above.