Import Frame Inventory Data

Owned by Susannie Tiempo (Unlicensed)
Last updated: Jun 17, 2024 by Pierre Hebert
8 min read

 

inventory.png

 

Overview

One of the key features of Rx-Inventory is the ability to import your inventory data (from other sources/platforms) into the system. You can use this functionality to migrate existing frame data into Rx-Inventory, mass create new inventory records, or mass update existing inventory items.

Permissions

The relevant permissions for importing frame data can be found in Rx-Hub → Roles → (Specific Role) → Rx-Inventory. Learn more or get a refresher on how our User Roles and Permissions work.

To be able to import frame data, the user needs at least the Create and Update of the SKU permissions. Ideally, you would also grant them the Read permissions so they can view the frames that they imported.

 

Ideal Permissions for Importing Frame Data

Required Setup and Configurations

Before you start your import, review if your file has the required columns and if the values match the datatype and required values of fields. We encourage you to review the column definitions here to avoid issues during import.

It accepts most file types as long as they contain rows of delimited data using the delimiters we currently support. However, we highly recommend that you use CSV or files with .TXT file extentions when non-comma delimeter.

Importing Frame Item Data - Instructions

To import your inventory data into the Rx-Inventory platform, follow these steps:

Basic Importing Instructions

 

  1. Navigate to the Manage Inventory → Import section.

Frame Import Buttons

 

  1. Click on the Choose File button to initiate the import process.

Important: Rx-inventory accepts most file types as long as they contain rows of delimited data using the delimiters it currently supports. However, we highly recommend that you use CSV or files with .TXT file extensions when using non-comma delimeter.

The import functionality will not work with XML-based file extensions (e.g. .XLSX) and .DOCX

  1. Choose the file containing your inventory data and click Open to start the import.

 

  1. Select the appropriate delimiter format that your file is using. Currently, Rx-Inventory supports comma, pipe, semi-colon, and tab delimiters.

  2. You can also specify if your file is using a header row or not by toggling on the File Has Header Row checkbox. Turning off the flag will require you to map the columns to the correct fields during the import, otherwise, it will automatically map them if the headers have the same names as our fields.

  1. The platform will validate the file and display any errors or warnings that need to be addressed. It will also show other relevant information regarding the import

Errors - Must be fixed/addressed to continue with the import process. e.g. The file does not have columns or no matched columns yet for required fields.

Warnings - Import can be completed without fixing the data. However, there might be possible errors in the values in your import file for some the records. e.g We only accept “s or y” for the stock order status, if your file has other values other than those, they will not be imported.

Sample Mapping Columns Messages

 

  1. Match the columns in your import file to the available Rx-Inventory columns by selecting them from the Import As select field. You can only match one column from the import file to one column in Rx-Inventory.

Sample Column Matching

 

  1. After you matched all the columns you want to import, click on Next. This will bring you to a confirmation page section with an overview of the total number of records that will imported and included columns.

Import Confirmation

  1. You can proceed to Submit the import or go Back and modify it.

  2. Once the import is complete, you will see an overview of the successful and failed imports, a count of new versus updated records, and details of the failed imports. You will also have the option to export an Error Report CSV document and a Raw Data CSV of the imported records.

Import Results Overview

Back to top

 

Additional Importing Functionalities

Importing VisionStar Records

There is a flag that allows users during import to indicate if frame inventory information is from VisionStar. If the flag is checked, Rx-Inventory will automatically insert the trace name associated with the record using the Eye, DBL, Manufacturer, and Frame Name.

 

VisionStar Export Flag

 

Adding Full-File Values

You can add columns in your import that will have constant values for all records in your import file. You can set this by selecting the Add Field button on the Full-File Values section to display the fields for column selection and the value. For columns with string values, the ex, otherwise, you can create the value you want for the column. You can also add multiple full-value fields in one import transaction.

Full-File Values

 

Updating Existing Items

You can update the existing records on your Rx-Inventory database using the import functionality. Rx-Inventory will use either UPC or the frame identifier fields (name, brand, collection, mode, manufacturer, edge type, material, gender, color, eye, bridge, temple, and part type) to check if the frame record already exists. If the record exists, Rx-Inventory will update it with values from the import file, otherwise, it will create a new inventory record. Note that only the fields that are included in the import file will be modified.

To update existing records on import, on the Map Columns → Import Options section, tick on the Update existing items flag and proceed on the next import steps as is.

Update Existing Items Flag

 

Importing Parts/Miscellaneous Items

By default, Rx-Inventory imports a record as a Whole frame item, if you want to also include frame parts or miscellaneous items, you have to check the “Import file contains parts/miscellaneous items” flag. This will make “Part Type” a required column. Note that you will need to follow the valid values for part type (misc, front, whole, temple, bridge, side_shield, clip). Also, ensure that there are no blank values for part type in any of the records.

Import File Contains Parts/Miscellaneous Items

Back to top

UI Options

Show Matched Flag

  • When On - shows all the columns (matched and unmatched)

  • When Off - shows the unmatched columns only

Show Matched Flag - On
Show Matched Flag - Off

 

Allow replacing used fields Flag

  • When On - on the column card, all columns will be available as options in the Import As field. If the field is already matched, it will show as Allocated

Allow Replacing Flag - On

 

  • When Off - on the column card, only columns that are unmatched/unallocated will be available options in the Import As field

Allow Replacing Flag - Off

Back to top

LINK ~ Frame Import Template ~

Import Column Definitions

Frame Columns

Column/Field Name

Description

Data Type

Required?

Expected Values

Column/Field Name

Description

Data Type

Required?

Expected Values

 Name

 The name of the frame

Text

Required

 

 Material

 The material the frame is made from

Text

Required

 

 Color

 The color of the frame

Text

Required

 

 Eye

 The eye measurement

Integer

Required

Only accepts whole number

 Temple

 The temple measurement

Integer

Required

Only accepts whole number

 Bridge

 The bridge measurement

Integer

Required

Only accepts whole number

 Manufacturer

 The manufacturer of the frame

Text

Required

 

 Edge Type

 The edge type of the frame

Text

Optional

 

 Gender

 The gender that the frame is designed for

Text

Optional

 

 Requested Base Curve

 The requested base curve of the frame

Text

Optional

 

 Minimum Base Curve

 The minimum base curve of the frame

Text

Optional

 

 Prop 65?

 Whether the frame is prop65 or not

Text

Optional

 

 Brand

 The brand of the frame

Text

Optional

 

 Collection

 The collection of the frame

Text

Optional

 

 Model

 The model of the frame

Text

Optional

 

 Frame Class

 The class of the frame

Text

Optional

 

Status

The inventory status of the item (active, inactive, discontinued, on order, backordered, recalled)

Text

Optional

Active: a, active, y, t, true yes, 1
Inactive: inactive, I, 0, no, false, n, f
Discontinued: discontinued, d
On order: on_order, onorder, oo, o
Back Ordered: backordered, backorder, back_ordered, back_order, bo, b
Recalled: recalled, r

 A

 The horizontal width of each lens at its widest point

Float (with single decimal place)

Optional

 

 B

 The vertical height of the lenses at the widest point of the lens within the frame

Float (with single decimal place)

Optional

 

 ED

 The longest diagonal distance inside the frame edges in mm

Float (with single decimal place)

Optional

 

 Part Type

 What portion of a frame the model represents

Text

Required
if the Import file contains parts/miscellaneous items flag is checked

misc, front, whole, temple, bridge, side_shield, clip

 UPC

 UPC of the frame

Text

Optional

 

 Manufacturer UPC

 The manufacturer's frame UPC

Text

Optional

 

 Trace

 The trace of the frame

Text

Optional

 

 Standard Cost

 Standard cost of the frame

Float (with two decimal place)

Optional

 

 Average Cost

 The average cost of the frame across all receipts at all warehouses

Float (with two decimal place)

Optional

 

MSRP

 Manufacturer Suggested Retail Price of the frame

Float (with two decimal place)

Optional

 

Suggested Retail Price

 Suggested Retail Price of the frame

Float (with two decimal place)

Optional

 

 Price Group

 The price group that the frame is a member of

Text

Optional

 

 Last Used

 The last time this item was used by the system

Datetime

Optional

 

 Backorder Date

 The date and time the item is expected to be available again

Date

Optional

 

 Reorder Method

 The method used to reorder this item

Text

Optional

 

 Dynamic Days

 The number of days after item creation to switch from min/max to usage history reorder method

Integer

Optional

 

 Description

 An optional freetext description of the item

Text

Optional

 

 Web

 This flag tracks if the frame is displayed on the lab's website, if applicable

Boolean

Optional

y, t, true yes, 1

 Vision Star Item Number

 Item number from VisionStar

Text

Optional

 

Back to top

Warehouse Columns

 

Column/Field Name

Description

Data Type

Required?

Expected Values

Column/Field Name

Description

Data Type

Required?

Expected Values

 Stock Status

Whether or not the item should be kept on hand (stocked) or ordered when required (special)

Text

Required

Stocked: stock, y, t, true, yes, 1
Special Order: s, special, special_order specialorder, special_order_item, specialorderitem

Note: This is not case sensitive but should not have white spaces(trailing or leading).

 Quantity

The total stock in the warehouse

Integer

Optional

 

 Quantity Allocated

The total stock allocated for jobs

Integer

Optional

 

 Quantity In Process

The total stock picked for jobs

Integer

Optional

 

Quantity On Order

The total on open/released Purchase Orders

Integer

Optional

 

Quantity Backordered

The total stock that is backordered

Integer

Optional

 

 Bin

The bin this item is stocked in

Text

Optional

 

 Min

The smallest amount of stock that should be kept on hand

Integer

Optional

 

 Max

The largest amount of stock that should be kept on hand

Integer

Optional

 

 Vendor

The name of the Vendor that supplies the item

Text

Optional

 

 Lead Days

The number of lead days to use in the EOQ calculation

Integer

Optional

 

 Warehouse

 The warehouse this record is attached to

Text

Optional

 

 Cost

 The cost of the frame at this warehouse

Float (with two decimal place)

Optional

 

 Average Cost

The average cost of acquisition from the vendor (computed when receiving Purchase Orders)

Float (with two decimal place)

Optional

 

 Safety Stock

The safety stock to use in the EOQ calculation

Text

Optional

 

Back to top