Mappings for Data Pull

This document was a significant rework of an outdated guide on building data mappings between an ERP system and Power Pick software. Recognizing that a key audience—warehouse administrators—was often new to JSON, my primary objective was to make this a strong, intuitive learning piece. I achieved this by including a dedicated JSON primer and full-length, annotated examples, ensuring the document was accessible and useful for both technical and non-technical users. While this translates into a longer learning piece, the content’s clear structure and real-world examples enable users to confidently configure integrations on their own.

Data Pull creates and runs integrations that transfer data from external services (such as your enterprise resource planning system) to Power Pick. To successfully populate Power Pick with data, you must create a mapping: a set of instructions that tell Data Pull how to translate and transfer information from the connected service to the appropriate fields within Power Pick.

For example, Data Pull can retrieve requests for parts from your enterprise resource planning (ERP) system and create orders in Power Pick. Power Pick will prompt warehouse staff to fulfill the order. The mapping tells Data Pull how to fill in the information so that Power Pick knows the material, the quantity of that material, and the deadline for fulfillment for that order.

Think of Data Pull’s mapping tab as a guide for interpreting the data returned in a request (typically JSON) from a source system, saving instructions for how to use those values to fill in data for orders or materials as needed. This is an essential part of creating an integration: without a mapping, Data Pull can’t create or delete data!

If you’re not familiar with JSON, here’s a short primer:

JSON stands for JavaScript Object Notation, a common data format that many programming languages can easily interpret. For example, many application programming interfaces (APIs) commonly respond to requests with a JSON body. A pair of braces, {}, denotes a JSON object. Pairs of keys and values (key: value) represent individual properties of an object, with commas separating pairs within a JSON string. Values can be strings, numbers, boolean values, lists, or JSON objects.

Square brackets, [], denote lists and commas separate objects in the list. The ItemPath API documentation provides sample JSON responses if you are curious to see how this looks in practice. There are also examples of JSON later in this article!

Remember that Data Pull has four integration types:

1. Create Orders
2. Delete Orders
3. Create Materials
4. Delete Materials

Depending on the integration type, you will need to construct different mappings. The general workflow for constructing a mapping is to:

1. Identify the keys or fields from the data provided by the source system. Consult with the source system’s documentation for a guide or data dictionary if you need more information.
2. Identify the matching columns from the Power Pick system. The API and Report references are good guides to values in the corresponding Power Pick records.
3. Enter your mapping into Data Pull.
4. Test your mapping with sample JSON.

In order for Data Pull to create an order or material, all required values must be in your mappings. For Data Pull to delete an order or material, you must provide enough information to identify the orders and materials that will be deleted (the record ID in Power Pick).

Create a mapping

After clicking on the Mapping tab, you’ll see the following:

The main region shows you the existing mappings created for this integration. A basic mapping will typically include the key (from your external system) and the column that it’s mapped to in Power Pick’s database.

To add a new mapping, navigate to the bottom of the page. Select the Mapping Type, provide any keys, columns, or values for the mapping, and Save.

Typically, programs that read JSON don’t expect the keys to come in a specific order. For basic mappings, you don’t need to worry if the order corresponds with the JSON body from your ERP, or the corresponding JSON object for Power Pick.

Data Pull supports the following mapping types:

Hierarchical structures

While the order of your mapping doesn’t matter for most cases, there is one case where the mapping interface expects a particular order. JSON supports data that has a hierarchical structure: a JSON object can include other JSON objects or arrays as values.

Here’s an example of a JSON object with a nested hierarchy, where request_details is a nested object, and items is an array of JSON objects:

{     
    "request_id": "12345",     
    "request_details":     
        {     
        "request_name": "Parts Request for Customer A",     
        "request_direction": "OUT",     
        "request_due_date": "2024-04-22T19:11:45",     
        "request_description": "Replacement parts for general assembly repair order."     
        },     
    "items": [     
        {     
        "item_name": "Part A",     
        "item_quantity": "5",     
        "item_lot": "114",     
        "item_description": "Part A is used in conjunction with Widget B"     
        },    
        {     
        "item_name": "Widget B",     
        "item_quantity": "5",     
        "item_lot": "114",     
        "item_description": "Widget Bs support Part A in the general assembly"     
        }    
    ]    
}  

In particular, if you add a grouping, order, or order line mapping, Data Pull will expect that there is a hierarchical structure. To instruct Data Pull to expect this nested structure:

1. Create a mapping for the parent object. For example, if you select Grouping from the drop down, indicate the key for the object, and Save.
2. Add the nested mapping by selecting the type, specifying any keys, columns, or values, and Save.
3. The mapping will appear in your table of mappings. Since this is from an object lower in the hierarchy, click and drag the object beneath the parent object, then drag it slightly to the right. It will appear indented beneath the parent object.

Any mapping indented to the right beneath another mapping is for a child object in the hierarchical JSON structure.

Advanced mappings

The advanced mapping type allows you to manually enter strings and perform concatenation operations using multiple keys. For example, you might want to add a prefix to order names created by this integration along with the key (DataPull - 53901):

1. Select Advanced as the mapping type The input fields will look similar to a column mapping (Key and Column).
2. Select the column you would like to map the advanced value onto.
3. To add the integration name as a prefix, type the following into the key textbox: "DataPull \- " and press enter (include the quotation marks). This will appear similar to a key, but the double quotes will indicate that this is a string. The textbox will allow you to enter additional strings or keys as needed.
4. Specify the ID/order name from your ERP as a key, for example, orderID.
5. Save.

You can also use the advanced mapping option to enter a fixed value for all records created by your integration.

If you’d like to manually set a column in Power Pick to a string, select Advanced as the mapping type, type your text in double-quotes, “text“, and press enter. Then select the column from Power Pick, and Save. For example, you could do this to include a message for the Info2 field that this order was created by this integration:

You can remove strings or keys from an advanced mapping with the x icon beside the value in the Key textbox.

Text mappings

You may want to store a value associated with a key as part of the request made by your integration, and store that as a text value. The value will be available in ItemPath’s logs or you can return as a confirmation (a response sent by Data Pull) to the source system.

Like other mappings, select Text from the dropdown menu and specify the key from the source system. Then enter a custom text value for that key and Save. When the integration runs, the value will be part of the call and available in Data Pull’s logs so you can refer to it with confirmations.

Example: Create Order Mapping

Suppose you are working with an ERP system where you can request a filtered list of requests via its API. When testing with your ERP system, you’re able to get a sample list of outbound requests (where "request_direction": "OUT") with medium priority through a combination of filters. Typically, this request corresponds to an address, or URL, which you’ll be able to enter in your Data Pull integration as you manage its settings. When you create the integration, make sure to specify that all orders created by this integration are “Picks” (requests for items leaving your warehouse).

Here’s a sample of the data you might receive:

{    
   "requests":[    
        {     
        "request_id": "12345",     
        "request_details":     
            {     
            "request_name": "Parts Request for Customer A",     
            "request_direction": "OUT",     
            "request_due_date": "2024-04-22",     
            "request_description": "Replacement parts for general assembly repair order."     
            },     
        "items": [     
            {     
            "item_name": "0000630425",     
            "item_quantity": "5",     
            "item_lot": "114",     
            "item_description": "Part A is used in conjunction with Widget B."     
            },    
            {     
            "item_name": "0000630425",     
            "item_quantity": "5",     
            "item_lot": "114",     
            "item_description": "Widget Bs support Part A in the general assembly."     
            }    
            ]    
        },    
        {     
        "request_id": "12345",     
        "request_details":     
            {     
            "request_name": "Restock for DC 8129",     
            "request_direction": "OUT",     
            "request_due_date": "2024-04-22T19:11:45",     
            "request_description": "Parts for installation of hopper module."     
            },     
        "items": [     
            {     
            "item_name": "0000630425",     
            "item_quantity": "5",     
            "item_lot": "114",     
            "item_description": "Washer assemblies."     
            }    
            ]    
        }    
    ]       
}

As you review the body of the response, make some basic notes for planning your mapping. Here are some important properties to note:

• The body of the response is an array of request objects. They consist of an ID, a request details object, and a list of items that comprise the order.
• The request stores key information as a nested object.
• Items play a similar role to order lines.

Start by looking at ItemPath’s API reference materials for Create Order requests and compare that to the fields with your source data. As you go, create a table or spreadsheet that includes all required fields and add any additional fields you would like to have completed in Power Pick.

Using the API reference, you might even create a hypothetical JSON object for your order:

{    
    "name": "Example Order Name",    
    "directionType": "1",    
    "order_lines": [    
        {    
            "materialName": "0000630425",    
            "quantity": 5,    
            "lot": "114",    
            "Info1": "Order line description"    
        }    
    ],    
    "deadline": "2024-04-22T19:11:45.590000",    
    "Info1": "Order description"    
}

The resulting table would look like this:

This might feel a little intimidating, but this table becomes a solid guide to constructing your mapping. Here’s how you can complete the mapping for your integration, step by step:

1. Select the Order mapping type. This tells ItemPath to parse the body of the response as an array of orders.
2. Map request_id to the name value:
   1. Select Column and enter request_id into the Key textbox
   2. Select Order > Name from the column dropdown menu.
   3. Save your mapping.
3. Map values from the request_details object:
   1. Select Grouping from the dropdown menu.
   2. Enter request_details as the key.
   3. Save.
   4. For each of the values from the request_details object that can be mapped using the Column type, repeat step 2 with new values (request_due_datemapped to Order > Deadline, request_description to Order > Info1), with each value indented to the right beneath request_details.
4. The next row in the planning table is for the array mapping for items to order lines. Select Order Lines from the drop down, and enter items as a key.
5. Each of the values from the items array can be mapped with a column mapping, as in step 2. Repeat step 2 for each pairing detailed in the table that maps a key to an order line value. To indicate that these are keys from objects in the order lines array, you will need to drag the added row to the right beneath the items mapping.
6. ItemPath saves your progress as you add and rearrange mappings. As you complete the table, you’ll build your mapping. You can then proceed to test your integration.

Best practices for mappings

Creating mappings between your systems can be the most labour intensive part of building integrations. It involves careful planning to drive outcomes in Power Pick and you’ll need to understand how to relate your source system to Power Pick. These best practices and tips will help you build your integrations.

Recommended values

When planning your integration, you can figure out what data you need when creating records by checking with the API documentation. Starting with all required columns is good, but you can review the recommended values to build a list of what data you would like to include on your orders and materials.

For orders, consider including the following columns:

• Name (required)
• Direction Type
• Order Lines:
  • Material Name/ID/Code (you must include one of these three fields)
  • Quantity (required)
  • Lot
  • Info1: Typically text describing the order line
• Priority
• Deadline
• Info1: Typically text describing the Order

For materials, consider including the following columns:

• Name: The name of the material (required).
• Codes: An array of codes associated with the material, for example, part lookup numbers.
• Info1: Often a description of the part.
• Info2: Often any notes about this material
• Unit of Measure: Unit used by this material.
• Material Property ID: A material property is a record in Power Pick that functions as a rule governing how a material is treated in orders or other interactions with the Power Pick system.

For easy reference for required values and data types, use the following pages from the API docs:

• Orders
• Materials

Retrieving and managing data

The exact details of getting information from your systems for constructing mappings varies from system to system. Developers often use curl to get a sample response body, which you can then use to test your integration. You should also consult with the documentation for your ERP system, identifying what resources are available are an important part of carrying out an integration.

When you’ve retrieved a sample, it might be a single line of condensed JSON. You can use tools such as VS Code with the Prettify JSON extension to automatically convert the JSON from a single line to a format easier to read.

Here’s a simple example of a JSON body as a single line:

{ "request_id": "12345", "request_direction": "OUT"}

With Prettify JSON, you can use your command palette (ctrl-shift-P > Prettify JSON) to enhance the readability of your data. The result will be:

{     
	"request_id": "12345",     
	"request_direction": "OUT"    
}

VS Code can also tell you if there is an error in your JSON sample that renders it invalid. If there is an error, Data Pull won’t be able to parse the information if you use the sample JSON in a test.

Troubleshooting

Data Pull checks to see if you have entered required values for each mapping type, and you can only save a mapping once you have made the required selections. You can update or remove existing mappings, but be sure to Save.

Your mappings play a central role in your Data Pull integration. This means that it’s important to ensure that you have entered the keys as they appear in the response from your system. Remember, Data Pull doesn’t actively check against your external service while you are constructing mappings.

It’s important to test your connections. If you have saved a sample piece of JSON, you can use the Test tab to conduct a manual test, without sending a request to an external service. Select the Test tab, then toggle the test to use sample JSON. Paste your sample into the textbox and Test. If Data Pull successfully interprets the JSON, you will see a success message. For help with particular error messages, see Integration Tasks & Tests.