Pull file from NetSuite custom record to an FTP server, change a custom select field on that custom record after file has been pulled

Comments

15 comments

  • Stephen Brandt Documentation Manager Community moderator
    Engaged
    Great Answer
    Top Contributor
    Celigo University Level 3: Master

    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
  • Brian Duffy Salesforce Integration Whiz NetSuite Integration Whiz
    Celigo University Level 4: Legendary
    Engaged

    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
  • Stephen Brandt Documentation Manager Community moderator
    Engaged
    Great Answer
    Top Contributor
    Celigo University Level 3: Master

    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:

    1. Export Pending Upload orders based on a NetSuite Saved Search, including the file ids
    2. NetSuite import – with a file download to retrieve the files from step 1
    3. FTP import – to upload these files
    4. Import records to NetSuite – use static field mapping to change the value of the custom record in each order

     

    0
  • Brian Duffy Salesforce Integration Whiz NetSuite Integration Whiz
    Celigo University Level 4: Legendary
    Engaged

    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
  • Stephen Brandt Documentation Manager Community moderator
    Engaged
    Great Answer
    Top Contributor
    Celigo University Level 3: Master

    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
  • Brian Duffy Salesforce Integration Whiz NetSuite Integration Whiz
    Celigo University Level 4: Legendary
    Engaged

    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
  • Stephen Brandt Documentation Manager Community moderator
    Engaged
    Great Answer
    Top Contributor
    Celigo University Level 3: Master

    Brian Duffy,

    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
  • Brian Duffy Salesforce Integration Whiz NetSuite Integration Whiz
    Celigo University Level 4: Legendary
    Engaged

    Hi Stephen,

    Thank you, please let me know what you find and if I should open a ticket

    Brian

    0
  • Stephen Brandt Documentation Manager Community moderator
    Engaged
    Great Answer
    Top Contributor
    Celigo University Level 3: Master

    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
  • Aleks Frolovs Referral Partner
    Celigo University Level 4: Legendary
    Engaged

    @Stephen Brandt I'm having the same issue. The Customer Support is puzzled.. 

    0
  • Stephen Brandt Documentation Manager Community moderator
    Engaged
    Great Answer
    Top Contributor
    Celigo University Level 3: Master

    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
  • Aleks Frolovs Referral Partner
    Celigo University Level 4: Legendary
    Engaged

    Stephen Brandt thank you for coming back to me. 

    I have following in lookup response mapping.. 

     

    0
  • ARTURO DURAN MORENO
    Engaged
    Celigo University Level 4: Legendary

    Hi Stephen Brandt

    Is there any update about this error? I'm having the same message error and I already reviewed my response mapping.

    Best Regards

    0
  • Stephen Brandt Documentation Manager Community moderator
    Engaged
    Great Answer
    Top Contributor
    Celigo University Level 3: Master

    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
  • ARTURO DURAN MORENO
    Engaged
    Celigo University Level 4: Legendary

    Thanks Stephen Brandt, I already submitted a ticket.

    Thanks a lot 

    0

Please sign in to leave a comment.