Structured Data Export Example
Proplanner’s Pivot Grid Module has powerful capabilities for searching across the process data library and generating user-defined pivot tables. As shown in a post from the end of July, this tool can be used to help answer questions such as the total MODAPTS time per station or per line for a specific product configuration. The pivot table can then be copied out as an Excel file for whatever purpose is needed.
On the other end of the life of data within Proplanner is the Import Data module. This allows for product (Item, BOM) and process (Routings, Activity Lists, Consumption, Resources) data to be imported into the system. In production environments, I tend to see this used during project startups to transition data from legacy systems into Proplanner, prior to utilizing backend integrations.
There are other cases though, such as one that I recently encountered where a company wanted to overhaul its resource mapping scheme with new resource IDs and shift local resource attributes (e.g. torque settings) from the activity to the resource library. Given there were many thousands of activity and resource records and associated attributes, a method was needed to extract the existing data, transform in Excel, and reimport back into Proplanner.
The Pivot Grid Export module can be used for this data extraction process the Import Data module can bring it back into the system. While we would certainly rather the data be edited using the Mass Update module, in cases like this Excel is the way to go.
To get started, you must first establish the search conditions. For this example, we want to pull the entire Proplanner process library into the results, so a search for all routings with an ID not equal to 0 is used.
Next, the fields to be used in the export table will be established. The Import Templates can be used as a guide to set the minimum fields needed. I will actually perform two separate data exports. The first will be a list of all of the resources referenced by at least one activity in the library. This is an interesting data set itself and demonstrates the power of the Pivot Grid Export module to generate custom reports.
The second export takes a little more set up in the field selection process. This is a great opportunity to copy the header fields from the Excel import template file into the field selection control. That will let you skip a few steps and ensure that the field names are ready to go for the Import Data Module.
The final step in data preparation is to layout the fields in the pivot table. We will first add the routing, operation, activity, and resource fields (in that order). You may notice that there is a field called Row Num that is added to each pivot table data set. This is a unique identifier that also enables this whole export-import process to function!
Adding the Row Num field as the first column (far left) causes the rest of the table to fill out with the appropriate record. Note that we do not actually create a ‘pivot’ with any aggregate statistics on the data, we are simply using a tool for a secondary purpose. In the case shown here, we are joining six different tables from the database without writing a single SQL statement or building a data connector to a BI tool.
Selecting File > Export to Excel > WYSIWYG will result in a file that is ready to be manipulated and then loaded back into the Import Data module.
As always, let us know at support@proplanner.com if any help is needed.
If you'd like to keep up on the latest Proplanner news, follow us on Facebook, LinkedIn, and subscribe to our YouTube channel.
Join our mailing list to be informed of the newest blog posts.
