Common Data Service Canvas App – Store and Update data in Collections using functions– Part 2

ClearCollect                     ||                         AddColumns                    ||                        GroupBy

In Part 1 of series we created Canvas app using CDS for Apps and customized screens. In this blog we will cover how to make use of collections and update data in collections. Let’s first understand what collections are.

Collections: Special kind of storage to store group of similar items. For example: List of Companies and Products. Collections data is local to an app and cannot be shared across applications or devices. Collections are useful to store and modify data in offline mode by creating a temporary copy of data. You can store single value as well as table data in collections. Below are functions used when working with Collections:

• Collect: adds record to existing collection or create new collection if not exists already. Collect function returns updated data source as table
• Clear: deletes all records from a collection. Clear function does not delete collection itself and returns nothing.
• ClearCollect: combination of Clear and Collect functions which deletes all records first and then adds new records to same collection. ClearCollect also returns updated data source as table

Refer blog for more details on Collections.

In this blog we will make use of ClearCollect function to store Company Products data. We will then Group By Products by Company and finally we will add new column to store Total number of Products by Company.

Collections helps us to visualize data as well with each transformation

Add Company Products Data Source:

• Select View tab and click on Data Sources. Click on + Add Data Source. Select Common Data Service connection if already exists or click on + New Connection

• Choose Company Products table and click on Connect

Store Company Products Data in Collection:

• Add a button on form. Select Insert tab, Click on Button. Button will be added on form. Change Text property of button to “Collect”

• Select OnSelect property of button and use below formula to load products data in Collection
  • ProductsByCompany – collection name
  • Company Products – data source

ClearCollect(ProductsByCompany,’Company Products’)

• Run app (Press F5) and press Collect button which will create collection and load products data. Press Esc to close app.
• Select View tab and Click on Collections. You will see ProductsByCompany collection created. Click on it to view collection data

• Click on back arrow to return to app designer

Group By Products data by Company:

Company column in Company Products is a lookup column and currently Group By function does not support lookup columns. Therefore to group products data by company, we need to add company name in our collection created above.

• Change value of OnSelect property of Collect button as below to add company name column in Collection. Here we are using AddColumns function to add new column
  • Company Products – data source
  • CompanyName – name of new column
  • cr2a5.Company1.cr2a5_companyname – get company name from Company lookup

ClearCollect(ProductsByCompany,AddColumns(‘Company Products’,”CompanyName”,cr2a5_Company1.cr2a5_companyname))

• Run app (Press F5) and press Collect button which will reload load products data with new column this time. Press Esc to close app.
• Select View tab and Click on Collections. Click on ProductsByCompany collection.

• Update value of OnSelect property now to add GroupBy function on top of AddColumns added by to group by products data based on CompanyName new column
  • CompanyName – new column
  • Products – group name, holds grouped data by column

ClearCollect(ProductsByCompany,GroupBy(AddColumns(‘Company Products’,”CompanyName”,cr2a5_Company1.cr2a5_companyname),”CompanyName”, “Products”))

• Run app and click on Collect button. Check ProductsByCompany collection and you will see only 2 columns now: CompanyName and Products (holds data for a company in child table). Click on child table inside Products column to see company specific products.

Add Column for Products count per Company:

Now data is grouped based on company. We will add new column in collection to hold product count per company

• Update value of OnSelect property of Collect button to add AddColumns function on top of GroupBy as below
  • ProductCount – name of new column
  • CountRows – function to count number of rows in Products column

ClearCollect(ProductsByCompany,AddColumns(GroupBy(AddColumns(‘Company Products’,”CompanyName”,cr2a5_Company1.cr2a5_companyname),”CompanyName”, “Products”), “ProductCount”, CountRows(Products)))

• Run app and click on Collect button. Check ProductsByCompany collection

Part 1

Part 3