Setting Up the Spreadsheet for Data Import
1 Template and Initial Setup
· Template Source: Begin by using the download template available directly from the website. This template provides best practices, column legends, and necessary structure.
· Skipping Products: For material import, the Products section of the template can be skipped.
· BOM Section: The Bill of Materials (BOM) section is crucial. In the demonstration, this section was set up as a Power Query table in Excel, which pulls in and aggregates data from a CSV.
2 Key Fields and Data Population
The template includes several new fields critical for the Request and Fulfill Queue:
· Facility Identifier
· Warehouse
· Bin
· Delivery Location
The easiest way to populate data into these fields is by using their actual names. Alternative methods, as noted in the instructions tab, include using the sequence identifier or a generated unique ID.
2.1. Sourcing Field Names from INNERGY
To obtain the correct names for these identifiers navigate:
Facility Name:
1. Go to Company Profile.
2. Locate the desired facility. The full name is used.
Warehouse Name:
1. Within the selected facility, identify the warehouse (e.g., main warehouse). Use the full name, not the short name.
Bin Location:
1. Access the details inside the warehouse. The bin name can be copied and pasted.
Delivery Location:
1. This is a dictionary field.
2. Go to the Configuration module.
3. Select Dictionaries.
4. In the General tab, search for delivery to find material delivery location.
5. Any of the listed names (e.g., CNC) can be used.
3 Power Query Process for Data Aggregation
The process for setting this up using (e.g., from Winstore, StoreTech, Intellistore).
Power Query is:
1. In Excel, go to the Data tab.
2. Select Get Data > From Text/CSV.
3. Navigate to and select the CSV file containing the material data.
4. Open the Power Query Editor.
5. Promote headers: Convert the first row of data into column headers.
6. Change type: Adjust data types for columns (e.g., to number).
7. Reorder: Arrange columns as necessary.
3.1 Repeatability and Live Data
A key advantage of Power Query is its repeatability:
· You can set up one sheet to bring in new data.
· The source can be configured to pull from a specific directory.
· Reloading the query makes the data live.
· By updating the source CSV (e.g., adding new information), refreshing the query in Excel, and saving, new data can be pushed to INNERGY.
· This allows for a streamlined workflow where output reports from systems like StoreTech, with consistent naming, can be placed in a designated directory, and then Excel's refresh all updates the data for import.