Create calculated properties whose value is the result of an SQL query or stored procedure. Module is configured by adding calculation rules in application configuration and selected properties are automatically converted to calculated properties. Currently, only SQL Server databases are supported.
Configuration #
After you have successfully installed the application, you can start adding calculation rules.
- Open M-Files Admin.
- In the left-side tree view, expand the desired connection to M-Files Server.
- In the left-side tree view, expand the Document Vaults node.
- Still, in the left-side tree view, expand the vault where you installed the connector and select Configurations.
- Expand Other Applications.
- Expand Extension Kit.
- Click on Property builder.
Field Description #
| Field | Value | Description |
| Enabled | Yes/No | Specifies whether the module is in use. |
| Configuration Mode | Simple/Advanced | Displayed simple or advanced configuration options. |
| Rule groups > Group [n] | ||
| Name | Text | Specifies name of the rule group. |
| Rule groups > Group [n] > Rules > Rule [n] | ||
| Name | Text | Specifies name of the rule. |
| Description | Text | Optional rule description. |
| Enabled | Yes/No | Specifies whether the rule is in use. |
| Configuration mode | Simple/Advanced | Display simple or advanced configuration options for module. |
| Property | Property definition | Property whose value will be set to the result of specified SQL query. |
| Property type | SQL | Type of property. Currently, only SQL properties are supported. |
| Recalculate daily | Yes/No | If it set to yes, property will be recalculated daily between midnight and 1 am. |
| Recalculate properties | Yes/No | If it set to yes, it will recalculate chosen properties when clicked Recalculate on dashboard. |
| Business Critical | Yes/No | If set to true, rule will be logged in SEQ. |
| Rule groups > Group [n] > Rules > Rule [n] > Filter Conditions | ||
| SearchConditions | Search Conditions | Displayed only if [Advanced] is selected as Configuration Mode. Optional filters that object must match in order for property to be calculated. Multiple filters are combined with OR operator. |
| Rule groups > Group [n] > Rules > Rule [n] > SQL settings | ||
| Connection string | Text | Connection string to the SQL Server database. Example: Data Source=10.0.0.7; Initial Catalog=TestDB; Persist Security Info=True; User ID=dbuser; Password=passw!23 |
| Result type | One of the following: • Simple value • Object ID • Object Name • Value list item • ID • Value list item name | Specifies what the result of SQL query represents. Simple value is used when resulting property is not lookup based. When resulting property is lookup based, returned value can be either object’s, or value list item’s ID or name. |
| Object type of query result | Object type | Displayed only when Query result type is Object Name. Expected object type of result. |
| Object class of query result | Object class | Displayed only when Query result type is Object Name. Expected object class of result. |
| Query type | One of the following: • Query • Stored procedure | Type of SQL query to execute. |
| Query | Text with placeholders | Displayed only when Query type is Query. SQL query to execute. |
| Stored procedure name | Text with placeholders | Displayed only when Query type is Stored procedure. Name of stored procedure to execute. |
| Rule groups > Group [n] > Rules > Rule [n] > SQL settings > Stored procedure parameter [n] | ||
| Name | Name | SQL parameter name. |
| Mode | One of the following: • Static • Dynamic | Static. Entered parameter value will be sent as-is. Dynamic. Placeholders will be resolved in entered parameter value. |
| Database Type | One of the following: AnsiString FixedLength Binary Boolean Byte Currency Date DateTime DateTime2 DateTimeOffset Decimal Double Guid Int16 Int32 Int64 Object SByte SingleString String FixedLength Time UInt16 UInt32 UInt64 VarNumeric Xml | Database data types from docs.microsoft.com/en-us/dotnet/api/system.data.dbtype AnsiString. A variable-length stream of non-Unicode characters ranging between 1 and 8,000 characters. AnsiStringFixedLength. A fixed-length stream of non-Unicode characters. Binary. A variable-length stream of binary data ranging between 1 and 8,000 bytes. Boolean. A simple type representing Boolean values of true or false. Byte. An 8-bit unsigned integer ranging in value from 0 to 255. Currency. A currency value ranging from -2 63 (or -922,337,203,685,477.5808) to 2 63 -1 (or +922,337,203,685,477.5807) with an accuracy to a ten-thousandth of a currency unit. Date. A type representing a date value. DateTime. A type representing a date and time value. DateTime2. Date and time data. Date value range is from January 1,1 AD through December 31, 9999 AD. Time value range is 00:00:00 through 23:59:59.9999999 with an accuracy of 100 nanoseconds. DateTimeOffset. Date and time data with time zone awareness. Date value range is from January 1,1 AD through December 31, 9999 AD. Time value range is 00:00:00 through 23:59:59.9999999 with an accuracy of 100 nanoseconds. Time zone value range is -14:00 through +14:00. Decimal. A simple type representing values ranging from 1.0 x 10 -28 to approximately 7.9 x 10 28 with 28-29 significant digits. Double. A floating point type representing values ranging from approximately 5.0 x 10 -324 to 1.7 x 10 308 with a precision of 15-16 digits. Guid. A globally unique identifier (or GUID). Int16. An integral type representing signed 16-bit integers with values between -32768 and 32767. Int32. An integral type representing signed 32-bit integers with values between -2147483648 and 2147483647. Int64. An integral type representing signed 64-bit integers with values between -9223372036854775808 and 9223372036854775807. Object. A general type representing any reference or value type not explicitly represented by another DbType value. SByte. An integral type representing signed 8-bit integers with values between -128 and 127. Single. A floating point type representing values ranging from approximately 1.5 x 10 -45 to 3.4 x 10 38 with a precision of 7 digits. String. A type representing Unicode character strings. StringFixedLength. A fixed-length string of Unicode characters. Time. A type representing a SQL Server DateTime value. If you want to use a SQL Server time value, use Time. UInt16. An integral type representing unsigned 16-bit integers with values between 0 and 65535. UInt32. An integral type representing unsigned 32-bit integers with values between 0 and 4294967295. UInt64. An integral type representing unsigned 64-bit integers with values between 0 and 18446744073709551615. VarNumeric. A variable-length numeric value. Xml. A parsed representation of an XML document or fragment. |
| Value | Value | Displayed only if [Dynamic] is selected as Parameter mode. Parameter value |
| Rule groups > Group [n] > Rules > Rule [n] > SQL settings > Advanced settings | ||
| Time to wait | Integer | Number of milliseconds to wait before executing any rule in background. |
| Number of retries | Integer | Maximum number of times COM error is retried. |
| Retry interval (milliseconds) | Integer | Numbers of milliseconds to wait between retries. |
Use Cases #
Getting organization structure information to an M-Files from AD and MIM #
Customer is a big manufacturing company and they are maintaining organizational structure: roles, positions and superiors in Active Directory. For managing identities through organization, customer is using Microsoft Identity Manager (MIM). With Microsoft Identity Manager they are exporting organizational structure to MS SQL database. In all workflows they are using roles and positions which are propagated automatically by Extended property builder.
User roles
| USERNAME | LAST_NAME | FIRST_NAME | ORG_UNIT | USERID | FULLNAME | JOBTITLE | LOGIN | SUPERIOR |
| CRONALDO | Ronaldo | Cristiano | Forwards | 6 | Cristiano Ronaldo | CS | cronaldo | 8 |
| LMESSI | Messi | Lionel | Forwards | 7 | Lionel Messi | CS | lmessi | 8 |
| LMODRIC | Modric | Luka | Midfielders | 8 | Luka Modric | MF | lmodric | NULL |
Employees are synchronized from Active Directory using M-Files Active Directory Group synchronization. When new person is created, user is synchronized and created in M-Files using Compliance Kit – User Synchronization Module.
For every Person customer need their position and their superior (Picture 1). This has been done by using Property builder.
Configuration of Property builder #
Customer has a query that provides Job Title based on username. On Picture 2. we can see connection string to database.
Query is written in Placeholder Editor and Property Username has been used in query as a parameter (Picture 3). Username is taken from same object where property Job Title is, or you can use different levels, but object where username is the root object.
When Rule is enabled, Job Title property is automatic property and Job Title is populated.
Now, there is a store procedure which calculate property who is your superior from database, Picture 4.
To execute store procedure, we use EXEC command in SQL window. As returned value is FullName of Person, we have configured that result is Person. So, Person have now two auto calculated properties, Position and Superior (Picture 5).
Providing financial information about project from database #
Customer is big Pharmaceutical company and there are using M-Files as a Project Management system. For every Project they need their financial information on a project. Financial information is extracted from SAP to database. They are using Property builder to add additional project financial information on a project object from SQL database.
