While making applications, you will undoubtedly use constants or pre-defined default settings. Items that have a special meaning and a specific value that does not change. Well… at least not for a while, or not very often. So you might be inclined to just “hardcode” these values whenever applicable. But when they do need to be changed, you’ll have a hard time finding all the situations where they are hardcoded in the expressions and labels in your application.

Do you recognize any of the following?

• The email address you want to use as the default sender for all emails in the application.

• The VAT-percentage in your financial calculations.

• The API-key or token for external applications you use, like Google APIs.

• Any pre-defined number or date to be used in some context.

In WEM, you can use a Calculated Field to hold these “constant” values and use this field in your expressions, templates, flows and calculations. This way, if the value needs to be changed, there is only one place in WEM where this needs to be done: the expression of the Calculated Field in the Data Model.

A Calculated Field can be any of the available WEM-Types (Text, Number, Date, Boolean, Single/Multi-select and even a File or Referenced list item).

Calculated Fields can be used in the expressions of other calculated fields. This makes way for some powerful stuff, so be smart and don’t go circular…

Advantage 1: you can find all the usages of a Calculated Field throughout your project with one click on [Find Usages] in the field's context menu.

Advantage 2: if you know a change is coming in the future, you can put this in the calculation expression. So you can prepare the change in advance and not worry when the time comes to make the changes.

So, whenever you feel the urge to put a seemingly constant value directly into an expression, stop and think whether a Calculated Field might be the better option.

Don’t forget to organize your Calculated Fields cleverly: put them in a logical place within your Data Model and give them names that instantly clarifies their intended purpose.

WEM supports OData V3. OData is an open protocol that allows you to consume or expose data through simple web requests. It builds upon the ideas of REST. Introduced by Microsoft in 2007, it is adopted by OASIS as a standard and supported by a large number of applications.

OData is comparable with ODBC and JDBC, as it allows you to communicate with an (often relational) database in a similar manner. It allows you to query, insert, edit or delete data, all through simple HTTP requests. An OData service shares the schema of the exposed data, which makes it possible for the client application to discover all the table and column definitions.

With WEM, you can both consume external OData sources and expose the database of your application as an OData service. In this blog post I will show you how to do the latter.

Example application

For this article we created a simple WEM application that stores product information about laptops, tablets and smartphones. This application is accessible via https://odata-demo.live.wem.io.

Enabling OData

Enabling OData for your WEM application is as simple as creating an OData Account for accessing your Project's Data. You can do this on the Project Settings page, in the Security Panel. Here you can create a new OData Login. Make sure that the password is secure, because these login credentials allows anyone with the information direct access to the data of your WEM application.

On the next step you can determine the level of access:

• Full access: these credentials will allow Read, Update, Insert and Delete on all datalists in your project;

• Restricted access: Define the proper access to specific lists and for specific actions

To be able to test the OData connection to your application via the Staging or Live URLs, you have to Publish your application to make the OData Login available in the Runtime.

Test the OData service in the browser

After you have created the OData Login and published your application, you can test the OData service in the browser. Type in the URL of your application followed by /odata/. The browser will prompt you for the user credentials.

You should see an XML document that describes which collections (persistent lists) are available in your WEM application. You can also view the detailed schema information by using the Metadata URL (just append $metadata to the Base URL).

If you want to learn more about using OData directly from your browser, you can read the official documentation.

Connect to your application using Excel

Excel can connect to any OData source. This allows you to directly view the data, create pivot tables, generate charts, etc. Excel can not modify the exposed data, Microsoft never implemented that feature.

To connect to an OData source from Excel, go to the DATA tab, and choose From Other Sources and then From OData Data Feed. This will start the data connection wizard. The first step asks for the URL and user credentials of the OData connection.

The second step allows you to select which tables you want to connect to. For this example, I just select the Products table.

Excel stores the OData connection settings in an external .odc file. Afterwards you can reuse this connection via the toolbar icon Existing Connections. In the next step you can change the name and location of this file. You can also choose to store the login credentials in the file. For this example, I leave everything unchanged and click on Finish.

The final step allows you to configure how you want to show the data in Excel. For this example, I just choose Table. This will show all the rows that are stored in database of your WEM application.

When this is done, you should see your data in Excel. You can now sort and filter the data using the standard Excel features. Note that Excel won't automatically synchronize the data. To see all the updates since the last time the data was imported, you can click on the button Refresh All (found in the DATA tab).

The WEM platform offers several distinct storage mechanisms for your application data. In this article we will discuss each of these options and what to consider if you intend to use them.

Concepts in Ontology

Organizing the data structure: The ontology (wiki) allows you to organize data in a tree structure. Each data element is represented as a node in the tree. These nodes are referred to as concepts. Attributes of the data item are represented as properties on the node. You can add custom properties on a specific level and determine the level to which the properties apply. Beyond organizing the data in a tree structure of concepts, it is also possible to create custom relationships between the concepts therewith creating a relational graph of concepts. Using a Concept Query, which is basically a graph query, you can retrieve specific concepts from the ontology. The Ontology provides a powerful feature in WEM, usable as basic Single/Multi select options or tabs, or moving towards more complex cross-related elements to steer application flows or provide multi-leveled cases.

Data availability: The information in the Ontology Concepts is available at the time of development, which allows you to include the actual data directly into your application logic.

Modifying data: The concepts, their properties and relations can only be modified during development time within the Modeler. During execution of the application in the Runtime Environments (Preview, Staging, Live) the information is read-only.

Data persistence: The data is persisted during the time of development in the Modeler, as part of the Project information.

Data accessibility: The data is accessible to all Modeler Sessions with access to the project, and read-only in User sessions in the Runtime.

Use case: This storage is perfect fit for data that describes the "conceptual domain" of the application. For example when creating an authentication/authorization system, the different kinds of user roles and privileges can be described using the ontology. This data is static in nature and will be included when modeling the access control logic of the application.

Persistent (Database) and Transient Lists

Organizating the data structure: In WEM, "Entities" (like Users, Books, Companies - multiple elements that have the same properties) are defined using Lists, in which an Entity instance (a specific User, a specific Book...) is a Row of the List. For example a list of people can be stored as a List named "Persons" and storing each person in the list as an instance by creating a row in the list. Entities can be nested to create parent/child relationships between the container entity and the nested entity. The amount of nesting is limited to 5 levels deep. Entities can be related one-to-many, by adding a Reference Field into a List (the many-part) that references another List (the source or the one-part in the relation). For example Books that contain a reference "Writer" to list Persons. Entities can also be related many-to-many using Lists that have Reference Fields to the other Entity-Lists that need to be related this way. For example a list BooksWriters that contains a Reference to Books and a Reference to Persons.

Data availability: During the time of modeling your application you can only define the schema/structure of the entity, creating the List and adding the Fields you will need for storing the actual data. Storing the actual data is only possible during the usage of the application in the Runtimes. The actual data is therefore not present in the Project during development in the Modeler.

Modifying data: The data can only be read and modified using the Application in the Runtimes.

Data persistence: Transient Lists hold information that is only available for the duration of the session and also only accessible to that session (single user, single time). Information stored in Persistent Lists will be stored in a specific Database, making the data available beyond the duration of a session (many users, many times). Note that when information is added to an Persistent List during a session, it will only be persisted when the changes are saved to the database using the Save All Changes node.

Data accessibility: Information kept in a Transient List is only accessible to the session of the user who added it (single user, single time/session). Information stored in a Persistent List and saved to the database, is accessible across all sessions of the application. Note that information added to a Persistent List but not yet Saved to the database, is only accessible to the user in the session where it was added or modified.

Using Ontology and Entities side by side

The total set of data for the application can be organized by using the Ontology Concepts for the static information of the data and Lists for the dynamic information (Runtime Data). For example when creating an authentication/authorization system, the Type of users, user Roles and Privileges can be defined in the Ontology as Concepts and the actual User instances can stored in a "User" List. In the User List you can add Single Select or Multi Select fields, based on the Concepts in the Ontology to store per User row which Type it is (Single Select), which Roles and Privileges the User has (Multi Selects). When this information is saved to the database, each User-Row will have a "reference" to the definition of the selected Concepts.

In 2019 we added the feature to expose the data of your WEM application with OData. This allows other applications to easily access and modify the data of your application. In the article Expose your data via OData you can read how this works in WEM, and how you can load your data into Excel 2013.

In Microsoft Excel 2016, things have changed a little bit. To connect to an OData source, you still go to the Data tab, but then you choose New Query > From Other Sources > From OData Feed.

You now get a popup where you can specify the URL to the OData feed. For this example, I again use the demo application http://odata-demo.live.wem.io/odata/. Make sure you add that last slash at the end of the url!

After you click Ok, it will prompt you for the user credentials. Select Basic and enter the username and password. The username and password for the demo application are demo and demo1234.

After you click on Connect, you will get a popup where you can choose which tables you want to import into Excel. In my case I choose "Select multiple items", and select all tables. Next, click on Load.

The panel "Workbook Queries" will appear on the right side of excel. This panel will list all the tables you chose to import. When you hover over these tables, a popover will appear that will preview the data and allow you to edit the query. This allows you to apply filters, and to combine data from multiple tables. In my case I simply want to import all the data from the Companies table, so I click on the three dots (•••) and choose the option Load To....

I can now choose to view this data as a Table and either load it into a new worksheet, or into an existing worksheet. In my case, I choose Existing worksheet. Finally, I click on the button Load, and the data will be imported into Excel.

Excel will store the connection to the OData feed in the excel document, so the next time you open this document, you can go back to the Data tab and click on the Refresh button to retrieve the updated data from the OData feed. If you want to see the workbook queries again, you can click on the button Show Queries.

Data is an important part of every application. So working with data is an important aspect of modeling your application with WEM.

Here you will find help, tips and tricks, and other information to help you with data related questions.

Read more on:

• The Power of the Calculated Field Are constant values always constant? Think about using calculated fields

• Consuming OData with Excel 2016

• Expose your data via OData

• Using OData to transfer data between runtimes

• WEM Storage Options

So, you’ve been building a nice application and testing it in the Staging Runtime. While doing so, you’ve entered quite some useful data. When it’s time to go Live, you publish and start the Live Runtime of your application. Alas… no data! You will need to re-enter everything… Or maybe not?!

With the support for OData, both consuming and exposing, WEM makes it quite easy to transfer data from one runtime to another for your project. This is particularly useful in two cases:

1. When you go live with your application and want to re-use data which is already available in Staging;

2. When your application has been running Live for some time, containing lots of useful data and you want to get it available in Staging for testing new or changed features or investigate bugs.

So, how do you get this working? Let’s work out number 1:

Getting data from Staging to Live

Create the OData Exposure:

1. Go to the Security Panel on the Project Settings page;

2. Create an OData Login and give it Full Access (or determine which access is really necessary);

3. Publish to Staging, this will enable OData access on the Staging runtime database;

Import the OData Service

1. Go to Webservices Tab in the Project tree, access the context menu on [Remote data sources] and click [Import OData Service];

2. Enter the OData Service information:

   • Enter a name for this service (like MyProjectStagingData);

   • Use the portal’s Staging URL, and append /odata/, e.g.: https://portalname.staging.wem.io/odata/;

   • Enter the credentials of the OData Login created in previous step;

   • Hit Import and see the lists and fields appear in the tree...

Then, build flowcharts

Using the remote data source (in this case, your Project Staging Runtime data), the database lists are available directly from your Project Tree (Remote Data Sources) and you can use them the same way as you use the database lists in your Data Model with listnodes on flowcharts, datagrids and repeaters on Templates.

You can drop a Datagrid on a template based on any of the lists in the Remote Data Source just to display the data. You can put a loopnode on such a list and copy the data to the corresponding list in your Data Model (add new row and handle each corresponding field with each iteration of this loop). Run it in preview mode to get the Staging data into Preview.

Be careful, use filters and checks where necessary!

Check if you are in the correct runtime, by using the IsLive() or IsStaging() functions (you don’t want to accidentally copy staging data into staging again…); Check if an item already exists, if you are going to use this flowchart repeatedly; Do not use the ID-fields, as they are automatically generated so they might (and probably will) be different between the Staging and Live runtimes.

Mind the Concepts! When copying staging concept fields to live concept fields, the underlying technical information is not exactly the same. Using an Assignment Node on a Live.DataModel.ConceptField and plainly assigning it with the ExternalDataSource.StagingData.DataModel.ConceptField will just not work as you might expect. But it’s easy to map it correctly with the available Ontology-functions.

For a Single Select field, use something like:

first(concept of GetChildren(‘root concept of single select’) 
where tostring(concept) = tostring(ExternalDataSource...SingleSelectField))

as the expression in the assignment node (replace the fields and concepts with the correct information).

For a Multi Select field, use something like:

GetDescendants(‘root concept of multi select’)
where tostring(concept) 
	in (tostring(concept) of ExternalDataSource...MultiSelectField)