Open navigation

Externally maintained value sets

Modified on Wed, 10 May, 2023 at 3:25 PM

A value set is a predefined list of possible entries that a user can choose from a drop-down list when entering data into a header or row in an Aico document. 

This article will deal with value sets that can be set up to be populated automatically from an external system (typically the ERP) or a file through integration.

You can see an example of a cost centre list below, where there are 443 results. These are being read from the ERP. They have not been manually inputted.

Follow the steps described previous article to create a new value set. But in this scenario, we are going to set the import functions, each of which will be described below.

Set the Import function (1) you are calling, together with the External Column (2). These are set in the External mappings table which shows how different sources can be set, such as retrieving the data directly from the ERP using a function, or retrieving these from a file on an SFTP. This is a more complex area and will most likely require Aico professional services to set up. 

The Import attributes (3) can be used to set additional input parameters for a function which would be unique for each value set. For example, you could call a BAPI in SAP which returns a list of Profit centres. This BAPI might require several inputs, some of which are fixed. Others might vary based on the company code, such as the controlling area.

The type (4) is significant:

Integration - This is used when a live call is made to an external system to read in values "on line". In this scenario, no values are stored in the Aico database and you will not see any values when looking at the Value set data itself.

Local - the most common setup means that the values will be stored locally in the Aico database. This is also the setting used in an externally maintained value set where we import the values periodically.

If you set the Local type, the frequency is set in the master data import cycle parameters in the parameters tab:

Set either the first two (mandatory) or all four of these parameters

Master Data import Cycle Duration - specifies a time interval after which the functions will be called each time. This should be used if you want to refresh more than once per day and the refresh can be done throughout the day. If you set PT4H then the value sets will be refreshed every 4 hours.

Master Data Import Cycle Error Duration - specifies when the function should be tried again if it errored the first time e.g., through a connection error. PT1H means it will wait 1 hour and then retry, after which it will revert to the PT4H interval.

Master data Import Cycle Time (this is the last parameter in the list) - This is the most common setting for most of our customers and allows you to set the import function to be called in the middle of the night, but it only allows for once-a-day import.

Master Data Import Cycle Error Time - specifies when the function should be tried again if it errored the first time, e.g. through a connection error. This parameter works in conjunction with the cycle time above and set a specific time when the import function should try again if it fails.

Note: Remember you cannot use the time of day settings without the cycle duration/error parameters being set.

The integration name (5) is only needed if you want to use a different integration to the one configured on the template the value set is linked to. A value set is connected to a header or a row on a template. In most cases a value set that is read in from an external source is company-specific. For example, I want to get a list of accounts linked to Company Code 1000.

By default, the import function will use the integration connected to the company and template.

If you want the import function to use a specific integration rather than the one described above, you set it in the Integration name which can be found in the Accounting Systems in the parameters.

Remove leading zeros in import (6) - This is a simple check box and will remove any leading zeros on imported values. For example, SAP stores all numbers as 10 digits, however, it displays only the numbers in its user interface, therefore, Account 123456 is stored as 0000123456. When we read them into Aico we want to remove these leading zeros so the user sees the account number they expect.

Blacklist ID in import - You can filter values out of the imported list based on values in another value set.

For example, you could create a value set called "Excluded values" which contains account values 9000,90001,90002...90099.

When the values are read in using the import function, Aico will filter out the values you have set on the "Excluded values" value set.

You must set the value set ID of the Excluded values as the Blacklist ID (not the name).


Regular expression filters - There are three settings relating to using a regular expression to filter in or out values that match a regex pattern.

 

The regular expression filter in import is where you write the regex to find the pattern you are looking for. By default, the import will only include values that match the pattern.

The exclusive filter in import (slightly wrongly named) is a Boolean which you check if you want to exclude the values found that match the regex pattern. Effectively it negates the regex result.

Filter value in import allows you to specify which value set field you are directing the regex at. This can be the "value" (1), "description" (2) or "filterValue"(3).

In the above example the Regular expression ^[*] finds values that start with an "*". Since the exclusive filter is ticked that means we want to exclude these results. Finally, we are asking for the regex to check the description. The result will be that any value with a description that starts with an "*" will be excluded. Therefore, "*Account Payables" would be excluded whereas "Account Payables" would not.

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article