Table of Contents
Power integration of M-Files with MS SQL database.
- SQL integration module is used for power integration of M-Files with MS SQL database
- Execute/Call stored procedure with parameters based on Event or Schedule
- Read data from database based on event or schedule
Configuration #
After you have successfully installed the application, you should configure SQL Integration. 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 SQL integration
Field description #
| Field | Value | Description |
| Enabled | Yes | Specifies whether the module is in use. |
| Configuration mode | Simple/Advanced | Display simple or advanced configuration options for module. |
| Rule groups [n] > Group [n] | ||
| Name | Text | Specifies the name of rule group. |
| Rule groups [n] > Group [n] > Rules [n] > Rule [n] | ||
| Name | Text | Custom rule name. |
| 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 rule. |
| Trigger type | One of the following: • ObjectCreated • ObjectChanged • ObjectEnteredState • OnComment • Scheduled • RunOnce • OnDelete | Type of event that triggers the execution of this rule. ObjectCreated. If this option is selected, rule will be executed when the source object is created. ObjectChanged. If this option is selected, rule will be executed when any change is made to the source object ObjectEnteredState. If this option is selected, rule will be executed when the source object has entered a new state. OnComment. If this option is selected, rule will be executed when the source object has new comment. RunOnce. If this option is selected, rule will be run only once and on all matching objects in vault. NOTE: On vault restart, all enabled rules with Run once trigger will be run immediately. OnDelete. If this option is selected, rule will be executed when object is deleted. |
| Business critical | Yes/No | If set to true, rule will be logged in SEQ. |
| Error handling behaviour | One of the following: Continue SuspendTransaction | Defines how errors in event handlers are handled: Continue. Log errors and continue execution. Suspend transaction. Rollback transaction and show error message. |
| Rule groups [n] > Group [n] > Rules [n] > Rule [n] > Monitored properties | ||
| Property definition | Property definition | Property definition that needs to change in order for rule to be executed. Multiple properties are combined with OR operator, meaning that at least one of the properties has to change for rule to be executed. |
| Rule groups [n] > Group [n] > Rules [n] > Rule [n] > Trigger source | ||
| Object type | Object type | Object type of source object. |
| Class | Class | Class of source object. |
| Workflow | Workflow | Workflow of source object. |
| State | State | Workflow state of source object. NOTE. If you have entered a state, you must also enter the workflow above. |
| Rule groups [n] > Group [n] > Rules [n] > Rule [n] > Trigger source > Search filters [n] > Filter [n] | ||
| Search conditions | Search conditions | Optional additional conditions that source object needs to satisfy. Conditions inside a single filter are combined with AND, while multiple filters are combined with OR operator. |
| Rule groups [n] > Group [n] > Rules [n] > Rule [n] > Trigger source > Search filters [n] | ||
| Look in | One of the following: • LatestObjVersion • AllObjVersion | When trying to matching the above source conditions, look in all object versions, or only the latest one. Latest obj version. If this option is selected, the current object version must match the specified conditions for object to be treated as source for this rule. All obj versions. If this option is selected, any object version must match the specified conditions for object to be treated as source for this rule. |
| Rule groups [n] > Group [n] > Rules [n] > Rule [n] > Vault search [n] > Search [n] > Status field conditions [n] > Status field condition | ||
| Field | One of the following: • ID • Type • Version | Status field. |
| Operator | One of the following: • = • =! • < • > • <= • >= | Operator to use for comparing actual and expected property value. |
| Value | Status field value | Expected status field value. |
| Rule groups [n] > Group [n] > Rules [n] > Rule [n] > [Advanced] Vault search [n] > Search [n] > Property conditions [n] > Property condition [n] | ||
| Property | Property definition | Specifies the property whose value will be checked |
| Operator | One of the following: • = • =! • < • > • <= • >= • Contains • Does not • contain • Starts with • Does not start with | Operator to use for comparing actual and expected property value. |
| Rule groups [n] > Group [n] > Rules [n] > Rule [n] > [Advanced] Vault search sort | ||
| Sort by | Property definition | Sort search results by property. |
| Sort direction | One of the following: • Ascending • Descending | Specifies in which direction results can be shown. |
| Rule groups [n] > Group [n] > Rules [n] > Rule [n] | ||
| Search timeout (seconds) | Integer | Search timeout in seconds. |
| Rule groups [n] > Group [n] > Rules [n] > Rule [n] > SQL settings > Connection string | ||
| Value | String | Database connection string. |
| Use secure password | Yes/No | Specified password to use in connection string. |
| Rule groups [n] > Group [n] > Rules [n] > Rule [n] > SQL settings | ||
| Query type | One of the following: • Query • StoredProcedure | Type of SQL query that will be executed. |
| Query | String | Specified SQL Query. |
| Value from | One of the following: • Source • Vault Search | Specifies whether to resolve placeholder expressions from trigger source or from vault search. |
| Result type | One of the following: • None • SimpleValue • ObjectID • ObjectName • ValueListItemID • ValueListItemName | Displayed options for showing results. |
| Rule groups [n] > Group [n] > Rules [n] > Rule [n] > SQL settings > Target property | ||
| Target property | Property definition | Optional save result of SQL query to selected property. |
| Rule groups [n] > Group [n] > Rules [n] > Rule [n] > SQL settings > Rollback Query | ||
| Query type | One of the following: • Query • StoredProcedure | Type of SQL query that will be executed. |
| Query | String | Specified SQL Query. |
| Advanced Settings | ||
| Time to wait (milliseconds) | Integer | Numbers of milliseconds to wait before executing any rule in background. |
| Number of retries | Integer | Maximum numbers of times COM errors are retried. |
| Retry interval (milliseconds) | Integer | Number of milliseconds to wait between retries. |
Use Cases #
When contract is active send data to database #
Customer is using M-Files for incoming invoices. In a relevant software, invoice gets processed and OCRed, and then sent for approval to relevant persons. When invoice is approved, data is sent for processing to ERP systems.
Configuration #
When Contract become Active rule will be executed.
The result of this configuration will be the execution of a stored procedure and adding Contract data from M-Files to the database.
When invoice is approved, send data for processing to database #
Customer is using M-Files for incoming invoices. In a relevant software, invoice gets processed and OCRed, and then sent for approval to relevant persons. When invoice is approved, data is sent for processing to ERP systems.
Configuration #
When AP Invoices entered state, Approved rule will be executed.
The result of this configuration will be the execution of a stored procedure and adding Invoice data from M-Files to the database.
