Power Automate - Get Items Parameters
Tags: powerautomate, rpa, lowcode, powerplatform
Published: 2024-05-13 05:33:32

Get Items in SharePoint and List Items in Dataverse are fundamental to a lot of flows, allowing easy ingress of data. They both work on the OData standard, though still having slight variations, which makes it easy to learn both.

OData is a API standard not just for Power Automate, so its easy to find documentation about it. So in this blog Im going to talk about the API and then link it to Power Automate (that way is easier to expand your learning).

The key settings that impact our Get Items is the query parameters. This is when we add a ? to our GET url and add a kind of formula to change how the data is returned. OData has 6 standard formulas when used in the Power Platform, they are:

- $filter
- $expand
- $select
- $orderBy
- $top
- $skip/$skiptoken

---

## $filter
Filter is probably the most useful, it allows you to return specific records.

At its basic it is:

`$filter = field eq 'value'`

eq represents = (equals)

| Query       | Represents | Description                        |
|-------------|------------|------------------------------------|
| eq          | =          | equals                             |
| ne          | <> / !=    | not equal to                       |
| gt          | >          | greater than                       |
| ge          | >=         | greater than or equal to           |
| lt          | <          | less than                          |
| le          | <=         | less then or equal to              |
| startswith  | left       | has same first characters          |
| substringof | contains   | has characters within string       |
| day         | dd         | dateTime has this day of the month |
_there is also month,year,hour,minute,second that match the day structure_

So if I wanted to return all records where the value was greater than 10 it would be:

`$filter = value ge 10`
_note that there is now no ', as this is only for strings, not integers or booleans_

You can also chain your formulas with and/or.

So if I wanted value equals 10 and name not David it would be:
`$filter = value eq 10 and name ne 'David'`

Or if I wanted value equals 10 and name is David or Laura it would be:
`$filter = value eq 10 and (name eq 'David' or name eq 'Laura')`

To to use in Power Automate, the Get Items adds a drop down but you can flip to raw import and build out your more complex formulas here:

![change to manual filter](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/aov16q3o90dhwm58gnvh.gif)

For Dataverse at the moment it's just the formula:

![dataverse filter](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ygumzlmrt3pq1bx9ejte.png)


**$expand**
Expand allows you to bring in any lookup values. So let's say you have 2 tables one with sales, one with item details. The item field in the sales table might be a lookup of the items in the item table. If we wanted to see all of the fields in the item table we can expand the item field.

Without you would get:
```
value:{
  "date":"10/01/24",
  "item":1,
  "advisor":2
}
```

But with expand you get:
```
value:{
  "date":"10/01/24",
  "item":{
    "id":1,
    "description":"pen",
    "cost":4
    },
    "advisor":2
}
```

To use it we just add the field name, if we want more then one we add a comma:

`$expand = item, advisor`

![dataverse expand](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/x26gkrihrl2ug4sohqne.png)

This is how it pretty much works directly in the Dataverse action (though be sure to get the field name not display name), the lookups are then returned as a flat response (ie they are not nested under it).

```
value:{
  "date":"10/01/24",
  "item":1,
  "_item_value@description":"pen",
  "_item_value@cost":4,
  "advisor":2
}
```

But it's different in the SharePoint action (unless using the SharePoint HTTP action).There is no expand option, instead the expand is delegated to the list in the site. When creating a lookup column/field you have an option to expand what fields you want. These are then returned as there own object.

```
value:{
  "date":"10/01/24",
  "item":{
    "id":1,
    "value":1
    },
  "item_x003a__x0020_description":{
    "id":1,
    "value":"pen"
    },
  "item_x003a__x0020_cost":{
    "id":1,
    "value":4
    },
  "advisor":{
    id":2,
   "value":2
  }
}
```
Luckily this mess is hidden away in the dynamic content selector.

## $select
Select allows you to return the fields you want. Its structured the same as $expand (comma separated list). If left blank it returns all, else just those fields (and defaulted metadata fields).

So:

`$select = date`

will return:
```
value:{
  "date":"10/01/24"
}
```

Again this is exactly how it is in Dataverse action, but not in SharePoint action.

In SharePoint again you delegate to the list site again, but this time by creating a list view. You then select that in the action and it will return the fields shown in that view.

![list view selector](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qsf6zd52mwvnpnmrv6vy.png)

JSON Response
```
value:{	
  "@odata.etag":"4"'
  "ItemInternalId":"1"
  "ID":1
  "Title":"1"
  "date":"2023-08-05T07:00:00Z"
  "{Identifier}":"Lists%252fTest%2blist%252f1_.000"
  "{IsFolder}":false
  "{Thumbnail}":{…}
  "{Link}":"https://37wcqv.sharepoin…8C39A44BB584B51CE50B8A32"
  "{Name}":"1"
  "{FilenameWithExtension}:"1"
  "{Path}":"Lists/Test list/"
  "{FullPath}":"Lists/Test list/1_.000"
  "{HasAttachments}":false
  "{VersionNumber}":"4.0"
}
```
_as you can see there is lots of metadata and the ID + Title columns always included_

We can also use the / to select a field from an expanded field:

`$select = item/cost`

## $orderBy
Order by is setting which field and direction to sort the returned data.

To use it simply add the field name, a space, and then if its ascending (asc) or descending (desc). Again you can add multiple by using a comma:

`$orderBy = date asc, item desc`

Finally SharePoint and Dataverse actions are the same.

Dataverse
![dataverse sort](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tifr7qs51s0r3j4oiylw.png)

SharePoint
![sharepoint sort](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/alt5rhzx259gueriyrai.png)

## $top
Top allows us to limit the number or rows of data, so if we only wanted the first 10 rows we would use:

`$top = 10`

There is no $bottom, but you can use the $orderBy and reverse the order to return the last rows.

Both Dataverse and SharePoint actions have top, so you just need to add a number (blank will return the maximum page size).

Important call out here, top returns the top n rows in that query, so it does not work with pagination on. Pagination will automatically send additional queries until it hits the threshold. This means if you turn on pagination and have a threshold of 100, even though you set the $top =10, you will still get 100 rows (because it will send 10 requests of 10).

If you want to know more about pagination check out this previous [blog](https://dev.to/wyattdave/understanding-pagination-in-power-automate-4igh).

## $skip/$skiptoken
Skip allows you to start your return from the middle of the data, so if we didn't want the first 10 rows but all of the rest we can skip them.

`$skip= 10`

Although part of OData, $skip is not supported by Dataverse or SharePoint, instead they use a $skiptoken (I do wish it was supported though as feels pretty useful). 

A skiptoken is returned by the server and allows you to skip a block of rows. Its main use is in pagination, where it will use the skiptoken to get the next page.

The SharePoint action doesn't have $skiptoken, but Dataverse action does. Though with automatic pagination it use is very limited.

![datavese skiptoken](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7k6bbwpiizrc32ngavyo.png)

---

## Additional Odata Formulas
There is also [$apply](https://learn.microsoft.com/en-us/power-apps/developer/data-platform/webapi/query-data-web-api#aggregate-data), which allows you to aggregate/group the returned data, and $count, that returns a integer showing number of rows of data, but no data. 

But $apply is not supported by SharePoint and not shown in the Dataverse action, and $count is not supported by either (again wish it was as I think it would be quite useful).

## Additional Inputs
There are also a couple of additional inputs:

**Partition ID (Dateverse)**
This is used for elastic tables (tables which us Cosmos DB which is an file database instead of Azure SQL a relationship database). 
![partition id input](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/197uxfl9upasys8aoii2.png)

I won't go into to much detail, just to say partitions are a key part of file based databases and help mitigate the slower performance of them vs relationship databases. You can read more [here](https://learn.microsoft.com/en-us/power-apps/developer/data-platform/elastic-tables)

**Fetch Xml Query (Dataverse)**
This allows you to build out queries in xml instead of OData (so it can do filters etc). XML is often thought of having been superseded by JSON, but it is what Dynamics (and therefore Dataverse) was originally built on and the Fetch Xml Query ensures backward compatibility. Also you can also build some kick ass queries if you know what you are doing.

**Limit Entries to Folder (SharePoint)**
Only for Libraries (yes libraries are lists under the hood so use the same API), allows you to select a specific folder.

**Include Nested Items (SharePoint)**
Again only for Libraries, this allows you to set the return to just be the library/folder, or every nested folder within that library/folder.

![sharepoint library inputs](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1dql6drt3arr404g4233.png)

---

You can use the SharePoint HTTP action, or the HTTP with Microsoft Entra ID (preauthorized) Invoke HTTP action, to call the GET queries directly, and here you can use all the API documentation to learn even more functionality (If you want to know more about HTTP actions I have a blog on it [here](https://dev.to/wyattdave/power-automate-all-the-http-connectors-and-what-they-do-1b42)).

![http actions](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3srcyyikgzb5fdlknb0w.png)

OData is also used in the Graph API (Microsoft uses for Outlook, Teams, etc), so a lot of above can be transferred across, with the usual small differences between each.