Compose output into an array or html table

[Rosie]

I want to get the records from a related table in dataverse and produce a table to go into an email.

Container ID  Bladder No  Seal No

ABC00111      BN0011        SN005548

ABD00445      BN0445       SN015581

I am able to get the related table information using Get Row by ID, then List Rows.

When I tried to use the output from the List Rows set I don’t get the fields showing as dynamic content, so I can’t append them to an array.

I was provided with a way to get the records in this post, using a Compose action using an xpath expression to retrieve the data from the List Row step.

The compose output is an array but seems to be treated like a string.

I have tried to remove the square brackets using another compose action but I get an error.

When the compose outputs are appended to the array, each output is one record.

How do I take the 3 sets of compose outputs and turn them into a table?

Many thanks

Rosie

[abm]

Hi @Rosie 

Do you want to turn into one table header as ContainerID, BladderNol SealNo and then all three rows as next lines?

If its three different tables then its much easier if not its going to be a custom implementation of the data which you need to combine in a different format (JSON array).

Thanks

[Rosie]

Hello,

Ideally one table but that sounds very complicated.

If it ends up looking the same then three tables might be ok.

Thanks

Rosie

[abm · Accepted solution]

Hi @Rosie

Yes the one table you may need to iterate all the values at the same time and construct a new JSON object.

If its separate table then see below.

Duplicate the same steps for BladderNo and SealNo.

Finally you can map all the three outs of Create HTML table in your email.

Thanks

[Rosie]

Thanks so much, that helps me get further along with a better formatted result.

Ultimately I would like to combine them into one table.

Is there a good resource you know of I could follow on how to do that?

[abm]

Hi @Rosie 

Could you provide an example of how you like to show the table header and values?

Thanks

[grantjenkins]

@Rosie Are you able to show your JSON output from your List rows - ContainerDetails action? If you could add the output in your post as a code sample </> then I could copy the data. I think we can extract out this data directly from that action into objects that you can then use within your HTML table.

[Rosie]

Hello, I have to click download to get the output, for some reason it does not display the output link.

I put in a Parse json step so I could more easily see the output.

Here it is from the List rows step:

{"statusCode":200,"headers":{"Vary":"Accept-Encoding","x-ms-service-request-id":"507dd382-d0d9-4173-b101-9f85af3f7f8f,1b427fcf-758d-44ef-8559-e6a5efaf5dfe","Cache-Control":"no-cache","Set-Cookie":"ARRAffinity=70b4fb972ddbadf5335293fdaecf0f9b6af12694d3ca8d2e4052f297da06add2eb29bb4b19df1459d2ea781b605f33064b9fea1f144b525b9d7676625c4ffb5408DB098088D1FD6D1356514174; domain=portnelsonuat.crm6.dynamics.com; path=/; secure; HttpOnly,ReqClientId=ad02bab0-394f-44d3-bd16-9527ae1c1651; expires=Wed, 08-Feb-2073 02:29:45 GMT; path=/; secure; HttpOnly,ARRAffinity=70b4fb972ddbadf5335293fdaecf0f9b6af12694d3ca8d2e4052f297da06add2eb29bb4b19df1459d2ea781b605f33064b9fea1f144b525b9d7676625c4ffb5408DB098088D1FD6D1356514174; domain=portnelsonuat.crm6.dynamics.com; path=/; secure; HttpOnly","Strict-Transport-Security":"max-age=31536000; includeSubDomains","REQ_ID":"1b427fcf-758d-44ef-8559-e6a5efaf5dfe","AuthActivityId":"f5a99caa-e222-4cbb-8bad-f628cb7c9920","x-ms-dop-hint":"8","x-ms-ratelimit-time-remaining-xrm-requests":"1,200.00","x-ms-ratelimit-burst-remaining-xrm-requests":"5999","OData-Version":"4.0","Preference-Applied":"odata.include-annotations=\"*\"","X-Source":"311765219464208317923620416615596179611941772451318820614017514422510410158248214541,23583104222159105109103993252108207014714030165219513323148311872310118510983132","Public":"OPTIONS,GET,HEAD,POST","Date":"Wed, 08 Feb 2023 02:29:47 GMT","Allow":"OPTIONS,GET,HEAD,POST","Content-Type":"application/json; odata.metadata=full","Expires":"-1","Content-Length":"3936"},"body":{"@odata.context":"https://portnelsonuat.crm6.dynamics.com/api/data/v9.1/$metadata#qp_orderses(qp_order,qp_Containers_qp_Orders_qp_Orders(qp_containerid,qp_bladderno,qp_sealno))","#Microsoft.Dynamics.CRM.DeleteMultiple":{"title":"DeleteMultiple","target":"https://portnelsonuat.crm6.dynamics.com/api/data/v9.1/qp_orderses/Microsoft.Dynamics.CRM.crmbaseentity/Microsoft.Dynamics.CRM.DeleteMultiple"},"@Microsoft.Dynamics.CRM.totalrecordcount":-1,"@Microsoft.Dynamics.CRM.totalrecordcountlimitexceeded":false,"@Microsoft.Dynamics.CRM.globalmetadataversion":"60420318","value":[{"@odata.type":"#Microsoft.Dynamics.CRM.qp_orders","@odata.id":"https://portnelsonuat.crm6.dynamics.com/api/data/v9.1/qp_orderses(e58db826-58a7-ed11-aad0-0022481438cf)","@odata.etag":"W/\"60420442\"","@odata.editLink":"qp_orderses(e58db826-58a7-ed11-aad0-0022481438cf)","qp_order":"Test Feb Pick Up 01","qp_ordersid@odata.type":"#Guid","qp_ordersid":"e58db826-58a7-ed11-aad0-0022481438cf","qp_Containers_qp_Orders_qp_Orders@odata.associationLink":"https://portnelsonuat.crm6.dynamics.com/api/data/v9.1/qp_orderses(e58db826-58a7-ed11-aad0-0022481438cf)/qp_Containers_qp_Orders_qp_Orders/$ref","qp_Containers_qp_Orders_qp_Orders@odata.navigationLink":"https://portnelsonuat.crm6.dynamics.com/api/data/v9.1/qp_orderses(e58db826-58a7-ed11-aad0-0022481438cf)/qp_Containers_qp_Orders_qp_Orders","qp_Containers_qp_Orders_qp_Orders@odata.type":"#Collection(Microsoft.Dynamics.CRM.qp_containers)","qp_Containers_qp_Orders_qp_Orders":[{"@odata.type":"#Microsoft.Dynamics.CRM.qp_containers","@odata.id":"https://portnelsonuat.crm6.dynamics.com/api/data/v9.1/qp_containerses(25d77134-58a7-ed11-aad0-00224814f88b)","@odata.etag":"W/\"60420440\"","@odata.editLink":"qp_containerses(25d77134-58a7-ed11-aad0-00224814f88b)","qp_containerid":"FEBU0001","qp_bladderno":"BL00019","qp_sealno":"SL0019","_qp_orders_value@OData.Community.Display.V1.FormattedValue":"Test Feb Pick Up 01","_qp_orders_value@Microsoft.Dynamics.CRM.associatednavigationproperty":"qp_Orders","_qp_orders_value@Microsoft.Dynamics.CRM.lookuplogicalname":"qp_orders","_qp_orders_value@odata.type":"#Guid","_qp_orders_value":"e58db826-58a7-ed11-aad0-0022481438cf","qp_containersid@odata.type":"#Guid","qp_containersid":"25d77134-58a7-ed11-aad0-00224814f88b","qp_Orders@odata.associationLink":"https://portnelsonuat.crm6.dynamics.com/api/data/v9.1/qp_containerses(25d77134-58a7-ed11-aad0-00224814f88b)/qp_Orders/$ref","qp_Orders@odata.navigationLink":"https://portnelsonuat.crm6.dynamics.com/api/data/v9.1/qp_containerses(25d77134-58a7-ed11-aad0-00224814f88b)/qp_Orders"},{"@odata.type":"#Microsoft.Dynamics.CRM.qp_containers","@odata.id":"https://portnelsonuat.crm6.dynamics.com/api/data/v9.1/qp_containerses(e0e36b40-58a7-ed11-aad0-00224814f88b)","@odata.etag":"W/\"60420433\"","@odata.editLink":"qp_containerses(e0e36b40-58a7-ed11-aad0-00224814f88b)","qp_containerid":"FEBU0002","qp_bladderno":"BLN00029","qp_sealno":"SLN0029","_qp_orders_value@OData.Community.Display.V1.FormattedValue":"Test Feb Pick Up 01","_qp_orders_value@Microsoft.Dynamics.CRM.associatednavigationproperty":"qp_Orders","_qp_orders_value@Microsoft.Dynamics.CRM.lookuplogicalname":"qp_orders","_qp_orders_value@odata.type":"#Guid","_qp_orders_value":"e58db826-58a7-ed11-aad0-0022481438cf","qp_containersid@odata.type":"#Guid","qp_containersid":"e0e36b40-58a7-ed11-aad0-00224814f88b","qp_Orders@odata.associationLink":"https://portnelsonuat.crm6.dynamics.com/api/data/v9.1/qp_containerses(e0e36b40-58a7-ed11-aad0-00224814f88b)/qp_Orders/$ref","qp_Orders@odata.navigationLink":"https://portnelsonuat.crm6.dynamics.com/api/data/v9.1/qp_containerses(e0e36b40-58a7-ed11-aad0-00224814f88b)/qp_Orders"}],"qp_Containers_qp_Orders_qp_Orders@odata.nextLink":"https://portnelsonuat.crm6.dynamics.com/api/data/v9.1/qp_orderses(e58db826-58a7-ed11-aad0-0022481438cf)/qp_Containers_qp_Orders_qp_Orders?$select=qp_containerid,qp_bladderno,qp_sealno"}]}}

This is from the parse json step:

In the dynamic content I only get the Body and Item fields

{
    "body": [
        {
            "@odata.type": "#Microsoft.Dynamics.CRM.qp_orders",
            "@odata.id": "https://portnelsonuat.crm6.dynamics.com/api/data/v9.1/qp_orderses(e58db826-58a7-ed11-aad0-0022481438cf)",
            "@odata.etag": "W/\"60420442\"",
            "@odata.editLink": "qp_orderses(e58db826-58a7-ed11-aad0-0022481438cf)",
            "qp_order": "Test Feb Pick Up 01",
            "qp_ordersid@odata.type": "#Guid",
            "qp_ordersid": "e58db826-58a7-ed11-aad0-0022481438cf",
            "qp_Containers_qp_Orders_qp_Orders@odata.associationLink": "https://portnelsonuat.crm6.dynamics.com/api/data/v9.1/qp_orderses(e58db826-58a7-ed11-aad0-0022481438cf)/qp_Containers_qp_Orders_qp_Orders/$ref",
            "qp_Containers_qp_Orders_qp_Orders@odata.navigationLink": "https://portnelsonuat.crm6.dynamics.com/api/data/v9.1/qp_orderses(e58db826-58a7-ed11-aad0-0022481438cf)/qp_Containers_qp_Orders_qp_Orders",
            "qp_Containers_qp_Orders_qp_Orders@odata.type": "#Collection(Microsoft.Dynamics.CRM.qp_containers)",
            "qp_Containers_qp_Orders_qp_Orders": [
                {
                    "@odata.type": "#Microsoft.Dynamics.CRM.qp_containers",
                    "@odata.id": "https://portnelsonuat.crm6.dynamics.com/api/data/v9.1/qp_containerses(25d77134-58a7-ed11-aad0-00224814f88b)",
                    "@odata.etag": "W/\"60420440\"",
                    "@odata.editLink": "qp_containerses(25d77134-58a7-ed11-aad0-00224814f88b)",
                    "qp_containerid": "FEBU0001",
                    "qp_bladderno": "BL00019",
                    "qp_sealno": "SL0019",
                    "_qp_orders_value@OData.Community.Display.V1.FormattedValue": "Test Feb Pick Up 01",
                    "_qp_orders_value@Microsoft.Dynamics.CRM.associatednavigationproperty": "qp_Orders",
                    "_qp_orders_value@Microsoft.Dynamics.CRM.lookuplogicalname": "qp_orders",
                    "_qp_orders_value@odata.type": "#Guid",
                    "_qp_orders_value": "e58db826-58a7-ed11-aad0-0022481438cf",
                    "qp_containersid@odata.type": "#Guid",
                    "qp_containersid": "25d77134-58a7-ed11-aad0-00224814f88b",
                    "qp_Orders@odata.associationLink": "https://portnelsonuat.crm6.dynamics.com/api/data/v9.1/qp_containerses(25d77134-58a7-ed11-aad0-00224814f88b)/qp_Orders/$ref",
                    "qp_Orders@odata.navigationLink": "https://portnelsonuat.crm6.dynamics.com/api/data/v9.1/qp_containerses(25d77134-58a7-ed11-aad0-00224814f88b)/qp_Orders"
                },
                {
                    "@odata.type": "#Microsoft.Dynamics.CRM.qp_containers",
                    "@odata.id": "https://portnelsonuat.crm6.dynamics.com/api/data/v9.1/qp_containerses(e0e36b40-58a7-ed11-aad0-00224814f88b)",
                    "@odata.etag": "W/\"60420433\"",
                    "@odata.editLink": "qp_containerses(e0e36b40-58a7-ed11-aad0-00224814f88b)",
                    "qp_containerid": "FEBU0002",
                    "qp_bladderno": "BLN00029",
                    "qp_sealno": "SLN0029",
                    "_qp_orders_value@OData.Community.Display.V1.FormattedValue": "Test Feb Pick Up 01",
                    "_qp_orders_value@Microsoft.Dynamics.CRM.associatednavigationproperty": "qp_Orders",
                    "_qp_orders_value@Microsoft.Dynamics.CRM.lookuplogicalname": "qp_orders",
                    "_qp_orders_value@odata.type": "#Guid",
                    "_qp_orders_value": "e58db826-58a7-ed11-aad0-0022481438cf",
                    "qp_containersid@odata.type": "#Guid",
                    "qp_containersid": "e0e36b40-58a7-ed11-aad0-00224814f88b",
                    "qp_Orders@odata.associationLink": "https://portnelsonuat.crm6.dynamics.com/api/data/v9.1/qp_containerses(e0e36b40-58a7-ed11-aad0-00224814f88b)/qp_Orders/$ref",
                    "qp_Orders@odata.navigationLink": "https://portnelsonuat.crm6.dynamics.com/api/data/v9.1/qp_containerses(e0e36b40-58a7-ed11-aad0-00224814f88b)/qp_Orders"
                }
            ],
            "qp_Containers_qp_Orders_qp_Orders@odata.nextLink": "https://portnelsonuat.crm6.dynamics.com/api/data/v9.1/qp_orderses(e58db826-58a7-ed11-aad0-0022481438cf)/qp_Containers_qp_Orders_qp_Orders?$select=qp_containerid,qp_bladderno,qp_sealno"
        }
    ]
}

[grantjenkins · Accepted solution]

Hopefully this will get what you're looking for. If this isn't what you're after or have any issues - let me know.

See full flow below. I'll go into each of the actions.

List rows - ContainerDetails is the same as what you already have.

Select takes the output from List rows - ContainerDetails and maps the fields you want in your HTML table. Below are the expressions used:

//From
first(outputs('List_rows_-_ContainerDetails')?['body/value'])?['qp_Containers_qp_Orders_qp_Orders']

//Container ID
item()?['qp_containerid']

//Bladder No
item()?['qp_bladderno']

//Seal No
item()?['qp_sealno']

Create HTML table uses the output from the Select.

This would give you the following output.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

[Rosie]

Thanks so much @grantjenkins.

The select is very interesting and when I was following @abm solution I thought it must be possible to use this with multiple values, I tried but didn't succeed.

Thanks so much for the time you have given me.