We are excited to announce the experimental feature release of the ability to work with dynamic schema in Power Apps formulas.  Power Apps formulas traditionally require that the set of fields returned by a data source call is fixed.  However, there are a number of services that may return a different set of fields based on the specific parameter values in the service call.  These service calls are said to have “dynamic schema” because the schema, or set of returned fields, changes dynamically based on how the service is called.  For instance, with Azure Dev Ops users may add custom fields to work items.  Because these custom fields are not available on all work items, some calls to Azure Dev Ops are considered dynamic. You will potentially get a different set of fields depending on which team’s work items you query.   Power Apps, until this point, could not work directly with these types of service calls.

Now, as part of the formula authoring experience, when a formula author encounters an warning indicating the return schema of a call is dynamic, an author may “capture schema”.  This will lock the schema for that particular call with the set of values it is called with.   And, you will be able to use the dynamic schema service call as part of your Power App.

Authoring a formula with dynamic schema

1. Turn on the “Dynamic schema” feature switch in Settings > Advanced Settings > Experimental feature.

Note: If you are on an older version of the platform, you may need to also enable the “Enhanced formula bar.”

2. A. Save your app B. Close your app. C. Refresh your browser and then D.  Re-load your app.    

3. Use the capture schema button to capture dynamic schema in your formula.  The capture schema button appears as part of the formula bar when you encounter an expression that returns dynamic schema.

 

Example

Azure DevOps has a mix of calls that are static and dynamic.  For example, the return value for “ListQueriesInFolder” is listed as “VstsList[QueryHierarchyItem]“.   However, the return value for the GetQueryResultsV2 has a note that the output of the operation is “dynamic“.  We’ll use both of these to illustrate how to work with dynamic schema.

A. We can use the first query (ListQueriesInFolder) to get a list of queries in our environment.  Insert a gallery and then author a formula for the Items property like the following:

B. Then use the results of this query in a second gallery that gets the detailed work items for the second query (GetQueryResultsV2) using a formula that handles dynamic schema.  Insert a second gallery and author a formula for the Items property like the following:

Note the warning here.  At this point, depending on the nature of the specific query you are using, you might not get any results.   However, in the case of Azure DevOps, there are a set standard (“system”) values that are always present – regardless of any custom columns that may, or may not be present.  So, if you look at the values you can assign for the gallery at this point, note that they are all prefixed by the word “System”.   See the image below.   What is missing are any dynamic or custom values.

C. Expand the formula bar and then use the Capture schema button at the bottom of the formula bar.   

Press the “Capture schema” button at the bottom of the formula bar and the warning goes away.  Inspecting the list of properties now available for assignment (via This. … in the formula bar) shows the following.

First note the new property “Microsoft.VSTS.Common.Priority.  This is a custom property that did not appear in our first list.

Second note that we did not get all of the same fields we got in the first non-dynamic query.  We’re not seeing all of the system fields we first saw.  This is because when we captured schema, those other columns were not returned.  Most likely because there was not data for those columns and the system simply didn’t return them.

D.  Assign the new property to a field.  Here we have assigned Priority to a gallery field.

A gallery is, by default, a read-only control.  And it is important to note that Power Apps will not automatically handle updates for this field.  You can assign the property in a gallery but you cannot simply assign the value in a data card because data cards automatically handle both reads and creates/updates.  Data cards need a tabular data source where their built-in behavior works with tabular data sources.   All dynamic schema data sources are non-tabular (i.e., action.)

E.  Use a custom field to show values in a Display Form.

1. Add a Display Form.
2. Assign the DataSource property to the full set of records in the target gallery (e.g., DataSource = Gallery2.AllItems).
3. Set the Item property to something like “Gallery2.Selected“.
4. Add a custom data card.
5. Add a label to your data card.
6. Set the Text property of the label to the field you want in your data card.

 

Feedback

The request to handle dynamic schema is a long standing one.  We’re happy to finally release it first in an experimental feature state and eventually a preview and GA release.  This is a good time to provide feedback.  Please feel free to reach out in our product forums.

Thanks,

The Power Apps development team.