Process data in batches per week

iPaaS Transformers Data Transformers
1

Fellow Alumio-users,

I have a route which is configured to send e-mail requests containing data from our ERP to about a thousand suppliers. Our procurement-department wants to regulate on a weekly basis to which suppliers the requests will be send that week. Preferably procurement uploads a list of suppliers each week but alternatively we split the suppliers in batches of about 50 per week.

I tried to use an Excel-sheet in SharePoint which can be filled by procurement but reading a file from SharePoint using graph API is a lot more difficult than writing a file. Microsoft uses a redirect when calling the API which is not followed by Alumio.

I can create a flow in Microsoft 365 that copies the Excel-sheet to Azure blobstorage and read the file from there but am reluctant to use multiple processes that are dependent on but not aware of each other.

Does anyone have some experience with a similar challenge or has anyone succeeded in reading a file from SharePoint?

Alternatively, does anyone know how to filter all but the first X records from a recordset using an entity transformer? (This would allow me to get all suppliers each week, filter those who are already processed based on presense in storage entity, keep only the X records from the remaining data, proces those and write them to starage entity so they are filtered out in the next run).

Thanks for any insights

2

Hi @maxim.vanluttikhuize

Welcome to the Alumio forum!

Regarding your question, could you please provide us with the documentation to the SharePoint API you would like to use?

As for your question to filter all but the first X records from a record set, you could try the JMESPath approach. For example, we have the below entity data.

{
  "data": [
    {
      "id": 1
    },
    {
      "id": 2
    },
    {
      "id": 3
    },
    {
      "id": 4
    },
    {
      "id": 5
    }
  ]
}

We can filter the first three records by using the slice projections introduced by JMESPath. We can put the JMESPath syntax in a Value Setter, such as below.

image
image1035×332 17.7 KB

This way, Alumio will filter all records except the first three records. The syntax is &{data[:3]}. You can freely change the number 3 to any number you want.

{
  "data": [
    {
      "id": 1
    },
    {
      "id": 2
    },
    {
      "id": 3
    }
  ]
}

Please give it a try and let us know if this is not the case.

3

Gugi,

Thanks for the slice projections. This wil work for this issue.
As to the documentation concerning reading files from SharePoint:

I tried the request: /sites/{siteId}/drive/items/{item-id}/content