Netsuite Item Fufillments creating separate IFs for each item importing, when we need them created by Sales Order

Comments

17 comments

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

    Christian Bannard you need to use the grouping feature to group the records by a header level field. Typically that header level field would be an order number. 

    0
  • Christian Bannard
    Engaged
    Awesome Follow-up

    Hi Tyler,

    So I've done that and where previously the records were pushing into Netsuite, albeit creating an IF for each line imported, after setting the grouping I'm now getting the following error (bottom screenshot).

    The quantity has been set both in the main record detail and in the Inventory Detail sub-record.

    Regards,

    Christian

    Main record

    Sub record (Inventory Detail)

    Error message we're getting

     

    0
  • Christian Bannard
    Engaged
    Awesome Follow-up

    Does this look like the correct format for an Item Fulfillment for Netsuite that's creating a fulfillment for two line items or am I missing something?

    {
      "nlobjFieldIds": {
        "celigo_nlobjTransformId": 3740358,
        "ceilgo_nlobjTransformType": "salesorder",
        "celigo_recordmode_dynamic": "true",
        "celigo_replaceAllLines_item": "true",
        "trandate": "26/10/2023",
        "celigo_replaceAllLines_package": "true",
        "shipstatus": "Packed"
      },
      "nlobjSublistIds": {
        "item": {
          "lines": [
            {
              "itemreceive": "true",
              "quantity": "1",
              "itemname": "MFGA01",
              "location": "VIC Warehouse",
              "line": "1",
              "celigo_inventorydetail": {
                "nlobjRecordType": "inventorydetail",
                "nlobjFieldIds": {
                  "location": "MEL",
                  "celigo_initializeValues": "location"
                },
                "nlobjSublistIds": {
                  "inventoryassignment": {
                    "lines": [
                      {
                        "quantity": "1",
                        "line": "1",
                        "binnumber": "BULKDD"
                      }
                    ]
                  }
                }
              },
              "_keys": [
                "location"
              ]
            },
            {
              "itemreceive": "true",
              "quantity": "1",
              "itemname": "MFGA01",
              "location": "VIC Warehouse",
              "line": "2",
              "celigo_inventorydetail": {
                "nlobjRecordType": "inventorydetail",
                "nlobjFieldIds": {
                  "location": "MEL",
                  "celigo_initializeValues": "location"
                },
                "nlobjSublistIds": {
                  "inventoryassignment": {
                    "lines": [
                      {
                        "quantity": "1",
                        "line": "2",
                        "binnumber": "BULKDD"
                      }
                    ]
                  }
                }
              },
              "_keys": [
                "location"
              ]
            }
          ]
        }
      }
    }
    0
  • Tyler Lamparter Principal Product Manager
    Awesome Follow-up
    Engaged
    Top Contributor
    Answer Pro
    Celigo University Level 4: Legendary

    Christian Bannard since you enabled group by rows on the export, it looks like some of the mappings on backend reference the 0 index position of the grouped rows. I'm not sure if you can change this in the UI or not, but if you can it would be under the settings wheel next to the effected field. Can you check the quantity field and see if it has use first row specified? I not, can you remake the import step from scratch and remap your fields?

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

    Christian Bannard here is an example of what I mean. On the backend, the quantity is mapped to 0.quantity and sku is mapped to 0.SKU because you had changed from non grouping records to grouping records. I think there is a field in the UI of the field level setting to adjust it.

    0
  • Christian Bannard
    Engaged
    Awesome Follow-up

    Cool, both the SKU and quantities were all ticked on as First record.

    So am I correct in assuming this should be unticked for all mappings?

    0
  • Christian Bannard
    Engaged
    Awesome Follow-up

    Still getting the same error even after unticking First record throughout all fields in the mapping.

    0
  • Christian Bannard
    Engaged
    Awesome Follow-up

    Hi Tyler Lamparter,

    So I recreated the Import completely from scratch per your recommendation and ensured that none of the mappings had First record ticked on, and still no joy, same problem, the record is still being allocated line 0 and line 1 respectively when it runs its loop, rather than reading our mapping of 1 and 2.

    importing record {"index":0,"retryCount":0}
    {"nlobjRecordType":"itemfulfillment","nlobjFieldIds":{"celigo_nlobjTransformId":3740358,"celigo_recordmode_dynamic":"true","celigo_replaceAllLines_item":"true","trandate":"26/10/2023","shipstatus":"Packed"},"nlobjSublistIds":{"item":[{"quantity":"1","itemname":"MFGA01","location":"VIC Warehouse","line":"1","_keys":["location","line"],"celigo_inventorydetail":{"nlobjRecordType":"inventorydetail","nlobjFieldIds":{"quantity":"1"},"nlobjSublistIds":{"inventoryassignment":[{"quantity":"1","binnumber":"BULKDD"}]}}},{"quantity":"1","itemname":"MFGA03","location":"VIC Warehouse","line":"2","_keys":["location","line"],"celigo_inventorydetail":{"nlobjRecordType":"inventorydetail","nlobjFieldIds":{"quantity":"1"},"nlobjSublistIds":{"inventoryassignment":[{"quantity":"1","binnumber":"BULKDD"}]}}}]}}
    nlapi calls registered for $R:
    r = NRecord.transform({"fromType":"salesorder","fromId":3740358,"toType":"itemfulfillment","defaultValues":{"recordmode":"dynamic"}});    
    r.setValue({"fieldId":"trandate","value":"26/10/2023"});    
    r.setText({"fieldId":"shipstatus","text":"Packed"});    
    r.setCurrentSublistText({"sublistId":"item","fieldId":"location","text":"VIC Warehouse"});    
    r.setCurrentSublistText({"sublistId":"item","fieldId":"location","text":"VIC Warehouse"});    
    r.selectLine({"sublistId":"item","line":0});    
    r.setCurrentSublistValue({"sublistId":"item","fieldId":"itemreceive","value":false});    
    r.selectLine({"sublistId":"item","line":1});    
    r.setCurrentSublistValue({"sublistId":"item","fieldId":"itemreceive","value":false});    
    r.selectLineItem({"sublistId":"item","line":0});    
    r.setCurrentSublistValue({"sublistId":"item","fieldId":"itemreceive","value":true});    
    r.setCurrentSublistValue({"sublistId":"item","fieldId":"quantity","value":"1"});    
    r.setCurrentSublistValue({"sublistId":"item","fieldId":"itemname","value":"MFGA01"});    
    r.setCurrentSublistText({"sublistId":"item","fieldId":"location","text":"VIC Warehouse"});    
    r.setCurrentSublistValue({"sublistId":"item","fieldId":"line","value":"1"});    
    sr = r.getCurrentSublistSubrecord({"sublistId":"item","fieldId":"inventorydetail"});    
    r.commitLine({"sublistId":"item"});

    As a work-around I also tried subtracting 1 from the line_id field in the mappings using:

     {{ subtract line_id 1 }} 

    ... with the thought that even if the line_id starting at 0 when the records are grouped as you say, subtracting 1 from the line_id we're mapping (which was generated from the original Netsuite PO we send to the 3PL warehouse) should then match the line_id's Celigo has generated and trying to pass to Netsuite when it runs its query on the Netsuite side.

    Despite trying this subtract to get our record numbers to match the automatic record numbers Celigo is assigning the records as a work-around, it is still erroring, though this time with a different error (the usual one about trying to access and modify a sublist item that may not exist).

    What I don't understand is why Celigo is ignoring our mapping completely when we've set a specific mapping in the mappings (it's basically totally ignoring our mapping when we tell it what line IDs we want to associate with the records we're looking up).

    Following is a Preview screenshot of the record output (pasted into VSCode for ease of reading with syntax highlighting).

    One thing I noticed is that with the attempted subtract work-around is that it generates the line 0 field for the SKU MFGA01 line correctly, though then doesn't loop through the next line record and generate a revised line 1 value for SKU MFGA03 record, which it should.

    Any idea why it wouldn't generate both line_ids?

    The subtract 1 should have turned MFGA01 to line_id 0 and MFGA03 to line_id 1, as the original numbers were 1 and 2 respectively, yet it doesn't.

    The input:

    The output:

    One thought. Whilst this flow has worked previously on a single record basis, should I be using "Items : LineID" or "Items : Line Number"?

    To-date we've always used Line ID though thought maybe we can use Line Number instead?

    Anyway, I'm going to raise a ticket with Celigo Support and will include this thread in the ticket so Support can get up to speed on it.

    Regards,

     

    Christian

     

     

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

    Christian Bannard can you send the sample JSON payload? Next think I would try is adding a transform step on the export side to convert to rows [{},{},{}] to an object with a lines array {lines:[{},{}]}. Then make a new import using that transform and see if you get the same result. I can't tell if some things are still pointing to 0 index or not.

    0
  • Christian Bannard
    Engaged
    Awesome Follow-up

    Tyler Lamparter the initial file coming from your FTP CSV converter in JSON preview format:

    {
      "page_of_records": [
        {
          "rows": [
            {
              "fulfillment_date": "26/10/2023",
              "consignment_no": "AQ000003",
              "aquilia_sales_order_no": "SOAU0047944",
              "customer_name": "Christian Bannard",
              "purchase_order_no": "POAU0090738",
              "line_id": "1",
              "shipping_attention": "Christian Bannard",
              "send_to_address_1": "20 Erskine Drive",
              "send_to_address_2": null,
              "send_to_city": "Rowville",
              "send_to_state": "Victoria",
              "send_to_postcode": "3178",
              "SKU": "MFGA01",
              "quantity": "1",
              "fulfilled_from_location": "MEL",
              "pack_description": "CARTON",
              "weight_kg": "10",
              "length_cm": "120",
              "width_cm": "50",
              "height_cm": "7",
              "cubic_m3": "0.05"
            },
            {
              "fulfillment_date": "26/10/2023",
              "consignment_no": "AQ000003",
              "aquilia_sales_order_no": "SOAU0047944",
              "customer_name": "Christian Bannard",
              "purchase_order_no": "POAU0090738",
              "line_id": "2",
              "shipping_attention": "Christian Bannard",
              "send_to_address_1": "20 Erskine Drive",
              "send_to_address_2": null,
              "send_to_city": "Rowville",
              "send_to_state": "Victoria",
              "send_to_postcode": "3178",
              "SKU": "MFGA03",
              "quantity": "1",
              "fulfilled_from_location": "MEL",
              "pack_description": "CARTON",
              "weight_kg": "10",
              "length_cm": "130",
              "width_cm": "50",
              "height_cm": "7",
              "cubic_m3": "0.07"
            }
          ]
        }
      ]
    }

    This is the payload going out of Celigo into Netsuite:

    importing record {
          "index":0,
          "retryCount":0
    }
    {
          "nlobjRecordType":"itemfulfillment","nlobjFieldIds":
          {
                  "celigo_nlobjTransformId":3740358,
                  "celigo_recordmode_dynamic":"true",
                  "trandate":"26/10/2023",
                  "shipstatus":"Packed",
                  "celigo_nlobjTransformType":"salesorder"
          },
          "nlobjSublistIds":
          {
                  "item":[
                          {
                                  "quantity":"2",
                                  "itemname":"MFGA01",
                                  "location":"VIC Warehouse",
                                  "itemreceive":"true",
                                  "_keys":
                                  [
                                          "location"
                                  ],
                                  "celigo_inventorydetail":
                                  {
                                          "nlobjRecordType":"inventorydetail",
                                          "nlobjFieldIds":
                                          {
                                                  "quantity":"2"
                                          },
                                          "nlobjSublistIds":
                                          {
                                                  "inventoryassignment":
                                                  [
                                                          {
                                                                  "quantity":"2",
                                                                  "binnumber":"BULKDD"
                                                          }
                                                  ]
                                          }
                                  }
                          },
                          {
                                  "quantity":"3",
                                  "itemname":"MFGA03",
                                  "location":"VIC Warehouse",
                                  "itemreceive":"true",
                                  "_keys":
                                  [
                                          "location"
                                  ],
                                  "celigo_inventorydetail":
                                  {
                                          "nlobjRecordType":"inventorydetail",
                                          "nlobjFieldIds":
                                          {
                                                  "quantity":"3"
                                          },
                                          "nlobjSublistIds":
                                          {
                                                  "inventoryassignment":
                                                  [
                                                          {
                                                                  "quantity":"3",
                                                                  "binnumber":"BULKDD"
                                                          }
                                                  ]
                                          }
                                  }
                          }
                  ]
          }
    }

    nlapi calls registered for $R:

    r = NRecord.transform({"fromType":"salesorder","fromId":3740358,"toType":"itemfulfillment","defaultValues":{"recordmode":"dynamic"}});  

    r.setValue({"fieldId":"trandate","value":"26/10/2023"});        

    r.setText({"fieldId":"shipstatus","text":"Packed"});    

    r.setCurrentSublistText({"sublistId":"item","fieldId":"location","text":"VIC Warehouse"});      

    r.setCurrentSublistText({"sublistId":"item","fieldId":"location","text":"VIC Warehouse"});      

    r.selectLine({"sublistId":"item","line":0});    

    r.setCurrentSublistValue({"sublistId":"item","fieldId":"itemreceive","value":false});  

    r.selectLine({"sublistId":"item","line":1});    

    r.setCurrentSublistValue({"sublistId":"item","fieldId":"itemreceive","value":false});  

    r.selectLineItem({"sublistId":"item","line":0});        

    r.setCurrentSublistValue({"sublistId":"item","fieldId":"quantity","value":"2"});        

    r.setCurrentSublistValue({"sublistId":"item","fieldId":"itemname","value":"MFGA01"});  

    r.setCurrentSublistText({"sublistId":"item","fieldId":"location","text":"VIC Warehouse"});      

    r.setCurrentSublistValue({"sublistId":"item","fieldId":"itemreceive","value":true});    

    sr = r.getCurrentSublistSubrecord({"sublistId":"item","fieldId":"inventorydetail"});
     
    It's still looking to be using 0 and 1.
     
    Regards,
     
    Christian
    0
  • Tyler Lamparter Principal Product Manager
    Awesome Follow-up
    Engaged
    Top Contributor
    Answer Pro
    Celigo University Level 4: Legendary

    Christian Bannard try removing the "use dynamic" mapping. Also, try to create a new import step and use SuiteApp 1.0 instead of 2.0.

    0
  • Christian Bannard
    Engaged
    Awesome Follow-up

    Hi Tyler Lamparter,

    Just thought I'd update you on this. 

    So we finally managed to get this resolved and it's now fully functional!

    We did end up using Rules 1.0 rather than 2.0, with setting as follows in case anyone ever needs them.

    This is a fully functional version of an IF flow from CSV to Netsuite, which allows multi-part IFs to work correctly.

    Note: The only caveat to this flow is that if you're sending CSV imports and fulfilling from multiple locations for the same IFs, you need to separate any IF line items from different locations into separate CSV files if they pertain to the same SO (i.e. you can't have two line items being fulfilled from two different locations in the same file, though note that you can have two different files with those same two line items in different files if you need to fulfill orders from different locations.

    e.g. you have a sales order you're fulfilling, you fulfill one line item of a SKU where the customer requires two line items, from your Melbourne Warehouse, and the other item from Brisbane Warehouse, you need to put these line items in separate CSVs as Netsuite won't allow fulfillments from multiple locations in the same pass. You need to process two CSVs, one for Melbourne for that SKU and one from Brisbane.

    Import formatting for bringing in our CSVs:

    The formatting of our Transform using "Rules 1.0":

    The initial record type selection of our Export into Netsuite:

    The sub-record selection ("Item" and "InventoryDetail" was created by our transform):

    Our main record mappings are as follows:

    The Lookup for the "CreatedFrom" record to find the existing Netsuite SO we're going to transform into our Netsuite IF:

    Translation of our Locations from the imported CSV value (since we're using a 3PL), to the names of our Locations as used in our Netsuite and using this Location as a key to initialise for Inventory Detail for lookup of our Bins and Quantities for the Inventory Detail section (basically keying the second sublist for Inventory Detail to the first sublist, which is the line items of the IF).

    The settings for our sub-record for Inventory Detail:

    Our end-result record that we going to push to Netsuite:

    Anyway, I thought I'd detail the entire process so it's helpful to anyone that may ever tackle the same issue as it's taken around six weeks to get to the bottom of configuring IFs successfully from CSV to Netsuite.

    Tyler, thanks for your assistance with offering of suggestions and bouncing ideas off. Eternally grateful!

    Maybe one part of Celigo documentation that requires a bit of fleshing out (CSV to Netsuite basic functionality).

    Regards,

     

    Christian

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

    Christian Bannard thanks for the follow up and glad you were able to figure it out! One thing I want to ask though is why did you use rules 1.0 instead of rules 2.0? Rules 2.0 has a lot more functionality and is a lot more powerful. There is a really good video done by our CTO to showcase how to use it: https://docs.celigo.com/hc/en-us/articles/12581205330587-Transformation-2-0.

     

    I suspect you may have left Rules 2.0 on the "Modify input rows []" settings which didn't allow you to output a single object with an array of lines. 

     

    Instead of "modify input rows []", you should use "Create output record from input rows []". 

     

    With this option selected, you can setup your structure like this:

    0
  • Christian Bannard
    Engaged
    Awesome Follow-up

    This part of the equation was modified by your technical support team; originally I was using Rules 2.0 and thought the same in that it was a bit of a backwards step. I will create a duplicate flow and see about replicating it into the new format as I'd prefer it to be on the latest. At present getting it working is the most important thing as we're about three weeks away from going live and are a bit pressed for time, and "working" is better than "doesn't work" during go-live! :-) 

    I'm now trying your recommendation with out Item Receipting part of the equation, though it's not populating anything in the item or inventorydetails section of the output when I run it. It populates the initial output correctly in the preview, though errors with the following and looks like nothing got pushed to the actual record insertion request into Netsuite.

    Initial transform to organise records into respective groups/sub-groups of data:

    Initial data:

    Error message:

    Main mapping:

    Sub-list mapping for Inventory Details:

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

    Christian Bannard I want to clarify here that transform rules 1.0 vs 2.0 isn't going to be the issue here. Transform rules are on your export and is completely delinked from your import to NetSuite step. Once a record is exported from source, it runs through transform rules and comes out the other end as a modified record. That modified record then gets passed off to the next step. The reason for failure in this case is either because the configuration of transform 2 didn't match the output you had in transform 1 or something else on your NetSuite step.

    0
  • Christian Bannard
    Engaged
    Awesome Follow-up

    Hi Tyler Lamparter, apologies for the delay in response.

    So, we ended up correcting the issues and eventually got this all working. After your Celigo Support staff got it working in Rules 1.0 I ended up perservering and got Rules 2.0 working also, so that's all good and we got Item Fulfillments functional both for part fulfillment and total fulfillment on Inventory Items.

    With that said, we've now hit another snag, with that being that we can't fulfill Kits.

    The same flow simply will not work for kits, even though it will fulfill Inventory Items flawlessly.

    Any recommendations on how to handle this?

    From what we can see:

    1. Simply providing Celigo with a list of kit member items and their LineIds to fulfill kits is not enough and a header SKU (for the kit) seems to be needed.

    2. We are transforming records in Netsuite using Special Order Items in Netsuite, so the Sales Order automatically generating an associated Purchase Order, which automatically converts Sales Orders, which have Kit SKUs on them (and don't include Member Item SKUs as line items) into Purchase Orders, which only have Member Item SKUs on them, and no Kit SKUs.

    3. Is there any way that you're aware of to create an Item Fulfillment in Netsuite using only Member Item SKUs and not need the Header SKU also for the kit?

    Note: The header SKU information isn't passed back to us by the 3PL when they fulfill an order because what we're sending them initially is a Purchase Order with no Kit SKU on it, just the Member Item SKUs. So they basically just pass us back the shipment information of what's been shipped (the Sales Order Number, the Purchase Order number, Item SKU, and the LineID, along with the carrier information, tracking link for parcels, etc.

    0

Please sign in to leave a comment.