·
3 min read

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

Try-out step by step example

Query records

 

OPERATION METHOD URI
Retrieve table records GET [Portal URI]/_api/accounts

Example:
https://contoso.powerappsportals.com/_api/accounts
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:
https://contoso.powerappsportals.com/_api/accounts?$select=name,revenue&$top=3
Retrieve account by using account ID GET [Portal URI]/_api/accounts(e0e11ba8-92f6-eb11-94ef-000d3a5aa607)?$select=name

Example:
https://contoso.powerappsportals.com/_api/accounts(e0e11ba8-
92f6-eb11-94ef-000d3a5aa607)?$select=name

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:
https://contoso.powerappsportals.com/_api/accounts?$select=name,revenue&$filter=revenue gt 90000&$top=3

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:
https://contoso.powerappsportals.com/_api/accounts?$select=name,revenue&$top=3

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:
https://contoso.powerappsportals.com/_api/accounts/$count

Column comparison

 

OPERATION METHOD URI
Compare columns GET [Portal URI]/_api/contacts?$select=firstname&$filter=firstname eq lastname

Example:
https://contoso.powerappsportals.com/_api/contacts?$select=firstname&$filter=firstname eq lastname

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

Neeraj Nandwana
Neeraj Nandwana