Unitfly Customer Portal

SQL Integration

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

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

Field description

FieldValueDescription
EnabledYesSpecifies whether the module is in use.
Configuration modeSimple/AdvancedDisplay simple or advanced configuration options for module.
Rule groups [n] > Group [n]
NameTextSpecifies the name of rule group.
Rule groups [n] > Group [n] > Rules [n] > Rule [n]
NameTextCustom rule name.
DescriptionTextOptional rule description.
EnabledYes/NoSpecifies whether the rule is in use.
Configuration modeSimple/AdvancedDisplay simple or advanced configuration options for rule.
Trigger typeOne 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 criticalYes/NoIf set to true, rule will be logged in SEQ.
Error handling behaviourOne 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 definitionProperty definitionProperty 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 typeObject typeObject type of source object.
ClassClassClass of source object.
WorkflowWorkflowWorkflow of source object.
StateStateWorkflow 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 conditionsSearch conditionsOptional 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 inOne 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
FieldOne of the following:
• ID
• Type
• Version
Status field.
OperatorOne of the
following:
• =
• =!
• <
• >
• <=
• >=
Operator to use for comparing actual and expected property value.
ValueStatus field valueExpected status field value.
Rule groups [n] > Group [n] > Rules [n] > Rule [n] > [Advanced] Vault search [n] > Search [n] > Property conditions [n] > Property condition [n]
PropertyProperty definitionSpecifies the property whose value will be checked
OperatorOne 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 byProperty definitionSort search results by property.
Sort directionOne 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)IntegerSearch timeout in seconds.
Rule groups [n] > Group [n] > Rules [n] > Rule [n] > SQL settings > Connection string
ValueStringDatabase connection string.
Use secure passwordYes/NoSpecified password to use in connection string.
Rule groups [n] > Group [n] > Rules [n] > Rule [n] > SQL settings
Query typeOne of the following:
• Query
• StoredProcedure
Type of SQL query that will be executed.
QueryStringSpecified SQL Query.
Value fromOne of the following:
• Source
• Vault Search
Specifies whether to resolve placeholder expressions from trigger source or from vault search.
Result typeOne 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 propertyProperty definitionOptional save result of SQL query to selected property.
Rule groups [n] > Group [n] > Rules [n] > Rule [n] > SQL settings > Rollback Query
Query typeOne of the following:
• Query
• StoredProcedure
Type of SQL query that will be executed.
QueryStringSpecified SQL Query.
Advanced Settings
Time to wait (milliseconds)IntegerNumbers of milliseconds to wait before executing any rule in background.
Number of retriesIntegerMaximum numbers of times COM errors are retried.
Retry interval (milliseconds)IntegerNumber 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.

Configuration of SQL Integration
Configuration of SQL Integration

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.

Configuration of SQL Integration
Configuration of SQL Integration
Configuration of SQL Integration
Configuration of SQL Integration

The result of this configuration will be the execution of a stored procedure and adding Inovice data from M-Files to the database.

Table of Contents