Blog

Code - Where do we define datasource, table, and field definitions?

In a code project, where is the code that defines the datasources, table, and field definitions? This post describes the relevant places and as an experiment, we'll see whether or not we can hack the data type definition of a SharePoint field. The purpose of this is to try to a bug where Power Apps doesn't recognise numeric calculated SharePoint columns as numbers.

This post explores the areas in a code project that relate to data connectivity. As a starting point, we'll take the example of an app that connects to a SharePoint list of property records. This is the same data structure that we've seen in previous posts.

We'll discover the files that corresponds to table definitions, and we'll see what happens when we manually modify the field definitions for a SharePoint list.

Overview of the project structure

When we open the code project, we find three folders that relate to data connectivity. These include:

  • Connections - This folder contains a file called Connections.json. This file contains a JSON definition of the connections in an app (for example, SharePoint, SQL Server, etc)
  • DataSources - This folder contains a JSON file for each data source in an app. Let's suppose our app connects to a SharePoint list called property. In this folder, there will be a file called Property.json that describes the schema of this list.
  • Pkgs - Beneath the Pkgs folder is a child folder called TableDefinitions. This folder contains a JSON table definition file for each datasource in an app. Taking our example, there will be another file called Property.json with more specific details about the list schema.
The screenshot beneath shows the content of the DataSources > Property.json file. The main part of ths file contains a "ConnectedDataSourceInfoNameMapping" section. This area maps the friendly description of columns names (eg names that may contain spaces), with the logical column names.

Where to find more precise table definitions

To supplement each JSON file in the data sources folder, there is a corresponding JSON file in the pkgs > TableDefinitions folder. This file more precisely describes the table definition. The screenshot beneath illustrates this file. The core part of this file contains a "DataEntityMetadataJson" section that contains a string escaped definition of the table.


To clarify the exact content of the "DataEntityMetadataJson" section, the screenshot beneath shows an unescaped version. The notable details here include:

  • The initial (the x-ms-capabilities node) identifies the field names that are sortable, and filterable.
  • The filterFunctionSupport node desrbies the filter functions that the data source supports. In this SharePoint example, we see the supported functions that relate to SharePoint, which includes the "and", "or", "equals", and "startswith" operators.
  • The schema section contains an array of the column names. Each element describes the column name, column data type, whether the column is writable, the acceptable min/max values, and the delegable operators that the column supports.


Demonstration - How to modify the data type of a column

To demonstrate how to modify the the data type of a column, we'll attempt to modify the AquisitionPriceUSD column in our SharePoint list. This is the caculated column that we covered in my previous post here:

This post identified an issue in Power Apps, where it incorrectly identifies SharePoint calculated numeric columns as strings. This prevents us from easily applying numeric formatting, or by using screen controls that are designed for numbers.

When we examine the schema section, we see the cause of this problem - the data type of the AquisitionPriceUSD is defined as a string.


We'll now try to fix this problem by changing the data type of this column like so:

{
  \"title\":\"AquisitionPriceUSD\",
  \"type\":\"number\",
  \"format\":\"double\"
  \"x-ms-permission\":\"read-only\",
  \"x-ms-sort\":\"none\"
}
We inject this escapted version into the schema section, and repackage our project with the PSAopa command.

Opening and testing our app in Power Apps Studio

When we now open our app in Power Apps Studio, we see that Power Apps correctly recognises the calculated AquisitionPriceUSD column as a number. Our modification has worked!



A caveat to this method is that if we were to refresh the data source from the designer, it would revert the data type of the AquisitionPriceUSD column back to a string.

Conclusion

This post highlighted the areas in a code project that relate to data connectivity. The Pkgs > TableDefinition folder contains a file for each data source. This file contains an escaped version of the column names and data types. In this post, we walked through how to modify this file, in order to change the way that the designer recognises the data type for a calculated SharePoint column.
Previous
Next
Related posts