How Can We Help?

Property Builder

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.

  1. Open M-Files Admin.
  2. In the left-side tree view, expand the desired connection to M-Files Server.
  3. In the left-side tree view, expand the Document Vaults node.
  4. Still, in the left-side tree view, expand the vault where you installed the connector and select Configurations.
  5. Expand Other Applications.
  6. Expand Extension Kit.
  7. Click on Extended property builder.

Field Description

FieldValueDescription
EnabledYes/NoSpecifies whether the module is in use.
Configuration ModeSimple/AdvancedDisplayed simple or advanced configuration options.
Rule groups > Group [n]
NameTextSpecifies name of the rule group.
Rule groups > Group [n] > Rules > Rule [n]
NameTextSpecifies name of the rule.
DescriptionTextOptional rule description.
EnabledYes/NoSpecifies whether the rule is in use.
Configuration modeSimple/AdvancedDisplay simple or advanced configuration options for module.
PropertyProperty definitionProperty whose value will be set to the result of specified SQL query.
Property typeSQLType of property. Currently, only SQL properties are supported.
Recalculate dailyYes/NoIf it set to yes, property will be recalculated daily between midnight and 1 am.
Recalculate propertiesYes/NoIf it set to yes, it will recalculate chosen properties when clicked Recalculate on dashboard.
Business CriticalYes/NoIf set to true, rule will be logged in SEQ.
Rule groups > Group [n] > Rules > Rule [n] > Filter Conditions
SearchConditionsSearch ConditionsDisplayed 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 stringTextConnection 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 typeOne 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 resultObject typeDisplayed only when Query result type is Object Name.
Expected object type of result.
Object class of query resultObject classDisplayed only when Query result type is Object Name.
Expected object class of result.
Query typeOne of the following:
• Query
• Stored procedure
Type of SQL query to execute.
QueryText with placeholdersDisplayed only when Query type is Query.
SQL query to execute.
Stored procedure nameText with placeholdersDisplayed 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]
NameNameSQL parameter name.
ModeOne of the following:
• Static
• Dynamic
Static. Entered parameter value will be sent as-is.
Dynamic. Placeholders will be resolved in entered parameter value.
Database TypeOne 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.
ValueValueDisplayed only if [Dynamic] is selected as Parameter mode. Parameter value
Rule groups > Group [n] > Rules > Rule [n] > SQL settings > Advanced settings
Time to waitIntegerNumber of milliseconds to wait before executing any rule in background.
Number of retriesIntegerMaximum number of times COM error is retried.
Retry interval (milliseconds)IntegerNumbers 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

USERNAMELAST_NAMEFIRST_NAMEORG_UNITUSERIDFULLNAMEJOBTITLELOGINSUPERIOR
CRONALDORonaldoCristianoForwards6Cristiano RonaldoCScronaldo8
LMESSIMessiLionelForwards7Lionel MessiCSlmessi8
LMODRICModricLukaMidfielders8Luka ModricMFlmodricNULL
Table 1. Organizational structure from AD

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.

Picture 1. Person

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.

Picture 2. Basic Configuration

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.

Picture 3. Query with Parameter

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.

Picture 4. Configuration store procedure

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).

Picture 5. Person – M-Files

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 Extended property builder to add additional project financial information on a project object from SQL database.

Table of Contents
Free webinar

Unlock the full potential of M-Files

No coding, faster configuration and better quality for your clients. See Extension Kit in action!