Pull file from NetSuite custom record to an FTP server, change a custom select field on that custom record after file has been pulled
I have a custom record for shipment requests to our 3PL, a script creates these records and they are in the status of 'Pending Upload' and there is a custom field that holds the link to the file, I want to create a flow that searches for the custom records that have a status of 'Pending Upload' export the file to an FTP server and change the status on the custom record.
Does anyone know if the above is possible?
-
Hi, Brian Duffy.
I encourage readers with more practical experience to jump in with any advice about possible efficiencies or pitfalls.
From your brief description, it sounds exactly like the kind of tasks you want an integration platform to accomplish, and all of the steps should be supported by integrator.io. At a high level, we could sketch a single flow:
- Export records from NetSuite – all orders with that field matching, or filter a later subset
- Import records to FTP – write the file to your server
- Import records to NetSuite – use static field mapping to change the value of the custom record in each order
Perhaps this method isn't obvious from your question: "Pull file from NetSuite." integrator.io doesn't need to work with a file/blob in this procedure.
And it could arguably be clearer in our documentation that "import records to FTP" actually means in most cases, "upload the resulting JSON records as a text file to an FTP server."
(p.s., You're not from the Columbus, OH, area, are you?)
0 -
Hi Stephen,
Thank you for your help, I have an export from NetSuite, looking up the custom record type, using a saved search to identify the records that are 'Pending Upload', when I preview data I get:
{
"page_of_records": [
{
"record": {
"id": "101",
"recordType": "customrecord_cdp_docxfer",
"ID": "101",
"Shipment Request XML": "565213-1", <-- This is a link to an XML file in the file cabinet, I simply whish to copy that file to the FTP server
"Status": "Pending Upload",
"Shipment Request Doc": "565213-1",
"Integration Response": "",
"dataURI": ''
}
}
]
}When I add FTP under Destinations it is added as a transfer and then I get errors that no import is selected, very confusing.
0 -
Brian Duffy. Sorry, I confused the file link and record mentioned with the actual XML files in the NetSuite file cabinet. The flow proposed above would not be useful for transferring existing files.
An in-house expert pointed me to a more Granular working example of what you're asking for:
- Export Pending Upload orders based on a NetSuite Saved Search, including the file ids
- NetSuite import – with a file download to retrieve the files from step 1
- FTP import – to upload these files
- Import records to NetSuite – use static field mapping to change the value of the custom record in each order
0 -
Hi Stephen, this has been very helpful so far, thank you, I'm running into the error:
Mismatch in the length of the data and the files array
on step 2, I am getting records that look like this:
{
"record": {
"id": "101",
"recordType": "customrecord_cdp_docxfer",
"ID": "101",
"Shipment Request XML": "565213-1",
"Status": "Pending Upload",
"Shipment Request Doc": "565213-1",
"Integration Response": "",
"dataURI": [redacted]
},
"settings": {
"integration": {},
"flow": {},
"connection": {},
"import": {}
}and my file.name handlebar is
{{#each record}}{{record.[Shipment Request XML]}}{{/each}}
(I am not from the Columbus, OH area)
0 -
Hi, Brian Duffy. Glad to hear it (minus the inevitable troubleshooting).
If I can reconstruct the flow correctly in this format, your records started off with Pending Upload orders, which presumably means that they each one should have files attached. Is it possible that that's not how everything works in practice in NetSuite?
Would it help to filter out records without a "Shipment Request XML" before the import? Or add a conditional #if or #compare to the #each loop that adds the filenames?
_________________
RE: other Brian Duffy. It would have been too big of a coincidence, but now I'll always think of all BDs as sharp, patient engineers. :)0 -
Hi Stephen, once again thank you for your help. Every record has a link to the file, I think one of my issues was that the search results were only displaying the name of the file without a way to get the internal id. I added a column to the search that returns the internal id of the file, then in the 'Destinations & Lookups' I'm doing NetSuite - Lookup files, to get the internal id of the file and then destination of FTP. I successfully retrieve the records but then get an error, I believe, on the FTP import:
code: pre_import_validation_failed
message: [{"message":"required sub schema, imp.file.xml is missing.","source":"connector","code":"missing_required_field","resolved":false,"occurredAt":1614187474625}]
0 -
You've encountered the "rare" undocumented error. I'm looking further into it, tracing similar resolutions of "pre_import_validation_failed" in our support logs, and making inquiries at Celigo.
I'll try to post a follow-up status within a day or two. (To set your expectations, it's a very uncommon error message. The answer most likely will be to recreate the flow or to submit a support ticket so that we can troubleshoot the flow as a whole.)
0 -
Hi Stephen,
Thank you, please let me know what you find and if I should open a ticket
Brian
0 -
Hey, Brian Duffy. A pattern hasn't emerged in the few cases I was able to find. I'll keep this thread updated if anything turns up.
I had hoped to be be able to offer you something in this forum, but your best and quickest track for troubleshooting would be through customer support.
0 -
@Stephen Brandt I'm having the same issue. The Customer Support is puzzled..
0 -
Hi, Aleks Frolovs. I'm following your ticket and Support's attempt to reproduce the error without being able to look at your customer's integration. In the best case, you'll have a quick resolution during your scheduled meeting.
I'll try to keep this thread updated with possible causes and troubleshooting tips for this error, and other Community members are welcome to contribute, as well.
In the previous post, the problem was solved by correcting the case of the lookup response field in the response mapping: from data[0].blobkey to data[0].blobKey.
0 -
Stephen Brandt thank you for coming back to me.
I have following in lookup response mapping..
0 -
Is there any update about this error? I'm having the same message error and I already reviewed my response mapping.
Best Regards
0 -
ARTURO DURAN MORENO, I definitely recommend submitting your question to support.
There wasn't a pattern that I could discern, looking through the support queue for tickets related to the posts above.
0 -
Thanks Stephen Brandt, I already submitted a ticket.
Thanks a lot
0
Please sign in to leave a comment.
Comments
15 comments