Set properties #
This section provides options to set properties in M-Files using the results returned from an SQL query.
- Expand the Set properties section.
- Click on Add SqlQueryResultProperty.
- Expand SqlQueryResultProperty [1].
The following options are available by default: Reference column by, Column index, Property, and Result type. Based on the selected values, more options may appear.
Reference column by #
Set properties > SqlQueryResultProperty [1] > Reference column by
This option determines how to reference the column from the SQL result set.
- Index (default): The column is referenced by its index. If selected, the Column index field is available.
- Name: The column is referenced by its name. If selected, the Column Name field is available.
Column index #
Set properties > SqlQueryResultProperty [1] > Column index
Displayed when Reference column by is Index.
Specifies the zero-based index of the column in the SQL result set that should be used to set the M-Files property (e.g. 0 for the first column, 1 for the second, etc.). Example: If you want to use the data from the first column in the result set, you would enter 0 here.
Column name #
Set properties > SqlQueryResultProperty [1] > Column name
Displayed when Reference column by is Name.
Specifies the exact name of the column in the SQL result set that should be used to set the M-Files property. Example: If your SQL query returns a column named EmployeeName, you would enter EmployeeName here.
Property #
Set properties > SqlQueryResultProperty [1] > Property
This option specifies the M-Files property to set using the value from the SQL result set column. Select the property from the value list.
Result type #
Set properties > SqlQueryResultProperty [1] > Result type
Specifies what the value from the SQL result represents.
- None (default): No special handling is applied to the result value. This option can be used when the result value doesn’t need to be mapped to any specific lookup or value list.
- SimpleValue: Select this option if the result is a straightforward value (e.g., a string, number, or date) that should be directly set to the specified M-Files property.
- ValueListLookup: Select this option if the result is intended to reference a value from a value list. When selected, the Map Value List Lookup By field is available.
- ObjectLookup: Select this option if the result should be used to reference an object in M-Files. When selected, the Map Object Lookup By field is available.
Map Value list lookup by #
Set properties > SqlQueryResultProperty [1] > Result type: ValueListLookUp > Map Value list lookup by
Displayed only when Result type is ValueListLookup.
Specifies how to map the result of a value list lookup property.
- ValueListItemName (default): If selected, the Create if doesn’t exist field appears.
- ValueListItemID: If selected, the Throw if all items don’t exist field appears.
- ValueListItemGUID: If selected, the Throw if all items don’t exist field appears.
- ExternalValueListItemID: If selected, the Throw if all items don’t exist field appears.
Map Object lookup by #
Set properties > SqlQueryResultProperty [1] > Result type: ObjectLookUp > Map Object lookup by
Displayed only when Result type is ObjectLookup.
Specifies how to map the result of an object lookup property.
- ObjectName (default): If selected, the following fields are available: Object type of result, Object class of result, and Create if doesn’t exist.
- ObjectID: If selected, the Throw if all items don’t exist field appears.
- ExternalObjectID: If selected, the Throw if all items don’t exist field appears.
- ObjectProperty: If selected, the following fields are available: Object type of result, Object class of result, Result M-Files object property, Create if doesn’t exist, and Search timeout (sec).
Create if doesn’t exist #
Set properties > SqlQueryResultProperty [1] > Create if doesn’t exist
Displayed when Result type is ValueListItemLookup, and Map Value list lookup by is ValueListItemName. Also displayed when Result type is ObjectLookup and Map Object lookup by is ObjectName or ObjectProperty.
If a target property is lookup-based, this setting controls whether a specified object or value list item will be created, if they do not already exist in the target vault.
- No (default): If selected, the system will not create new items if they do not exist in the target vault.
- Yes: If selected, the system will create new items in the target vault if they do not already exist. NOTE! For value list lookups, ensure that the “Allow users to add new values to this list” checkbox is enabled. For real object lookups, note that only objects without any required properties can be created through this setting, because only name and class properties will be set on creation.
Throw if all items don’t exist #
Set properties > SqlQueryResultProperty [1] > Throw if all items don’t exist
Displayed when Result type is ValueListLookup and Map Value list lookup by is ValueListItemID, ValueListItemGUID, or ExternalValueListItemID. Also displayed when Result type is ObjectLookup and Map Object lookup by is ObjectID or ExternalObjectID.
If the target property is lookup-based, this setting controls whether all existing items will be added or not. If at least one item doesn’t exist in the target vault and this is set to true, then no items will be added to the lookup property.
- Yes (default): If selected, and if at least one item does not exist in the target vault, none of the items will be added to the lookup property.
- No: If selected, the system will add all existing items, even if some items from the SQL result set are missing.
Object type of result #
Set properties > SqlQueryResultProperty [1] > Result type: ObjectLookUp > Map Object lookup by: ObjectName
Displayed only when Result type is ObjectLookup and Map Object lookup by is ObjectName.
Specifies the object type of results. Choose from the value list.
Object class of result #
Set properties > SqlQueryResultProperty [1] > Result type: ObjectLookUp > Map Object lookup by: ObjectName
Displayed only when Result type is ObjectLookup and Map Object lookup by is ObjectName.
Specifies the class of objects in the lookup. Choose from the value list.
Result M-Files object property #
Set properties > SqlQueryResultProperty [1] > Result type: ObjectLookUp > Map Object lookup by: ObjectProperty
Displayed only when Result type is ObjectLookup and Map Object lookup by is ObjectProperty.
This field specifies the object property that will be used for the lookup. This allows you to define which specific object property will be used to match the SQL result. Example: If you are using an object property such as EmployeeID for the lookup, select EmployeeID from the list. This ensures that the SQL result is matched based on the specified object property.
Set properties on #
Determines the object where the properties will be set based on the SQL query results. The available options are:
- Source (default): Set properties on the object that triggered the rule.
- Vault search: Set properties based on a search performed within the vault.
- Specify: Set properties on objects returned from an additional vault search which is configured using the following fields: Static search filters for Value from, Dynamic search filters for Value from, Search results sort, and Exclude source from search results.
Static search filters for Value from #
Set properties on: Specify > Static search filters for Value from
Displayed only when Set properties on is Specify.
This setting lets you define static filters for the vault search that will be used to determine the objects on which properties will be set. Multiple search filters are combined with the OR operator (at least one must be satisfied). Multiple search conditions within a single search filter are combined with the AND operator (all must be satisfied).
- Expand Static search filters for Value from.
- Click on Add Static search filter.
- Expand Static search filter [1].
When expanded, the Search conditions field becomes available to specify the criteria for the static search filters. Example: To filter objects where the Status property is Active, you would create a static search filter with conditions Status = Active.
Dynamic search filters for Value from #
Set properties on: Specify > Dynamic search filters for Value from
Displayed only when Set properties on is Specify.
Dynamic expressions in these filters will be resolved from the trigger source. Multiple search filters are combined with the OR operator (at least one must be satisfied). Multiple search conditions within a single search filter are combined with the AND operator (all must be satisfied). NOTE! If static search filters are not set, then it is mandatory to include the target class condition in dynamic search filters.
- Expand Dynamic search filters for Value from.
- Click on Add Dynamic search filter.
- Expand Dynamic search filter [1].
When expanded, the following fields are available: Property, Operator, Value and Mode. For detailed descriptions of the options, please refer to this page.
Search results sort #
Set properties on: Specify > Search results sort
Displayed only when Set properties on is Specify.
Specifies how to sort search results. When expanded, the following fields are available: Sort by and Sort direction.
Exclude source from search results #
Set properties on: Specify > Exclude source from search results
Displayed only when Set properties on is Specify.
Specifies whether to exclude the source object from vault search results. This option is useful when there are multiple objects with the same name, as it allows users to exclude the object that triggered the rule.
- No (default): The source object will not be excluded from the vault search results.
- Yes: The source object will be excluded from the vault search results.
Properties #
This section allows you to set properties not directly related to an SQL response. The properties can be set on the source, vault search, or specified search objects, depending on how the Set properties on configuration is set. These properties can either be static or dynamic, referencing properties from the source object.
- Expand Properties.
- Click on Add Property setter.
- Expand Property setter [1].
The following fields are available by default: Property, Value from, Value type, Value, Multi select lookup & multi line text behaviour, Add as additional property, and Update option.
Value from #
Properties > Property setter > Value from
Specifies the option for searching the value. Choose one of the following options:
- Source (default): Searches for the value from the trigger source.
- Vault Search: Searches the vault.
- Specify: Specifies where to get the value from. If selected, additional fields are available: Static search filters for Value from , Dynamic search filters for Value from, Search results sort, Exclude source from search results.
Value type #
Properties > Property setter > Value type
Controls how the value of the property will be calculated: either directly by copying the value from the source or as a function of selected property values.
- Value (default): The value of the property will be copied directly from the source. When this option is selected, the Multi-select lookup & multi-line text behaviour and Value fields become available.
- Function: The value of the property is calculated as a function of other property values. When this option is selected, the Function section appears. For details on the Function section, please refer to this page.
Multi-select lookup & multi-line text behaviour #
Properties > Property setter > Value type: Value > Multi-select lookup & multi-line text behaviour
Displayed only if Value Type is Value.
If the property is a multi-select lookup or multi-line text, this setting defines how the selected value will be added. There are three options:
- Overwrite (default): The existing value will be overwritten.
- Append: The selected value will be appended to the existing value.
- Remove: The value will be removed from the property.
Add as additional property #
Properties > Property setter > Add as additional property
Determines whether to add the property to the object as an additional property when it does not exist on the target object.
- No (default): The property is not added if it doesn’t exist on the target.
- Yes: The property will be added to the object if it doesn’t exist on the target.
Update option #
Properties > Property setter > Update option
Defines additional conditions for determining when the property value will be updated.
- Always (default): The property will always be updated with the new value.
- OnlyWhenTargetEmpty: The property is updated only if the target property is empty. This option prevents overwriting existing values.
- OnlyWhenSourceNotEmpty: The property is updated only if the source has a non-empty value, preventing updates with empty values.
