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

[nicholaswhite]

I'm using a non-standard API response pattern to group my import data into rows.

I shouldn't need to use the one-to-many as the data is now grouped into an array of arrays. In the preSave function on this lookup the options.data, when printed to the console/logs, is an array of arrays as well. IO should treat each item within that array as a separate "record".

I'm then mapping the data back to the original data:

When the lookup step is complete though, I'm still only getting 1 record being imported instead of the 2+ records I'd like to be importing based on the grouped lookup data.

Any idea what i'm missing here to get this lookup to split into multiple records? I've tried changing the results mapping to data[*] but that doesn't return anything.

[tylerlamparter]

@nicholaswhite 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?

[nicholaswhite]

@tylerlamparter 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.

[tylerlamparter]

@nicholaswhite 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.

[nicholaswhite]

Thanks @tylerlamparter

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.

[tylerlamparter]

@nicholaswhite 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"
}

[nicholaswhite]

Absolutely glorious,

Thank you @tylerlamparter.

That worked perfectly.

[tylerlamparter]

@nicholaswhite I have an updated method on another thread. Here you go! https://connective.celigo.com/t/amazon-settlement-200k-lines-file/318

[daveguderian]

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