Import NetSuite line items

Comments

22 comments

  • Stephen Lemp

    Are there any details related to the Items : Create Line mapping that could be shared?

    0
  • Stephen Brandt Documentation Manager Community moderator
    Engaged
    Great Answer
    Top Contributor
    Celigo University Level 3: Master

    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. 

    1
  • Danilo Marques

    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."}]}]"

    Could you help me, please?

    0
  • Stephen Brandt Documentation Manager Community moderator
    Engaged
    Great Answer
    Top Contributor
    Celigo University Level 3: Master

    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. 

    0
  • Stefan Podell

    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!

    0
  • Stephen Brandt Documentation Manager Community moderator
    Engaged
    Great Answer
    Top Contributor
    Celigo University Level 3: Master

    Hi, Stefan Podell. I apologize for the delay, while I checked with the development team. One of our resident NetSuite experts confirmed your solution:

    Yes, you can use multiple fields with the "Use as a key field to find existing lines" flag.

    1
  • Stefan Podell

    Thank you, Stephen Brandt!

    0
  • Robert Aguilera

    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?

    1
  • Stephen Brandt Documentation Manager Community moderator
    Engaged
    Great Answer
    Top Contributor
    Celigo University Level 3: Master

    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:

    1. In the transfer, specify a column that is common to the related rows in How would you like to group and sort records? > Group records by field
    2. Add a transformation after the transfer, and set up rules to structure the fields within a line_items[] array. For example:

      *.Location  ==> line_items[*].Location
    0
  • Matthew Morrill

    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.

    0
  • Tyler Lamparter Principal Product Manager
    Awesome Follow-up
    Engaged
    Top Contributor
    Answer Pro
    Celigo University Level 4: Legendary

    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.

    1
  • Matthew Morrill

    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.

    0
  • Tyler Lamparter Principal Product Manager
    Awesome Follow-up
    Engaged
    Top Contributor
    Answer Pro
    Celigo University Level 4: Legendary

    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.

    0
  • Matthew Morrill

    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:

    • Items : Line ID
    • Expense : Line ID
    • Batch Transaction Information : Line ID

    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.

    0
  • Tyler Lamparter Principal Product Manager
    Awesome Follow-up
    Engaged
    Top Contributor
    Answer Pro
    Celigo University Level 4: Legendary

    Matthew Morrill items : line id should be it. What does the request look like under "view request"?

    0
  • Matthew Morrill

    Tyler Lamparter Thanks again for all the help!

    Here's what the request and "Error Fields" look like:

    0
  • Tyler Lamparter Principal Product Manager
    Awesome Follow-up
    Engaged
    Top Contributor
    Answer Pro
    Celigo University Level 4: Legendary

    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.

    1. Remove the "Internal ID" mapping. This isn't needed and shouldn't harm anything, but remove it and try again.
    2. Try removing the "record." piece of the mapping. This shouldn't be needed and shouldn't harm anything, but just knocking out small things to make sure. Try and run again.
    3. If that doesn't work, try to map "Use dynamic mode" and hardcode the value to "true". Move the mapping to the top of your mappings.
    4. If that doesn't work, remove the previous use dynamic mapping and try to transform the data on the initial source step like was explained above. The transformation would look like this. After that, modify your line mapping to map to the new lines array field i.e. line_items[*].line_id. Add the other fields into your transform as needed. This would be on the source side of the flow.
    5. If all that fails, try to use SS1.0. You'll have to remake the import and make sure to select SS1.0 before you save it. After that, remake the mappings.
    6. If all that fails, I would file a ticket and see if support can help out.
    0
  • David Gollom Strategic Partner
    Top Contributor
    Celigo University Level 4: Legendary
    Answer Pro
    Great Answer
    Engaged

    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.  

    0
  • Tyler Lamparter Principal Product Manager
    Awesome Follow-up
    Engaged
    Top Contributor
    Answer Pro
    Celigo University Level 4: Legendary

    David Gollom what are you trying to solve for? Just creating new lines and ignoring existing lines?

    0
  • David Gollom Strategic Partner
    Top Contributor
    Celigo University Level 4: Legendary
    Answer Pro
    Great Answer
    Engaged

    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.  

    0
  • Tyler Lamparter Principal Product Manager
    Awesome Follow-up
    Engaged
    Top Contributor
    Answer Pro
    Celigo University Level 4: Legendary

    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.

    0
  • David Gollom Strategic Partner
    Top Contributor
    Celigo University Level 4: Legendary
    Answer Pro
    Great Answer
    Engaged

    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.  

    0

Please sign in to leave a comment.