Power Apps portals: Query data using portals Web API (public preview)
Web API operations in portals so far were limited to creating, updating, deleting, associating and disassociating tables. With this public preview, we’re adding the capability to retrieve data using GET requests through portals Web API
Preview capabilities
The feature provides the following capabilities during this public preview:
- Query records
- Apply system query options
- Request specific properties
- Filter results
- Order results
- Aggregate and grouping results
- Retrieve a count of rows
- Column comparison
- Retrieve related table records with a query
How to get started?
Prerequisites
- Your portal version must be 9.3.10.x or later for this feature to work.
- Enable table and field for Web API operations. More information: Site settings for the Web API
- The portals Web API accesses table records and follows the table permissions given to users through the associated web role. Ensure you configure table permissions accordingly. More information: Create web roles
Site setting for Web API read operations
You must configure the site setting WebAPI/enableReadOperationPreview and set its value to True to enable read operations using portals Web API.
Sample Code
Query records
| OPERATION | METHOD | URI |
|---|---|---|
| Retrieve table records | GET | [Portal URI]/_api/accounts
Example: |
| Use $select and $top system query options to return the name property for the first three accounts |
GET | [Portal URI]/_api/accounts?$select=name,revenue&$top=3
Example: |
| Retrieve account by using account ID | GET | [Portal URI]/_api/accounts(e0e11ba8-92f6-eb11-94ef-000d3a5aa607)?$select=name
Example: |
Apply system query options
| OPERATION | METHOD | URI |
|---|---|---|
| Retrieve table records | GET | [Portal URI]/_api/accounts?$select=name,revenue&$filter=revenue gt 90000&$top=3
Example: |
Request specific properties
| OPERATION | METHOD | URI |
|---|---|---|
| Use the $select system query option to limit the properties returned | GET | Portal URI]/_api/accounts?$select=name,revenue&$top=3
Example: |
Filter results
Use the $filter system query option to set criteria for which rows will be returned.
Standard filter operators
| Operator | Description | Example |
|---|---|---|
| Comparison Operators | ||
| eq | Equal | $filter=revenue eq 10000 |
| ne | Not Equal | $filter=revenue ne 10000 |
| gt | Greater than | $filter=revenue gt 10000 |
| ge | Greater than or equal | $filter=revenue ge 10000 |
| lt | Less than | $filter=revenue lt 10000 |
| le | Less than or equal | $filter=revenue le 10000 |
| Logical Operator | ||
| and | Logical and | $filter=revenue lt 100000 and revenue gt 2000 |
| or | Logical or | $filter=contains(name,'(sample)’) or contains(name,’test’) |
| not | Logical negation | $filter=not contains(name,’sample’) |
| Grouping Operators | ||
| () | Precedence grouping | $filter=(contains(name,’sample’) or contains(name,’test’)) and revenue gt 5000 |
Standard query functions
The Web API supports these standard OData string query functions.
| Function | Example |
|---|---|
| contains | $filter=contains(name,'(sample)’) |
| endswith | $filter=endswith(name,’Inc.’) |
| startswith | $filter=startswith(name,’a’) |
Order results
Specify the order in which items are returned using the $orderby system query option. Use the asc or desc suffix to specify ascending or descending order respectively. The default is ascending if the suffix isn’t applied.
| OPERATION | METHOD | URI |
|---|---|---|
| Specify order using orderby | GET | Portal URI]/_api/accounts?$select=name,revenue&$orderby=name asc,revenue desc&$filter=revenue gt 90000Example: https://contoso.powerappsportals.com/_api/accounts?$select=name,revenue&$orderby=name asc,revenue desc&$filter=revenue gt 90000 |
Aggregate and grouping results
By using $apply, you can aggregate and group your data dynamically as seen in the following examples.
| Scenarios | Example |
|---|---|
| List of unique statuses in the query query |
accounts?$apply=groupby((statuscode)) |
| Aggregate sum of the estimated value | opportunities?$apply=aggregate(estimatedvalue with sum as total) |
Retrieve a count of rows
| OPERATION | METHOD | URI |
|---|---|---|
| Use the $count system query option | GET | [Portal URI/_api/accounts/$count
Example: |
Column comparison
| OPERATION | METHOD | URI |
|---|---|---|
| Compare columns | GET | [Portal URI]/_api/contacts?$select=firstname&$filter=firstname eq lastname
Example: |
Retrieve related table records with a query
| OPERATION | METHOD | URI |
|---|---|---|
| Use the $expand system query option in the navigation properties | GET | [Portal URI/_api/accounts?$select=name &$expand=primarycontactid($select=contactid,fullname)Example: https://contoso.powerappsportals.com/_api/accounts?$select=name &$expand=primarycontactid($select=contactid,fullname) |
We are looking forward to your feedback as we march towards GA
Your feedback will help us continue to build on and improve the capabilities of this feature. We want to hear from you!
See documentation here for detailed overview.
Thank you,
Neeraj Nandwana