NetSuite Import (Sales Order) fails cause of mapped date field (API SuiteApp SuiteScript 2.x)
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
Comments
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.
Stefan Lindner 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 David Gollom mentioned, SuiteApp 1.0 and SuiteBundle 1.0 will work as usual with the drop down list.
Stefan Lindner 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.
Hi Tyler,
Check ticket 180792. This is a different issue and also not repeatable in the SuiteBundle 1.0 version.
David Gollom 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.
Tyler Lamparter Yea, that's exactly it. Grouped records without a transform. Thanks for the quick response!!
David Gollom no problem! Appreciate the patience
Hi Tyler Lamparter, David Gollom,
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
Stefan Lindner do you still have a date format specified in the dropdown on the field level settings?
I'm also not able to get the handlebar solution to work. Same error. This is painful.
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"
Stefan Lindner 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"}}David Gollom 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.
Mike Branch what does your setup look like?
David Gollom 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://docs.celigo.com/hc/en-us/community/posts/360077243491-FAQ-How-can-I-set-mapping-order-to-ensure-particular-fields-are-mapped-first-
Tyler Lamparter 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:
Mike Branch 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?
Hi Tyler Lamparter,
sorry for the late feedback - same situation as discribed by Mike Branch.
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:
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 Stefan Lindner 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});
Mike Branch Stefan Lindner David Gollom for now, you probably should use SuiteApp 1.0 if you run into one of these issues. We are working on patch for some of these issues and I'll let you know when we have an eta for it. Appreciate your patience here.
Hey Tyler. Thanks for all the assistance. Hopefully the patch fixes some of the issues.
I got it to work with 1.0. Tyler Lamparter thanks for your help!
Hello Tyler Lamparter, we are facing the similar issue with the Date and Date/Time fields imported via Celigo Suitescript 2.x NetSuite connector after switching from Suitescript v1. Can I ask you if there was any progress on the bug fixing recently?
According to NetSuite documentation for a setValue method there is a change of Date and Date/Time object requirements to post the Javascript Date() instead of a string what was a case for Suitescript v1. However I don't see the method even declared in the payload for the date fields in the request what might be a potential issue beside the changed Date value formatting requirements.
Kristyna Macha Sverakova Mike Branch David Gollom looks like a patch should come in a week or so for date issue and issue when your data is grouped at the source into rows vs records. Confirming with PM on date, but figured I'd give you an update.
Hi @Tyler Lamparter,
thanks for the update - looking forward to have the new version. :)
Btw. is there any kind of newsletter or so to get informed about new releases of the SuiteApp?
In the regular email for IO upgrades I didn’t see any information acc. SuiteApp changes or did I miss something?
Thanks and regards
Stefan
Hi, Stefan Lindner. All Celigo NetSuite integration bundle updates are timed with the regular six-week release cycle, and you can find their release notes in their own "What's new" section: NetSuite integration bundles. They're typically not that newsworthy, containing bug fixes and support for additional record and subrecord types. And they're also not that timely, since the pre-SuiteApp version needs to be installed into customer accounts over weeklong phases.
For a bug fix-only patch, we haven't communicated the status in the past, and we'll take your request under advisement, at a minimum announcing the release in the Community.
Stefan Lindner David Gollom Kristyna Macha Sverakova Mike Branch patch was deployed today for date issue. Let us know how it goes! Grouped data issue is pushed to May release* along with several others, but the date issue was the most pressing.
https://docs.celigo.com/hc/en-us/articles/14852364987163-Celigo-platform-2023-4-5-maintenance-release-notes
*assuming all goes according to plan
Hi , I´m having exactly the same error, I tried the dateFormat and different configurations but nothing works, does the patch was deployed? does the issue have a solution?
Alan Reyes patch was deployed so now don't use handlebars and instead use the native date formatting options under the settings wheel on that field mapping.
Hi Tyler Lamparter, it was my first tried, exactly as I used to do with SuiteScript 1.0
But this is the error:
Please sign in to leave a comment.