Spreadbases

Configuring spreadbases

You first have to create a spreadbase, by going to the spreadbases submenu of the admin button.

There, you can click the button to create a new spreadbase and configure the following options.

• general
  • name: here you can provide a good description for your spreadbase, e.g. “entities list” or “managers”
  • access bundle: allows you to configure which users can access and/or modify the spreadbase, using the familiar system of access bundles
    
• structure allows you to create the various datafields that will make up the data structure of the spreadbase
  • Click on to add a new datafield — the options correspond to the datafields you can add to a concept’s datafields, such as true/false, numbers, currencies, text fields, dates and durations .
  • For each type of datafield, you can also use a list of that datafield. For example, when storing a list of company representatives, you can also store the company numbers of all the companies each representative is entitled to represent.
  • For each datafield, you can add a (translatable) label and a code.
    • The label is used for on-screen presentation purposes, visible to end-users.
    • The code can be used to retrieve the contents of a datafield within in a clause, and to link datafields together, as described below. This code will never be shown to end-users. You are therefore advised to not insert any spaces or special characters in this code.
  • Be aware that if you delete a datafield, all the data associated with it, will be removed as well.
    
• data allows you to fill in the contents of a spreadbase

Importing data

You can import data from an existing Excel-spreadsheet, by using the button. ClauseBase will interpret the first row of the first tabsheet of the Excel-file as the headers of the database, and all subsequent rows as data values. The second row of the tabsheet (i.e., the first row with data) will be used to assess which data-type to use.

Setting up a service instance

Spreadbases can be configured using the integrations submenu in the upper right corner of the software. (As is the case with many other parts of the software, this menu may not be visible if you have insufficient rights).

Creating a service instance

Before you can effectively use a spreadbase within a Q&A, you first need to create a service instance in the integrations administration section. This can be done by either clicking on the button and choosing spreadbase, or by choosing “clone active instance” in the popup-menu when another spreadbase service instance was selected.

Once a spreadbase is added as a service instance, you can configure the following elements:

General

• template allows you to build a service-instance on top of another service instance, so that common elements can be singled out and should not be entered over and over again
• description is an internal description of the spreadbase
• prefix allows you to enter a short code (typically a few letters only — e.g. “ent” or “pt”) that will be used to refer to this spreadbase within Q&A questions
• access bundle allows you to define which users can include this service instance in their Q&As, and/or can modify the service instance. Note that the underlying spreadbase has its own access rights that define who can access and modify the data within the spreadbase.

Input fields

The most important setting is located at the bottom: the spreadbase ID. Here you have to choose the spreadbase you want to connect to this service instance.

In this section you can also configure the input parameters that will be used to search in and filter a spreadbase.

• If end-users need to be able to dynamically search on a certain parameter, then this input field should be set to dynamic. The input-field will then receive its value from a certain question or datafield in the Q&A. For example, in a spreadbase consisting of legal entities, users may need to be able to search on the entity’s address by typing in a few letters — those letters will then be dynamically delivered to the service instance.
• If, on the other hand, this service instance should always limit its data to certain data (e.g., only the Belgian entities), then you should set this input field to fixed (e.g., with the value for country set to “France”).
• You can also combine fixed and dynamic fields within the same service instance. For example, when the location datafield would be set fixed to “France” and the name datafield would be set to dynamic, Q&A users would get a popup list that allows them to search within French entities using a few letters of the name.

Dropdown fields

This section allows you to configure which particular datafields should be shown towards the end-user for the selected service instance. For example, in a spreadbase with legal entities, a dozen datafields (name, address, zip, city, country, VAT number, manager name, …) may get stored, but only a few datafields are perhaps useful to be shown to the end-user in a particular Q&A.

Using the button, you can add datafields to show in the dropdown field. Once added, you can then rearrange the position of a datafield using the buttons. Optionally, you can also define which field to sort on, using the sort by dropdown list.

Input & output tags

Each of the input fields and output fields can be optionally mapped to a different name using the input tags and output tags menu. This is a standard feature of every data source that is used in a service instance, but is not very relevant for spreadbases, as these are under full control of the user. (This feature is much more useful for data obtained from an external server, where it could for example be useful to map a cryptic datafield “lc_e_inf” to a more readable “entity-location”.)

Integrating a spreadbase into a Q&A

Once a spreadbase is created, and at least one service instance is associated with it, you can start using the spreadbase in a Q&A.

At this moment, the spreadbase can only serve as a popup-list within a Q&A. This allows end-users to search for, and then select, a certain record of the spreadbase. The various datafields of the selected datarecord can then be copied towards one or more other questions.

Setting up a spreadbase as a source of predefined values

Instead of manually creating a set of predefined values, you can use a spreadbase as a source of predefined values.

• This is particularly useful when more than a handful of options exist, as it would be tedious to enter all those options manually as a predefined value in the spreadbase.
• Also from a management perspective, it is probably not a good idea to manually add many different predefines to questions, and then copying/pasting those predefines to different Q&As. If one of those values needs to be changed (e.g. the address of a legal entity is changed), then that same change needs to be replaced in many different places. Spreadbases, on the other hand, allow you to centrally manage all the values in a central location.

To effectively use a spreadbase as a source of predefined values, you have to click on the + Integration button.

Next, you have to select the service-instance you want to obtain data from, from the dropdown list at the left side:

You can optionally select a dynamic input field from the dropdown list on the right side. This input field will then be used to filter the list presented to the user, based on what the user is typing. For example, if in the list of legal entities the name of the legal entity was set as a dynamic input field, then you can choose the name as an input filter, so that if the end-user types “ab” in the Q&A popup window, only legal entities with “ab” somewhere in the name will be shown to select from.

Filtering on other values

Instead of — or in addition to — filtering on a fixed datafield (set in the service instance), or some characters typed in by the end-user into a filter box, you may also want to filter the spreadbase on some other existing value.

For example, if you allow the user to first choose a certain legal entity, and next want to allow the end-user to only select the signatory from the list of managers appointed within the previously selected legal entity, then that legal entity’s name will serve as a filter.

To setup such a filter, you have to insert a send tag into the options (subsection integration & customization) of the question whose answer will be used as a filter. This send tag should be constructed as service-instance-prefix^dynamic-datafield of the service instance that should be filtered. For example, assume the following setup:

• A first spreadbase contains the legal entities, and has datafields name, address, city, country and VAT-nr.
• A second spreadbase contains the managers of the various entities, and has datafields first name, last name and legal entity VAT nr.
• Note that the two spreadbases use the VAT nr. as linked datafield (in both cases having the code “vat-nr”), which allows you to link a certain manager to a certain legal entity (because each legal entity can have one or more managers). In this particular setup, the VAT nr. is probably the most interesting field, because it can be assumed to be unique and never change. The legal entity’s name would also be a reasonable candidate, but has the downside that legal entities tend to change their name, which would then require to update not only the legal entity’s name in the legal entities spreadbase, but also all the instances of that legal entity’s name in the managers spreadbase.
• The service-instance associated with the first spreadbase has prefix ents, while the service-instance associated with the second spreadbase has prefix managers. This second service-instance has its VAT-nr. set as a dynamic input field.
• To ensure that the VAT nr., stored in a certain answer, is used as a filter for the list of managers that can be chosen by the end-user, you should insert a send tag equal to managers^vat-nr into that answer. This causes the value currently present in the VAT-nr. answer to be used as an input value for the dropdown list of the list of managers, so that only those mangers who have a VAT-nr in their record that is equal to the specified VAT-nr. — in other words, only the managers of the previously chosen legal entity will then show up in the list of managers the end-user can choose from.
  
  The button shown next to the send tags in the integration & customization options of a question helps you to insert this tag. It will contain a list of all the dynamic datafields of all the available service-instances to which values can be “sent”.

Advanced filtering and transformations through a custom function

When the filtering options described above are not enough, you can use a custom function, written in the Clojure language, by clicking on the “…” icon.

In the entry box that appears, you can then write code to transform each entry retrieved from the spreadbase:

• Each entry is represented by var $1.
• When nil would be returned, the entry will be skipped from the list shown to the end user.
• Each entry consists of a map of keys and values, where the keys are keyword-versions of the code of the column. For example, the following spreadbase would contain entries such as {:name "John Smith", :birthday {:year 1980, :month 3, :day 31}}.
  • If the spreadbase would contain list-values, then those will be represented as a vector — e.g. {:name "John Smith", :companies ["Alpha Inc.", "Beta GmbH"]}. By way of example: if you would like to filter the list so that only representatives who can sign for company “Beta GmbH”, you could enter (when (coll/present? "Beta GmbH" (:companies $1)) $1). And if you would not like to hardcode the company’s name, you could even retrieve it from some other answer: (when (coll/present? (answer "company") (:companies $1)) $1)

• You can transform each entry’s values if necessary. For example, in the example above, you could set the name in uppercase by entering (update $1 :name str/upper-case).

Distributing the chosen value

Once the end-user chooses a specific item from the spreadbase (e.g., a certain legal entity), you will want to distribute the values of this item across different answers. For example, you may want to insert the name into the answer to the question that asks for the legal entity’s name, while you want to insert the address in the answer to the question that asks for the address, and the manager’s name into the signatory box.

In order to do so, you have to insert receive tags into the integration & customization subsection of the options of those questions that need to “receive” values from the chosen item. For example, in the example set out above, you could insert an ents^name receive tag in the question that asks for the entity’s name, and insert an ents^country in the question that asks for the entity’s country.

The button shown next to the receive tags in the integration & customization options of a question helps you to insert these tags. It shows a list of all the available datafields, from all the available service instances.

Notes and caveats relating to the distribution

• If you assign a certain service instance as the source of predefined values for a particular question, this particular question will not automatically “receive” the value chosen by the end-user. Instead, you must manually include a receive tag in that question, similar to how other questions need such receive tag.
  
• The distribution (i.e., insertion of values) only takes place once, at the moment the end-user selects the value. Once this action has been performed, the values that were inserted no longer “know” where they came from, are no longer “linked” to the spreadbase they came from, and can be freely modified. So please take into account that afterwards, the end-user may change those values.
  
  For example, if answer “country” has received the value “Germany” when a certain German entity was chosen, the end-user may afterwards change this value manually into France.
  
• It may happen that you want to store a certain value merely to act as a filter for some dropdown list. If you do not want this value to show up on the end-user’s screen, you can set its visibility to “do not show this question” in the integration & customization” subsection of the question’s options.
  
  Please do not confuse this situation with a question that happens to be disabled due to some condition (or it’s card’s condition) not being met. A question whose visibility is set to “do not show this question”, will behave as a normal, enabled question — with the single difference that it just happens to be invisible, because the designer does not want it to show up on the end-user’s screen.
  
• If you set a receive tag for a table-based question, then new rows will be inserted in that table upon distribution of the value (except when the table is set to have row-labels or a maximum number of rows, in which case the value will get copied into the column(s) of the first row).
  
• If the standard distribution mechanism is not sufficient for your needs you can use a custom function (:on-apply-service-instance).

Concatenating values during distribution

It may happen that certain questions need to “receive” values from different spreadbase datafields at once.

For example, assume that the spreadbase legal entities has separate datafields for street, house number, zip, city and country. It may be the case that in some Q&As in which that spreadbase could be used, the legal entity’s address is stored as a single answer, in which the entire address must be inserted (e.g., “Main Street 123, 1000 Brussels, Belgium”).

To cope with this situation, you can concatenate different values into a single tag, by using the pipe symbol. For example, the tag ent^street| |ent^house-number|, |ent^zip ent^city|, |ent^country will “receive” five different values from the spreadbase, and concatenate them together with spaces and/or commas.