There are times when you might not have the desire or ability to enter
data directly into All Orders. Fortunately, the application makes it easy
to import data into your main entity tables from a standard Microsoft
Excel spreadsheet file. Why might you want to do this? Here are a couple
You have a colleague who wishes to enter records,
but is currently in a remote location with no access to the All Orders
You have records stuck in an old database application.
Since most data-intensive applications support export to Excel, this is
a handy way of converting the old data without having to retype everything.
To import some data from Excel, choose File
> Import > Excel, and then pick the data type you wish to
import from the submenu that appears. You have five options:
Of course, in the file that you import, you'll need headers in place
to let All Orders know which columns to import into which fields. Fortunately,
you have a template .XLS document for each of the data types just waiting
for you in the Import folder within
your All Orders 4 install directory.
Feel free to copy these documents and use them for adding your data.
Once you've chosen a data type, you'll be prompted to choose a file.
By default, All Orders will take you to the aforementioned Import folder
as well as auto-select the file based on the data type you chose. Click
Open to import the file's contents.
Importing Lists can be used either to add or modify records. Only fields
that need to be modified need to be in completed in the Excel template.
For example, if you want to change all the prices, complete the FullName
and Price Columns of the Excel template. If you wish to delete the contents
of a field without replacing it put \d
in the cell.
You should not delete any columns or individual work sheets.
The sheets in Excel templates provided mirror table structure of All
example you will notice that the ItemInventory.xls (used to import all
types of items) has several sheets. The
main sheet Item is the starting point. You
must enter values in this sheet first before any other sheet will be considered.
example if you want to add 2 vendors for a particular item, you
first enter the Item's 'FullName' and Group on the Item sheet.
Then on the Vendor sheet enter the Item twice plus each vendor once.
that the Vendors listed must already be in All Orders.
Importing bill of materials and/or kits is done in the same way. For
example in the kits.
To create a kit you start with the Item worksheet as above, then populate
the Kit Components worksheet repeating the item full name for each kit
Finally populate the Kit Selections work sheet as follows:
Transaction Excel files also mirror the All Orders table structure.
for example the InvAdjustment.xls which will create a Qty Adjustment in
All Orders. It
has 3 worksheets.
for the top portion of the Qty Adjustment.
InventoryAdjustmentItem: Used for each item on to be included in the
to import lot or serial #s if applicable.
ControlRef in the InventoryAdjustmentHeader represent 2 adjustment.
is repeated for each item that will be included in the adjustment in InventoryAdjustmentItem.
also that for ControlRef 1, Item YY45-15 is repeated twice because I wanted
to make the adjustment to 2 separate bins.
If an item has lot or serial #s, you will need to complete LotSerialNumbers.
case the LotControlRef corresponds to each Lot or Serial # entry. LotControlRef
is used also to link toLotSerialNumbers to InventoryAdjustmentItem
Call to speak with a NumberCruncher Solutions Consultant at: