Spreadbases
Last updated
Last updated
The word spreadbase is a combination of a spreadsheet and a small database, intended to host limited amounts of information in Clause9, such as:
a list of legal entities and their administrative details (name, address, VAT number, etc.)
a list of managers and their contact details
a list of product details (description, internal code, price, minimum volume, etc.)
a list of appeal courts in your country
Spreadbases are intended to solve a practical problem facing many users of Clause9: some data sets are just a little too large to store as predefined values in a Concept or Q&A Question, while other data sets are just a little too small to be worth the hassle of setting up a full-fledged database outside of Clause9. In other words, spreadbases target medium-size databases with limited complexity — typcially between 20 and 200 items.
In the context of Clause9 integrations, a spreadbase qualifies as a service for which one or more service instances can be configured by the user, as discussed in the page on services & service instances .
Instead of using spreadbases, you may also want to have a look at:
the possibility to directly integrate with native Excel-spreadsheets for lookups. Unlike spreadbases (which are targeting questions in a Q&A), the lookups can be performed from within a clause
the global placeholders system, whose functionality is somewhat different than spreadbases, but also partially overlaps for some use cases
You first have to create a spreadbase, by going to the spreadbases submenu of the admin button.
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
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
If you import a spreadsheet into a spreadbase that already contains a structure and data, then this structure and data will get deleted.
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).
A single spreadbase can be used in an unlimited number of service instance. This allows you to, for example, show slightly different datafields — e.g., show the legal entities’ names and addresses in a certain popup list, while showing the entities’ VAT number and export registration number in some other popup list; or only showing the French legal entities in one popup list, while showing all the legal entities in some other popup list.
Once a spreadbase is added as a service instance, you can configure the following elements:
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.
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 “Belgium”).
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.
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.
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”.)
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.
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.
A partial solution to the management issue described above, would be to use a certain card as a proxy. Such proxy alleviates the concern of replicating the same predefines across many different Q&As, but still suffer from the problem that it becomes tedious to manually enter dozens of different predefines.
On the other hand, manually defined predefine-values have the advantage that they can be subject to conditions (e.g., specifying that predefined entity A should only be shown when product line X is chosen). Adding such conditions at the level of an individual item is not possible for values in a spreadbase.
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.
Note that fixed data fields are not shown here, because they are automatically filtered on.
For example, if in the list of legal entities the country would be set fixed to “Germany”, then only legal entities in Germany would be shown towards the user. In such case, it could be helpful to nevertheless also allow the end-user to filter on the name or address of the German entities that are being shown, by setting these datafields to dynamic and selecting them in the right-hand dropdown list.
Also note that the input box that will allow the end-user to effectively type in characters to filter on, will only be shown as from the moment the spreadbase contains at least 10 records. Below that number it is considered not very useful to show a filter.
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.
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).
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.
Prefix predef^will ensure that a question (X) will only ever receives values from “itself”. Hence, if some other question (Y) also integrates with the same spreadbase, then the value selected by the end-user in question Y will not get distributed into the answer of question X.
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).
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.
If a part of a concatenated tag starts with an exclamation mark, then that tag will be skipped if the entire value is empty up to that point. For example, in the tag |ent^zip|!, |ent^city| the comma in the middle part will be skipped when the ZIP happens to be empty, to avoid that a needless comma would end up in the result.
There, you can click the button to create a new spreadbase and configure the following options.
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 .
You can import data from an existing Excel-spreadsheet, by using the button. Clause9 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.
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.
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.
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”.
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.
Instead of using the prefix of the service-instance, you can also use the prefix predef^. (If you have chosen a service integration as the source of your predefined values, and you subsequently choose a field from that spreadbase through the button, then the prefix will automatically become predef^).
