User Guide Addovation Change

Last Updated: 2024-11-15

Introduction

This document provides guidance on how to use the Addovation Change add-in to create and use Excel templates.

Addovation Change task pane

The task pane is opened by clicking the Addovation Change button in the “Home” tab of the office ribbon.

Picture 1 - task pane button

This action will prompt a splash screen to appear on the right side of the Excel document.

Picture 2 - splash screen

Authentication

Properties

When creating a new document, some properties need to be filled out before Addovation Change can be used. If the properties page is not open, the link to the properties page is accessible from the user account button.

Picture 3 - user menu

Picture 4 – properties page

API URL

The URL to the backend used by Addovation Change, available in the cloud portal.

Subscription access key

The access key for the subscription is available in the cloud portal. The access key is required to connect to the backend. It also tells if a user is a template user or a template creator.

Start Change automatically when Excel loads

If this checkbox is checked, the Change task pane will automatically open the next time the document is opened.

Sign in

With validated properties in place, the user can sign in.

Picture 5 - sign in window

Properties save options

A template creator can choose to save only the API URL, both the API URL and the subscription access key, or none of them, depending on how the template will be published and to whom. If both properties are saved by the template creator, the only thing the user has to do to start working with the template, is to sign in.

Home Page

Once signed in, users gain access to the homepage, which displays content tailored to their user type. There are two user types: "template creator" and "template user."

Template creator

If the user is a template creator, the homepage provides functionality to search for a projection or a quick report to work with.

Picture 6 – creator home page

Template user

If the user is a template user, the home page provides functionality to interact with an existing template.

User home page - Compact view

If all projection types on a sheet have compact view enabled, the dashboard will be a simplified view. All available commands will run for every quick report and projection type on the sheet.

Picture 7 - user home page, compact view

User home page - Normal view

If any of the projection types on the sheet has compact view disabled, the dashboard will be a normal view. Entity sets and quick reports will be grouped with any actions/functions connected to the same Excel table. Actions and functions connected to custom tables created by the template creator are listed separately.

Picture 8 - normal view

Create template

Search & select tab view

In the homepage, there are two available tabs: "Search" and "Selected". "Search" tab allows users to search for projections. "Selected" tab displays a list of selected items categorized under "Entity Sets", "Actions", "Functions" and "Quick Reports".

A progress bar at the top of the pane indicates the status and progress of the steps.

Picture 9 - search & select tab view

The "Search" and "Selected" tabs can be navigated with an item selected, and the search results will remain unchanged. The search results will only be reset once an item is selected and "Next" is clicked, or the selected item is double-clicked.

Search for a projection or a quick report

A search is performed by typing a search criterion and pressing ENTER. The search will look for any name, module or description that matches the criterion.

Matching projections will be displayed grouped by Component. Quick reports will be displayed grouped by Category. If the search field is left blank and ENTER is pressed, a wildcard search will be performed, and all available items will be returned. Wildcard search can cause poor performance and is not recommended.

Picture 10 - search results

Projections

Picture 11 - search projection

A record can be selected by either double-clicking on it or by single-clicking on it and then clicking the "Next" option.

At this point, the "Search" step will be completed in the progress bar and a new list will be displayed with all the available projection types, i.e., entity sets, actions and functions. It’s possible to navigate back to the projection search result if needed by selecting the "Previous" option..

Picture 12 – unfiltered list of projection types

The list can be filtered by typing in the search field.

Picture 13 - filtered list of projection types

Selecting a projection type

The required projection type (entity, action, or function) can be selected by either double-clicking on it or by single-clicking on it and then clicking the "Next" option.

At this point, the "Select" step in the progress bar will be marked as complete, and users will be directed to the "Selected" tab.

The newly added entity set will appear at the top of the selected list and the configuration window will automatically open, pointing to the relevant record.

Picture 14 - selected tab

Entity Sets

An entity set is represented as a table on the worksheet. The workflow is to first select a cell in the sheet as the position where the table will be placed, and then double click an entity set in the list to insert a new table. If the sheet has multiple tables, they must be placed side-by-side, not on top of each other.

Picture 15 - selected entity set

The inserted table will have a “Status” column automatically added as the leftmost column, which will display the status from save operations. Key columns will be colored with a different color. The user can add more columns to the table if necessary.

Picture 16 - inserted entity set

As explained earlier, the entity set properties dialog is automatically opened when the table is inserted. It can also be opened using the Edit icon.

Picture 17 - entity set properties

Entity set properties

Get, Add, Update, and Delete operations are not available for all entity sets. What operations are supported for each entity set must be looked up in the projection API.

Important

The format of the sheet columns must be set for all table columns. Date type columns must be set to Category “Date”. For all other types the Category “Text” is recommended. The column types must be looked up in the projection API.

Picture 18 – manually set category to Date

Important

It’s possible to delete columns from the table, but key columns cannot be deleted if the table will be updated in any way. The “Add” operation may also require mandatory columns in addition to the key columns. What columns are mandatory must be looked up in the projection API.

Name

How the item will appear in the list.

Filter

Filter for the OData query used when fetching data. The filter can be hard coded or reference a cell in the workbook.

Hard coded: WoNo eq '1001'

Hard coded with wildcard and OData function: startswith(WoNo,'10%1')

Reference: WoNo eq '{Sheet1!A1}'

Reference and OData function: startswith(WoNo,'{Sheet1!A1}')

You can read more about OData in Change OData filtering.

Compact view

If all actions and entity sets on a sheet use this setting, the task pane will show a simplified view to the user.

Get

The user can populate the table by performing a Get operation.

Add

The user can add new items by adding rows to the table.

Update

The user can update existing items by modifying table rows.

Delete

The user can delete items by deleting table rows.

Entity set commands

There are a couple of commands available when working with an entity set table.

Picture 19 - entity set commands

Clear table Clears data from a table

Edit Described above in section “Entity set properties”

Get data Populates the table with fetched data

Push data (save) Changes to the table is pushed to the server (add/update/delete)

Delete item Deletes the selected entity set and the table from the sheet.

Actions

Actions are connected to a table on a sheet. The table can be any table, including an entity set table. The workflow when connecting an action to a table is to select the table which will have the values for the action parameters, and double click the action in the list.

Picture 20 - selected action

Picture 21 – table with action parameters

Either double-clicking the action or single-clicking on it and then clicking the "Next" option will direct the user to the "Selected" tab and open the action settings.

Picture 22 - selected columns for action parameters

Action settings

The action settings dialog is where the table columns and action parameters are connected. There’s a dropdown menu for each action parameter, where the user can pick which column from the selected table should be connected to the parameter.

If an action returns one or more values (it doesn't have to return anything), each response value must have a column where it can be displayed. The columns are selected from a dropdown where all the columns of the table are in the list, an additional option to skip the response is available for each value. If more columns are needed they can be added to the table and will appear in the dropdown list.

There’s also a dropdown at the top for selecting which column should be the “Response Status Column”. Entity set tables always have a Status column available, , but if the entity set table has Add, Update or Delete operations allowed, a new column has to be added and set as the status column for the action, they can not share the status column. Neither can two actions share the same status column.

Action properties

Like entity sets, actions also have a few properties

Picture 23 - action properties

Name

How the item will appear in the list.

Compact view

If all actions and entity sets on a sheet use this setting, the task pane will show a simplified view to the user.

Action commands

The action commands

Picture 24 - action commands

Clear table Clears data from a table

Edit Described above in “Action properties”

Invoke action Invoke the action using parameters from the table

Delete

Deletes the selected action and, optionally, the table from the sheet.

The user will be asked if the table connected to the action also should be deleted, even if the table is an entity set.

Functions

Functions, similar to actions, are connected to a table on a sheet. The table can be any table, including an entity set table. The workflow when connecting a function to a table is to select the table which will have the values for the function parameters, and double click the function in the list.

Picture 25 - selected function

Picture 26 – table with function parameters

Double clicking the function item will direct user to the "Selected" tab, and open the function settings.

Picture 27 - select columns for function parameters

Function settings

The function settings dialog is where the table columns and function parameters are connected. There’s a dropdown menu for each function parameter, where the user can pick which column from the selected table should be connected to the parameter.

A function returns one or more values and each response value must have a column where it can be displayed. The columns are selected from a dropdown where all the columns of the table are in the list, an additional option to skip the response is available for each value. If more columns are needed they can be added to the table and will appear in the dropdown list.

There’s also a dropdown at the top for selecting which column should be the “Response Status Column”. Entity set tables always have a status column available. If the table doesn’t belong to an entity set or the status column is already in use by another function, the user should add a status column to it before configuring the function.

Function properties

Like entity sets, functions also have a few properties

Picture 28 - function properties

Name

How the item will appear in the list.

Compact view

If all actions and entity sets on a sheet use this setting, the task pane will show a simplified view to the user.

Function commands

The function commands

Picture 29 - function commands

Clear table Clears data from a table

Edit Described above in “Function properties”

Invoke function Invoke the function using parameters from the table

Delete Deletes the selected function and, optionally, the table from the sheet.

The user will be asked if the table connected to the function also should be deleted, even if the table is an entity set or connect to other functions and actions.

Quick reports

When a quick report is found, the workflow is to first select a cell in the sheet as the position where the table will be placed, and then double click the quick report to insert a new table. If the sheet has multiple tables, they must be placed side-by-side, not on top of each other.

Picture 30 - selected quick report

The user will be directed to the "Selected" tab and quick report settings dialog will open automatically.

Picture 31 - quick report settings

Quick report settings

The settings dialog for the quick report is where any input parameter is configured. For each input parameter, an address (cell) from which the parameter value is to be fetched is entered. It's also possible to copy the address for the selected cell by clicking the 'copy' button in the text field.

Picture 32 - quick report table

Quick report properties

Quick reports cannot be updated, but have a few properties.

Picture 33 - quick report properties

Name

How the item will appear in the list.

Compact view

If all items from projections and quick reports connected to a sheet use this setting, the task pane will show a simplified view to the user.

Autorun

Will run the quick report automatically, i.e. clear the table and fetch the data again, when the user signs in to the application.

Notifications

Synchronization status

After a push operation, the response will be displayed in the Status column.

Synchronization success

Picture 34 - ok response

Synchronization error

Picture 35 - error response

General errors

General errors will be displayed as notifications at the top of the task pane.

Picture 36 - error notification

Previously dismissed error messages are available by clicking on the red bell icon.

Picture 37 - dismissed error messages

Known Issues

Issue - Add-in closes when populate is run in parallel instances of the workbook

Office 365 provides out of the box collaboration support. Unfortunately, the Add-in closes during such collaboration instances when data is fetched using the Add-in. This is only visible during a "Get" operation on the workbook for all other concurrent users of the workbook when they also have the Add-in opened.

Issue - CRUD Operations on a custom query

Custom queries appear the same as Entities in the current UI. We hope to show them as different in a future iteration of Change. Custom queries will also allow CRUD operations to be selected but result in errors when executed. Template creator needs to be able to distinguish between the custom queries they write, the custom queries provided by IFS and entities provided by IFS.