Concat a timestamp onto a NetSuite Date string

Integrator.io
, , ,
1

An export from NetSuite returns a Date as a string in DD/MM/YYYY format. This field is called AcceptedDate.

I need to upsert this Date into a Date Time field on Salesforce and my initial idea was to do a simple concatenation, something like:

{{dateFormat YYYY-MM-DD AcceptedDate DD/MM/YYYY GMT}}T{{timeStamp HH:MM:SS}}Z

I hoped this would return in a Salesforce Date Time format (2024-10-9T12:15:20Z)and could then be inserted but this sets the field to a null; no error thrown.

Is it possible to take a NetSuite date string, format the date and also append a time stamp onto the end of it?

Celigo AI keeps suggesting a change to the NetSuote saved search but this unfortunately is not an option, I need to make these changes in Celigo.

2

Hi @davidoneill ,

I understand that you can't change the saved search this is pulling from. The reason why that is preferred is because the date format in NetSuite is subject to user and company preferences, and so could change without warning in the future, causing unexpected behavior in the flow. I prefer to use a Formula (Text) field in searches over native date fields because this allows the format to be hard-coded directly within NetSuite.

That being said, it is possible to do what you are trying to do with handlebars. The reason yours did not work is because the input and output formats need to be quoted.

For example using an acceptedDate of “04/10/2024”:

handlebars: {{dateFormat "YYYY-MM-DD" AcceptedDate "DD/MM/YYYY"}}T{{timestamp "HH:mm:SS"}}Z
output: 2024-10-04T14:07:35Z

Note the quotes around the date formats. Also, note the lowercase “mm" in the timestamp. Capital M stands for month, not minute.

Also, would you consider moving this to the “Troubleshoot Custom Flows” section for more visibility for future searchers?

3

Hi Ezriah

Thank you, but that's my fault for not formatting the question well enough.

In reality, I have this exact code currently in Celigo, even with the lowercase mm, but the preview window shows null and if I ran any records, nothing is inserted.

Does anyone have any other suggestions?

4

@davidoneill Can you share the structure of the input data going into the mapping? Also any screenshots would be helpful to identify the issue.

5

Here is the mapping as I have it at the moment:

{{dateFormat "YYYY-MM-DD" CustomerRecord.AcceptedDate "DD/MM/YYYY"}}T{{timestamp "HH:mm:SS"}}Z

And here is a subsection of the input data. The entire input is very large and most of it is not required but tthe sub structure of ‘CustomerRecord’ holds the data we need:

{

"add_on": {
"internalid": "add_on.internalid",
"name": "add_on.name"
},
"frequency": {
"internalid": "frequency.internalid",
"name": "frequency.name"
},
"billingschedule": {
"internalid": "billingschedule.internalid",
"name": "billingschedule.name"
},

"CustomerRecord": {
"id": "100",
"recordType": "customer",
"companyname": "Mapping Query",
"Billing Country Code": "US",
"Celigo[AT]_Salesforce_Id": "001xxxxxxxxxxxx",
"Is Individual": "F",
"datecreated": "6/7/2024 1:29 pm",
"billing_contact_name": "A Name",
"Last Modified": "10/9/2024 3:05 am",
"AcceptedDate": "6/10/2024"
}

}

6

I should add to this, when I test in the mapping screen itself, the output shows the field as null.

If I use just the DateFormat method with no time stamp, the date is populated into Salesforce successfully and a default time of 5am is appended.

Unfortunately I cannot use this as the time of the date accepted is important in Salesforce so it must be correct.

7

Hi @davidoneill ,

It seems odd to me that it would show null in the mapping editor. When I tried it, the date comes through correctly:

5301043caa20740200000001-1e225322e19244e88352be27e57e0a38.png
5301043caa20740200000001-1e225322e19244e88352be27e57e0a38.png2068×840 71.2 KB

I would try going through each step and repopulate the mock input and output data just to be sure there were no accidental changes. Also, try running it in test mode and/or check any logs you have on the Salesforce side to see what exactly is being imported into Salesforce. Is it possible that this is a date-only field that doesn't accept a timestamp?

If anyone else has any suggestions, please add them, but at this point you do seem to be doing everything correctly.

8

Ezriah, I wanted to offer a quick update.

A similar ask was required in a different flow with a slightly different structure but when I used the above option, it worked first time as expected.

I went back to the mapping which caused this issue and confirmed they were the same (excluding the structure of the data) but again it did not work. I also deleted this mapping, saved and then re-added from scratch.

As a final test, deleted the entire node as there were very few mappings in it, and recreated fresh. And it worked exactly as expected.

So it seems there was nothing wrong with the formula; there was something funky under the hood which was causing this to fail.

If anyone else experiences this issue, I would suggest first deleting the mapping and trying again after a save, or if really required, delete the entire node and recreate.

I don't seem to be able to move this to the ‘Troubleshoot custom flows’ section myself but please feel free to move it there yourself if you can.