Hello!
I am trying to create an HTML Table that uses several columns from a Sharepoint and turns them into multiple rows.
Example:
In Sharepoint:
Column 1 | Column 2 | Column 3 | Column 4 |
Total | Total | Total | Total |
In HTML Table
Column 1 | Total |
Column 2 | Total |
Column 3 | Total |
Column 4 | Total |
Is there a way to do this? I am using the Send an HTTP Request to Sharepoint function in order to get the totals from a view. In theory, I would actually like to be using "count if" so that it looks like this in an HTML Table with 3 columns and multiple rows.
Yes | No | |
Column 1 | Total | Total |
Column 2 | Total | Total |
Column 3 | Total | Total |
Column 4 | Total | Total |
Thanks!
@FLMike I am currently getting a response that looks like this:
{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":1,"Value":"No"} {"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":0,"Value":"Yes"}
No | Yes |
Is there something I should be doing to get totals? Also, once I try to add a 3rd category, the test fails when I run it. I feel like it's getting closer, but there must be a step that I'm missing.
Thanks!
@RobElliott Do you think your initial suggestion will work with what I am trying to do? Or is there a better way to do it? Thanks!
I didn't think @dsopshin was using it right now (and seems his response agrees), but yes I definitely get that and you are correct if he was.
I think the idea would be to use a calculated column, though I'm open to doing it other ways. I would need to find a way to get the total of the "yes" and the "no" responses in the column. Is that possible?
@FLMike I suspect the reason the poster is not using the get items is that the total is a calculated column which you can't use in a filter query.
Hi @dsopshin
May I ask why you aren't using the Get Items action to get the rows?
But this is how you would do it
1. Create a view with just those columns (not understand this doesn't solve our problem perfectly yet)
2. Use Get items (SharePoint Action) with a Filter Query, to get the rows you want (or later on we can use a Filter Action)
OPTIONS
Option 1
3A Assuming that you go ONLY the rows you wanted from SharePoint, we need to do 2 more steps.
--Add a Select Action and configure the Key/Value pairs to ONLY be the columns you wanted (the reason for this is, even with a view, you still get back system columns you do not want in your table
--Add a Create HTML Table Action, and add the Output from your above Select to the Input of the Create HTML Table action and BINGO you are done 🙂
Option 2
3B Assuming you got MORE rows than you intended, because you were NOT able to write a Filter Query
--Add a Filter Action
----In the filter action, you would need to use the Dynamic Content "value" property of the Step 2 Get Items as your input
----Then in the Mappings, you would want to click the Advanced button and write a Filter Expression, which would limit your rows based on email address but this time it would look like this
------
equals([Email Address],ValueFromStep2)
And this would make your rows be only the ones that match your email address
----Add a Select
-----Again we want to Select ONLY the rows you want, but this time its from the Filter Action, not the Get Items action
--------We need to Add the Keys and Values (but I need to know which ones you need to show you)
---Add a Create HTML Table and again put the Select as the input
And done
If you like my answer, I would really appreciate if you please Mark it as Resolved, and give it a thumbs up, so it can help others
Cheers
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey