One to Many using non-standard api response pattern on a CSV

Comments

8 comments

  • Tyler Lamparter Principal Product Manager
    Awesome Follow-up
    Engaged
    Top Contributor
    Answer Pro
    Celigo University Level 4: Legendary

    Nicholas White are you generating an Amazon report, then grabbing the results of the report? If so, why not have all this setup on the initial source step and utilize async helper?

    0
  • Nicholas White

    Tyler Lamparter Thanks for checking this!

    I'll be grabbing a report that's already been generated in Amazon on a schedule so I don't need to send a request and wait for the report to generate.

    0
  • Tyler Lamparter Principal Product Manager
    Awesome Follow-up
    Engaged
    Top Contributor
    Answer Pro
    Celigo University Level 4: Legendary

    Nicholas White when using a lookup, the result mapping will always tie back to the initial record count that came from the source side of the flow. For your example, you have 1 record from the source, which is the completed report details, then you have a lookup to get all the results of the document. When you result map it, you have 1 record and 1 page, but now you have a transaction_lines array of arrays within the 1 record. Given this, you would have to use one-to-many path on the NetSuite step and your one-to-many path would be transaction_lines. 

     

    With this setup, you potentially may run into issues due to page sizing. The max size of a page is about 5 MB, so if the results of your settlement report exceed that, then it may error out. You can try it out like this and see how it goes, but if you run into this issue, then you would need to configure it differently. I can think of 2 ways to rework it to handle this:

    1. Don't use a pre generated report and instead have the export step generate the report and use async helper to get the results. I'm not 100% sure that this is an option for settlements, but I know it works for most other reports.
    2. Have 2 flows. The first flow gets the list of completed reports and then updates a settings field for document id that is set up on flow 2. After flow 1 completes, flow 2 starts and then uses the settings field in the relative url of the export step to get all the results of the document. By doing this, you won't run into any 5 MB page limit and all grouped data will be considered a record. 
    0
  • Nicholas White

    Thanks Tyler Lamparter

    More than happy to set up number 2. That would definitely work.

    Do you have an article on using dynamic settings fields/updating the settings based on the value from a flow's data?

    I have not been able to find any articles on this.

    0
  • Tyler Lamparter Principal Product Manager
    Awesome Follow-up
    Engaged
    Top Contributor
    Answer Pro
    Celigo University Level 4: Legendary

    Nicholas White it would look something like this (also here is the integration zip file):

     

    Modify this red line value with the export id that is setup on flow #2.

     

    For the post body, update red line with export id of flow 2 and update green line with connection id of your amazon connection.

    {
    "_id": "",
      "name": "Get report results",
      "unencrypted": {
        "apiType": "Amazon-SP-API"
      },
    "_connectionId": "",
      "apiIdentifier": "e43b531afc",
      "asynchronous": true,
      "oneToMany": false,
      "sandbox": false,
      "http": {
        "relativeURI": "/reports/2021-06-30/documents/\{{settings.export.reportDocumentId}}",
        "method": "GET",
        "successMediaType": "csv",
        "formType": "http"
      },
      "file": {
        "type": "csv",
        "csv": {
          "columnDelimiter": "\t",
          "rowDelimiter": "\n",
          "hasHeaderRow": true,
          "trimSpaces": true,
          "rowsToSkip": 0
        }
      },
      "settings": {
        "reportDocumentId": "{{record.reportDocumentId}}"
      },
      "adaptorType": "HTTPExport"
    }

     

     

     

     

    0
  • Nicholas White

    Absolutely glorious, 

    Thank you Tyler Lamparter.

    That worked perfectly.

    1
  • Tyler Lamparter Principal Product Manager
    Awesome Follow-up
    Engaged
    Top Contributor
    Answer Pro
    Celigo University Level 4: Legendary

    Nicholas White I have an updated method on another thread. Here you go! https://docs.celigo.com/hc/en-us/community/posts/19915347098267-Amazon-Settlement-200K-lines-file

    0
  • Dave Guderian
    Engaged
    Celigo University Level 4: Legendary
    Awesome Follow-up

    Tyler - I'm struggling to understand how to customize this code for Hubspot. I'm assuming this is custom for Amazon and would need to be modified for HubSpot?

    My flow would look something like this (I think!):

    Flow 1: Export company properties from HubSpot > Integrator.io

    Flow 2: Get companies > AWS

     

     

    0

Please sign in to leave a comment.