A line item in NetSuite can be thought of as a record having 1:N relationships with other records. It is like a sublist, or a list of lines within a record. For example, a Sales Order has a one-to-many relationship with Items.
How to map line items
You can access line items using the sublist name. In import mapping, each sublist is populated in the format Sublist Name : Field Name, such as Items : Department.
Select the line item to map the source data to this field:
Pro Tip: You can use line-level lookups as well to find data related to that specific line.
Update existing lines
To find an existing line in NetSuite, it helps to understand the logic that integrator.io uses to decide whether the line needs to be created or updated:
- Search for key fields to find an existing line. For example, if you have a Sales Order, integrator.io tries to use the Item as a key field and find a line with the matching item value.
- If that Item cannot be found in the existing record, integrator.io will create a line.
- If a matching line item exists, integrator.io will update this line with the information that you provide.
In some cases, you might already have the line identifier (Line ID in NetSuite), which is unique for each line. Then, when mapping the Line ID for a sublist, integrator.io will use the Line ID to try to find and update that line; if no matching Line ID is found, one is created.
Provide a custom field as a key value to find a line item
You can also instruct integrator.io to use a custom or standard field to find an existing line.
Let’s say that you have a custom field called eTail order Line ID, which is unique for all the lines in that record. You can use this field to find a matching line with the field value:
- For the mapping to eTail order Line ID, click the Settings (
) button.
- In the resulting Settings pane, check Use as a key field to find existing lines.
- Click Save.
When the key field option is enabled, integrator.io tries to match the field value from the source data against the field value in NetSuite lines. If it finds a match, that line will be updated; otherwise, a new line will be inserted.
Replace all lines in a record
In some cases, the source data may contain the complete order instead of just the delta changes. In others, some lines might be removed from the source system that are still in your NetSuite line items. Nonetheless, you might want to remove all the existing lines and add all the lines from your source data into NetSuite.
To enable this setting, add a new import field mapping for Items : Replace All Lines, and hard-code the value to True. Each sublist on the parent record should have a similarly named mapping option available.
Note: If an item already exists on the record in NetSuite, integrator.io only updates the record. If the item doesn't exist, integrator.io replaces all lines and adds new items. To replace all lines every time, use an alternate identifier that is certain not to already have existing lines. Check the Use as key to find existing lines checkbox on the import field that you want to use as the identifier..
Comments
Are there any details related to the Items : Create Line mapping that could be shared?
Hi, Stephen Lemp. You ask a good question that I don't have a ready answer for, in terms of use cases and NetSuite behavior. Our developers identified it at first glance as a "legacy" mapping function, and we hope to get back to you with more details after looking into it further.
Hi guys
I did the mapping above for save in the "mediaitem" sublist but the NetSuite is returning: "[{"statusCode":422,"errors":[{"source":"application","code":"SSS_INVALID_SUBLIST_OPERATION","message":"Failed to create, update/transform record because You have attempted an invalid sublist or line item operation. You are either trying to access a field on a non-existent line or you are trying to add or remove lines from a static sublist.". Tip: Please make sure that all the line items that you're trying to use exist on the record."}]}]"
Hi, Danilo Marques. Sorry, but it's impossible to tell from here whether you're running into issues with the integration or NetSuite setup. My recommendation is that you sign up for an office hours session where one of our solution consultants can provide guidance and look at your mappings and NetSuite account.
Is it possible to use the "Use as a key field to find existing lines" flag on multiple fields? For example, for item fulfillments, I want to match on the line ID and the item name (to address occasional item substitutions, and I want to make sure we catch an exception where the warehouse substituted an item without the order being modified in NetSuite). Thanks!
Hi, Stefan Podell. I apologize for the delay, while I checked with the development team. One of our resident NetSuite experts confirmed your solution:
Thank you, Stephen Brandt!
Stephen, do you have any additional documentation on the fields needed and how to map them to create a work order that has multiple line items? Following the information on this document does not give me much insight into how to build it. I can successfully create the work order; I'm mapping the item to items: Item(Name) and quantity to items: quantity BUT when i run it it creates multiple Work orders vs my expectation to create 1 work order with 2 items on it. Can you help?
Update to previous question, involving a Data Loader flow with data parsed from Excel. By default, each row will contain a separate record. The solution to mapping them as line items involves restructuring the data to a JSON array format:
*.Location ==> line_items[*].Location
Stephen Brandt First off - Thank you! I've found this thread extremely useful.
Is there a way to update all existing line items on a transaction? In my use case, I don't want to create new lines, but I want to update the department field on each line for a record. If I simply try to update the transaction's main line "Department" it does not update the GL impact of all lines, just the main line. I've done a lot of troubleshooting, but haven't found any solutions for this yet.
Matthew Morrill you would need to have all the internal id and all the line ids. From there, you can group by and transform (like Stephen Brandt described above) into a structure like this:
{"internal_id": 123,
"line_items":[{"line_id":1},{"line_id":2}]
}
From there, it would be similar to the other thread we were discussing on for updating existing lines, but your mapping to line_id field would be line_items[*].line_id.
Tyler Lamparter Thank you! That section didn't quite click with me at first, but I get it now. In my utopian dream, I was hoping there would be a way to use a wildcard value like "line item = *", to match all non-null values for Line ID, then set the advanced import setting to "Only perform mapping when: Source record has a value"... but that doesn't seem to be an option.
Here's a detailed explanation of what I'm running into: When I run a saved search of GL impact of a transaction in NetSuite, the field {line} always returns a value for every GL line, no matter the transaction type; However, when I try to map Items : Line ID to some of our transactions, I get the "You have attempted an invalid sublist or line item operation" error. If I instead map the field to "Items : Line Number" it will find a match for some of the line numbers, but the "{line}" number has no relation to the "Items : Line Number" value, so I end up editing fields I did not intend to. Even if I know the MIN and MAX of {line} for a single transaction, (in this case MIN = 0, MAX = 30), I get the same error I referenced above once I go above Line Number = 20. I haven't found a field in NetSuite that I can pull into a saved search that would give me the relevant "Line Number" for each GL line...
My brute force solution idea was to overwrite a desired field for all lines in a transaction.
Matthew Morrill what does your saved search settings and output look like? I'm sure we can configure it in a way to work here and line id is what we use for our own integration apps.
Tyler Lamparter Attached are snippets from our NS saved search showing the Saved Search results and the field that's defined in the search settings. Also attached are snippets from our Celigo flow trying to map to this Line ID. In my use case, I'm attempting to update the targeted transaction line with a department. This is a single GL line on an Item Receipt transaction. This method works for most other transactions, but for some reason this transaction doesn't allow it.
Celigo's list of fields available for mapping includes 3 "Line ID" fields:
I've tried mapping to all 3 with the "Use as a key field to find existing lines" option checked, but I get the same error each time.
Matthew Morrill items : line id should be it. What does the request look like under "view request"?
Tyler Lamparter Thanks again for all the help!
Here's what the request and "Error Fields" look like:
Matthew Morrill can you try a few things? I'm not super sure why it's trying to add a line item there. If you notice, it's attempting to insert line 21.
Did you guys ever find out more on the Items : Create Line mapping? It's mentioned in this document NetSuite field definitions for import mapping – Celigo Help Center. I tried it a long time ago to ignore some lines during and import and it didn't work as expected. I striped them out with a script. It would be a handy feature sometimes.
David Gollom what are you trying to solve for? Just creating new lines and ignoring existing lines?
Tyler Lamparter not really solving anything specific. Just want to learn. However, the idea is you have an export with a line array. One of the lines is a subtotal that you don't need to import into NetSuite. You use a handlebar expression to set Create Line to false for that line and it gets ignored.
David Gollom that makes sense. Are you sure this boolean field mapping for "Create Line" doesn't work? I can test it sometime soon if you don't get to it before me.
Tyler Lamparter if I remember correctly, the mapping didn't "seem" to do anything. So, I didn't try real hard to make it work. I'll try to test it out.
Please sign in to leave a comment.