Layering new source data fields to existing arrayed payload
I'm building a flow that is exporting data from Microsoft Business Central. The original export data is below. Through multiple lookup steps following the export, I'm trying to add more fields to the payload without changing the format. Simply put, I'm adding one new field within each array element at each lookup step and will be imported to a Google Sheet. Each element represents a cell in a column, the array object is a row. I'm having a hard time determining when to use one to many, path to records, and how to reference the fields in the results mappings to pass the field into the data. Could I get some help here?
{
"page_of_records": [
{
"record": {
"@odata.context": "https://api.businesscentral.dynamics.com/v2.0/4f5e3a1e-e76c-43c7-9b50-d8c5eca4b71e/Production/ODatav4/$metadata#Company('Groove%20Life')/ExcelTemplateIncomeStatement",
"value": [
{
"@odata.etag": "W/\"JzIwOzE0NzU2NDM1MjIwOTIxNzM3MTgyMTswMDsn\"",
"lineNumber": 10000,
"display": "Income",
"netChange": 0,
"lineType": "header",
"indentation": 0,
"dateFilter": "2022-12-31"
},
{
"@odata.etag": "W/\"JzIwOzE0ODA0MjE4OTIyMDI2ODQ4MjgwMTswMDsn\"",
"lineNumber": 20000,
"display": "Ecom Income",
"netChange": 4780974,
"lineType": "total",
"indentation": 1,
"dateFilter": "2022-12-31"
},
{
"@odata.etag": "W/\"JzIwOzE0NzYyODI2OTA5MTk1NzI2MzczMTswMDsn\"",
"lineNumber": 30000,
"display": "Marketplace Income",
"netChange": 2202981,
"lineType": "total",
"indentation": 1,
"dateFilter": "2022-12-31"
},
-
Chris Huovinen on your lookups, what value(s) do you expect to be returned? Is each lookup just getting another field that you need to add into the list of values you already have for that record? For example, on your first step you get an array of 3 elements and each element is a column within the same row. When you make the lookup, are you getting a 4th column for that row? If so, you wouldn't need to specify a one-to-many path because you only need one more column (or multiple columns) returned for the same row.
One other thing that may help is flattening out the data. Since your single record has an array of columns (aka elements), you could flatten it a bit with a transform step prior to going to any lookups. One question there would be is every element within the returned value array always in the same position? For example, is Ecom Income always the second element in the array? If so, you could use the simple transform to flatten it. If not, a script could flatten it or we try to build this without flattening it.0 -
I appreciate the response. Yes that's correct. I am trying to add another field to the list of values for each set in the array.
The other piece I forgot to mention was that the lookup URI is filtering on the display field so the net change field for each subsequent look up is added to the correct set in the array. Now that I'm thinking about it, that may not be the right logic. How could I ensure the net change for the look up is added to the appropriate set in the array based on the display field.
My lookup relative URI is - /ExcelTemplateIncomeStatement?$filter=dateFilter%20ge%202022-01-01%20and%20dateFilter%20le%202022-12-31%20and%20display%20eq%20%27{{value[*].display}}%27&$select=netChange
This is what defaults but it throws an error. I change it to this {{display}} and it only returns a single set of data which makes me think there must be some other way to tell it to run through the array to retrieve the {{netChange}} for each {{display}}
0 -
Chris Huovinen, gotcha that helps a bit more. In that case, you would want to specify your one-to-many path as value like you have. I assume you only get 1 netChange value for each display field type? If so, then you probably just need your response map to be data.0.value.0.netChange = YTDnetChange (or data[0].value[0].netChange = YTDnetChange).
That will automatically map the netChange into the correct value array element. When you enable one-to-many path, just about everything within the lookup or import step converts its context to be in that path. So the lookup will make an api call for the first element in the value array, response map it to that element, then make the next call.0 -
Met with Chris Huovinen on Friday. Each value element in the returned array needed to have the netChange looked up on the lookup step. We switch some of the configuration for handlebar expressions to AFE2.0, added a path to records for the lookups, and then modified the result mapping to map the returned netChange into each display type in the original value array.
0 -
We published a brand new template in our marketplace that makes it super easy to work with Google Sheets. We published a new article too explaining all the details (here). Hope this helps.
0
Please sign in to leave a comment.
Comments
5 comments