Spreadsheet Import – Formula Master Records

 

Spreadsheet import is supported for raw materials, formulas, packaging, BOMs, customers, and vendors.  Only top level data can be imported, i.e. no raw properties or formula details (ingredient & quantities) are supported via this interface at this time.

 

To begin, select the data you want to import:

 

·      (20) Formulation Maintenance and Analysis

 

·      Click on the <<Find>> Button            

 

·        In the <FIND> window, click the wrench                  

 

 

·      Check off the columns you wish to import.  Make sure you check off each data field that you plan on importing

 

·      All the columns you selected will be displayed. 

·      If you are importing new data, we want the grid to be blank.  To do this, we can enter a filter that will not match any existing items.  For example, you could type in “Add Only” under the Item Code             

 

·      If you plan on updating existing records in Formulator, then you want the grid populated with your items.  In this example, we are only updating active items:                  

 

 

 

Copy the Grid to Excel:

·      Select Edit – Copy to Clipboard:                    

 

 

·      Open Microsoft Excel and paste:                   

 

·      The information from the grid will then appear in Excel                     

 

·      Add or modify data in Excel as required using as many rows as needed.  Do NOT leave space between rows in the formula code column.  The import will terminate when it hits a blank code.  Save the spreadsheet. NOTE: The data to import MUST be in the first worksheet of the workspace.

 

 

Import the data into Formulator:

·      (20) Formulation Maintenance and Analysis

 

·      Click on File à Import                        

 

 

·      Select the spreadsheet file you edited.  The import routine will examine the spreadsheet, locate importable rows, and display something like the following:  

 

 

 

·      Check the columns you wish to import.  Also, if you want to add new records, click the “Insert new records if not on file” checkbox.  Click “Import” to import the data.

 

·      NOTE: Not all columns in the formula <FIND> windows can be re-imported.  These columns include but are not limited to:

 

-       Physical properties

-       Equations results

-       QC Standards.

 

 

Importing Formula Details

 

You can also import formula detail lines via spreadsheet.  Formula Detail lines are imported in a fashion similar to importing the formula master records.  The difference is that the formula master will only by be listed once, along with the first detail line, and subsequent detail lines will only contain detail data.  The next formula master record will signal the end of the start of a new formula.

 

Here is an example of a spreadsheet set up for formula detail import:

 

 

Importing the above spreadsheet will display the following window:

 

Note that the formula detail columns are pre-checked and highlighted in blue. 

 

Only raw material type ingredients can be imported at this time.  Instructions, sub-totals, and in process QC must be entered manually.

 

The following formula detail columns are supported:

 

Item Code

Quantity

Units

Loss %

 

Item Code: The ingredient’s item code. 

Quantity: Amount of the ingredient.

Units: Units of the ingredient.

Loss %: Line loss, as a percentage from 0 to 100.