Last Updated: 2021-10-11
This is the fourth and final part of a tutorial that guides you through the process of building a Koivu Cloud spreadsheet-database app. As it's always helpful to focus on a concrete example, we have been building an app for sales personnel who need a tool to create quotes. The other parts of this tutorial series are:
This final part concludes our tutorial, and at the end, you should have an overview of how to design and implement a new Solution using the Koivu Cloud platform. By now you should have noticed that the cloud tables function as the backbone of a Solution, and that you build functionality by adding scripts into these cloud tables. Also, you've probably noticed that each column of every table in your solution is listed in the Column Definitions table. Similarly, each cloud table is listed in the Table Definitions table, which itself is also a cloud table. These two tables are essential when you're configuring your app, and in this final part we'll continue to set up our app using these cloud tables.
In this final part we'll finish our quote builder with the Koivu Cloud Excel add-in. You'll learn how to:
Creating a Koivu Cloud solution is a relatively easy task, however, you should at least have the following:
Koivu Cloud enables you to create different views from the same cloud table. You can use a view to, for example, display only a subset of the cloud table's columns. We'll use this feature to create a printable page of the quote, where we use the same table, but use only specific columns from the table. In essence, a view is a convenient way to customize and filter how and what data will be displayed in the sheet. And as a matter of fact, every time you open a cloud table in Excel, you're displaying a view.
The Table Definitions cloud table lists all of the cloud tables of your Solution, and the easiest way to create a new view from an existing cloud table is to clone its row. In summary, find the appropriate row from the Table Definitions table, clone it and enter a new ID (Key value). As a result, the new entry will be listed as its own cloud table option in the add-in's Select Table drop-down list, and you can open it into a new spreadsheet. Lastly, you can choose which columns you want to display in the spreadsheet.
To make a new view:
Quote Printable
.We now have a new view into the same data that is used in the Quote cloud table. Next, we can remove data that we do not need, and add information that we want to have. When you open a cloud table into a blank sheet, all of its columns are retrieved. You can customize the view simply by removing the columns you do not need, click Clear to clear it from the data, and perform any additional customizations for the template. For example, you could create a printable quote page that looks like this:
The above example has the following changes in the Excel template:
Also, you can create similar search fields for the table as we built previously for the Quote cloud table. This time we will not go into any further details as we've already discussed how these settings are done. However, please go ahead and customize your own printable quote page. For example, in Excel you could select View > Gridlines to hide the gridlines, and make the layout differ from a traditional spreadsheet.
Finally, we'll create another view that is used to display an analytics page. Our aim is to build a page that can present historical data using Excel charts. However, we don't want information about individual items, and therefore we want to limit the view to only include data about the customer and the Quote ID, for example. We're going to build the view in the Table Definitions table, and the view will only return the rows that we previously relocated to the header of the Quote sheet. You can limit the query to return only the header rows by adding a "level" query parameter into the Read URL column. That is, we'll retrieve only rows that have their level set as 2.
To create a view with query parameters:
/valueflow/v1/properties/Quote?level=2
. The level here matches the number of key columns being used; only the rows that have two or less key values will be returned. If the level is not defined, all are returned.Next, we want to customize our new view in the Excel template. Also, we need some additional fields for our analytics view, and therefore we need to create new columns. In summary, we need to:
To add new columns:
Order date
and Status
. See the image below.
We defined the property level of the new columns to be 2, which means that they will be used in the header only. Therefore, the next step is to move the columns to their correct location.
To move the fields out of the table:
21/09/2021
and status, e.g. Awaiting delivery
, in the new fields for a quote. Alternatively, you can use the web app to set the values.We also want to use the Order Date and Status columns as search criteria in the analytics view, and to this end, we'll use the secondary indices.
To define a secondary index:
[p.properties.status, p.properties.orderDate]
in the Key Index 2 column. The secondary key indices are defined as arrays in the Key Index 2, Key Index 3 or Key Index 4 columns. You can enter properties, keys, or expressions as a value. We have to use Quote table instead of the QuoteAnalytics view, because the QuoteAnalytics, QuotePrintable view tables search from the original one:999
in the Level column. The level field is automatically generated, so the changes you make won't persist.We can remove all columns that we don't need from the Excel template. The workbook functions as the UI for the quote builder, and therefore, if you remove a column, its data is not retrieved from the cloud. In other words, you can filter the view by removing unnecessary columns from the sheet.
To filter the view:
Note that if you have opened the Quote Analytics into a new sheet before you created the Order Date and Status columns, you can attach these columns into the view. That is, if you only have these columns in your view:
To attach columns:
We'll end this tutorial by defining the search fields for the Quote analytics page.
To configure the search fields for the Quote Analytics view:
You should now have two drop-down search fields in the add-in for the Quote Analytics view.
Now you can, for example, create a chart in Excel that displays your quote data, and you can use the search fields to filter by status and date:
Congratulations, you have now completed the tutorial and you can now build your own Koivu Cloud Solutions!
Explore these tutorials to learn more about what you can do with Koivu Cloud:
Make sure to visit our community site and learn more about Koivu Cloud.