NetSuite Import (Sales Order) fails cause of mapped date field (API SuiteApp SuiteScript 2.x)

[stefanlindner]

Hi,

I'm struggeling to create a NetSuite Import component with the new API version "SuiteApp SuiteScript 2.x".

I try to create a Sales Order, but as soon as I want to map a value for field "trandate" flow fails with this error: "Failed to save record because Invalid date value (must be DD.MM.YYYY)"

Source value: "orderDate": "2023-02-15T15:01:00.000000+01:00"
Mapping settings for NetSuite field "Date (trandate)": Date Format YYYY-MM-DDTHH:mm:ss.SSSSSSZ

Tired a lot of other combinations, but nothing is working - always the same error. Same mapping is working fine for API version 1.0

Any ideas, why it's not working?

View Request from error details:
importing record {"index":0,"retryCount":0}
{"nlobjRecordType":"salesorder","nlobjFieldIds":{"externalid":"98791f71-9303-4160-ace3-dd8b86e47db6","entity":2077980,"trandate":"2023-02-15T14:01:00Z","otherrefnum":"0006035118","location":4,"cseg_xxy_verkaufska":"Retail : Retail RoE : Netherlands","cseg_xxy_verkaufsla":"Netherlands","shipmethod":2159,"celigo_write_log":"true","internalid":""},"nlobjSublistIds":{"item":[{"item":1148,"quantity":174},{"item":1149,"quantity":174}]}}
nlapi calls registered for $R:
r = NRecord.create({"type":"salesorder","defaultValues":{}});
r.setValue({"fieldId":"externalid","value":"98791f71-9303-4160-ace3-dd8b86e47db6"});
r.setFieldValue({"fieldId":"entity","value":2077980});
r.setValue();
r.setValue({"fieldId":"otherrefnum","value":"0006035118"});
r.setValue({"fieldId":"location","value":4});
r.setText({"fieldId":"cseg_xxy_verkaufska","text":"Retail : Retail RoE : Netherlands"});
r.setText({"fieldId":"cseg_xxy_verkaufsla","text":"Netherlands"});
r.setValue({"fieldId":"shipmethod","value":2159});
r.insertLine({"sublistId":"item","line":0});
r.setSublistValue({"sublistId":"item","fieldId":"item","line":0,"value":1148});
r.setSublistValue({"sublistId":"item","fieldId":"quantity","line":0,"value":174});
r.insertLine({"sublistId":"item","line":1});
r.setSublistValue({"sublistId":"item","fieldId":"item","line":1,"value":1149});
r.setSublistValue({"sublistId":"item","fieldId":"quantity","line":1,"value":174});
r.save({"enableSourcing":false,"ignoreMandatoryFields":false});

Debug logs from Celigo IO Restlert Runner:

Regards
Stefan

[davidgollom]

Interesting Stefan.

I'm getting an unsolvable error on a NetSuite Update import too. It's very simple and works fine in the 1.0 version.

[tylerlamparter]

@stefanlindner on SuiteApp 2.0 use a handlebar expression instead of the normal dropdown list: https://docs.celigo.com/hc/en-us/articles/360039326071-Handlebars-helper-reference#dateFormat. Also like @davidgollom mentioned, SuiteApp 1.0 and SuiteBundle 1.0 will work as usual with the drop down list.

[tylerlamparter]

@stefanlindner I confirmed internally that we have a bug logged for the issue. For the short term, the handlebars or 1.0 versions will get you going.

[davidgollom]

Hi Tyler,

Check ticket 180792. This is a different issue and also not repeatable in the SuiteBundle 1.0 version.

[tylerlamparter]

@davidgollom259 that is a separate issue which is also logged. The issue is around SuiteApp 2.0 handling grouped records incorrectly. So if your initial export step has a grouping which then causes the record to be rows as the record, it behaves strangely on SuiteApp 2.0. For now, you can add a transform step on the export to change the format from [{},{}] to {lineData:[]} or revert the import step to 1.0.

[davidgollom]

@tylerlamparter Yea, that's exactly it. Grouped records without a transform. Thanks for the quick response!!

[tylerlamparter]

@davidgollom no problem! Appreciate the patience

[stefanlindner]

Hi @tylerlamparter, @davidgollom,

thanks for you input - managed to get this running with API version "SuiteApp SuiteScript 1.0". :)

Would be greate to have a kind of documentation, what have change with switch to API version "SuiteApp SuiteScript 2.x". I think, I'm not the only one having this issue... :D

Also tested the mentioned handlebar workaround, but didn't get it up and running:

source data:
{
"orderDate": "2023-02-15T11:14:00.000000+01:00",
"requestedDeliveryDate": "2023-02-23T15:01:00.000000+01:00",
}

handlebar for field "trandate":
{{dateFormat "DD.MM.YYYY" orderDate "YYYY-MM-DDTHH:mm:ss.SSSSSSZZ"}}

Still getting the same error - did a miss something?

Thanks
Stefan

[tylerlamparter]

@stefanlindner do you still have a date format specified in the dropdown on the field level settings?

[mikebranch]

I'm also not able to get the handlebar solution to work. Same error. This is painful.

[davidgollom]

FYI I have a new use case were the SuiteApp is failing. Updating a sales order and trying to set the Status to Pending Approval when it was Pending Fulfillment. This is failing with this error "Failed to create, update/transform record because Cannot find function getSelectOptions in object Field"

[tylerlamparter]

@stefanlindner I have this working in my environment and here is what my setup looks like. I noticed you have an extra Z in your handlebar input format which maybe is causing the issue. Can you try this and let me know?

{{dateFormat "MM/DD/YYYY" date "YYYY-MM-DDTHH:mm:ss.SSSSSSZ"}}

[tylerlamparter]

@davidgollom I actually just had this reported today from another customer that attended office hours, but they were attempting to update the credit hold field on the customer record. I've added your issue to it as well. In the meantime, you'll probably need to use SuiteApp 1.0.

[tylerlamparter]

@mikebranch what does your setup look like?

[tylerlamparter]

@davidgollom can you try and map "use dynamic mode" on your SuiteApp 2.0 import? It appears with 2.0, NetSuite requires using dynamic mode when using getSelectOptions argument, which we are on the backend when mapping to some of these list fields.

https://connective.celigo.com/t/faq-how-can-i-set-mapping-order-to-ensure-particular-fields-are-mapped-first/1208

[mikebranch]

@tylerlamparter I've tried several different configurations but here's what I've got currently. The input data is in the same format as NetSuite requires but nothing seems to work.

Input data:

Error message:

[tylerlamparter]

@mikebranch it looks like you don't even need to perform a date manipulation since your date format already matches. I assume you tried a standard mapping first without any manipulation? Can you debug the connection and screenshot what the logs look like?

[stefanlindner]

Hi @tylerlamparter,

sorry for the late feedback - same situation as discribed by @mikebranch.

Tired multiple things/combination in the mapping, but nothing worked for me. At the moment, I'm using this for the Date mapping:

under settings nothing else is selected.

Connection debugs showing this:

Connector starts to create the SuiteScript API call, but fails creating the line for field "trandate".

If I completely remove the date entry from mapping, it worked for me - even mapping a Sales Order status:

[mikebranch]

Debug log below. This is when using dateFormat in the mapping. With a standard mapping (sending "mm/dd/yyyy" unmodified from webhook to NetSuite) I get "r.setSublistValue();" similar to what @stefanlindner described. Notice the "null" in the NetSuite JSON request.

2023-04-12T15:07:56.447Z 5bee8f4e8651419e8c62a49a41743290 import 64237560613e4765caa13281

2023-04-12T15:07:28.952Z importing record 0 {"nlobjRecordType":"invoice","nlobjFieldIds":{"entity":10370,"celigo_replaceAllLines_item":"true","currency":4,"customform":236,"memo":"Payments to: *********"},"nlobjSublistIds":{"item":[{"item":801,"description":"1000-******”,”quantity":5,"rate":"100.00","taxcode":757,"custcol_xxxxxx_startdate":null},{"item":798,"description":"Test Discount","quantity":5,"rate":"-20","taxcode":757,"custcol_xxxxxx_startdate":null}]}}

2023-04-12T15:07:56.333Z nlapi calls registered for $R r = NRecord.create({"type":"invoice","defaultValues":{"customform":236}});

r.setFieldValue({"fieldId":"entity","value":10370});

r.setValue({"fieldId":"currency","value":4});

r.setValue({"fieldId":"memo","value":"Payments to: ******”});

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

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

r.setSublistValue({"sublistId":"item","fieldId":"item","line":0,"value":801});

r.setSublistValue({"sublistId":"item","fieldId":"description","line":0,"value":"1000-xxxxxx"});

r.setSublistValue({"sublistId":"item","fieldId":"quantity","line":0,"value":5});

r.setSublistValue({"sublistId":"item","fieldId":"rate","line":0,"value":"100.00"});

r.setSublistValue({"sublistId":"item","fieldId":"taxcode","line":0,"value":757});

r.setSublistValue({"sublistId":"item","fieldId":"custcol_xxxxxx_startdate","line":0,"value":"1970-01-01T00:00:00.000Z"});

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

r.setSublistValue({"sublistId":"item","fieldId":"item","line":1,"value":798});

r.setSublistValue({"sublistId":"item","fieldId":"description","line":1,"value":"Test Discount"});

r.setSublistValue({"sublistId":"item","fieldId":"quantity","line":1,"value":5});

r.setSublistValue({"sublistId":"item","fieldId":"rate","line":1,"value":"-20"});

r.setSublistValue({"sublistId":"item","fieldId":"taxcode","line":1,"value":757});

r.setSublistValue({"sublistId":"item","fieldId":"custcol_xxxxxx_startdate","line":1,"value":"1970-01-01T00:00:00.000Z"});

r.save({"enableSourcing":false,"ignoreMandatoryFields":false});