- Macro Expressions
- 1. Date Part Expressions
- 2. Date Format Expressions
- 3. Date Diff Expressions
- 4. Date add parsers
- 5. Filter Lookups Expression
- 6. Format Lookups Expressions
- 7. Math Expressions
- 8. Ternary Operator Expressions
- 9. Text transform parser
- 10. Translation Expressions
- 11. Regex parse
- 12. File metadata parse
- 13. Combining multiple Macros
- Configuring Auto properties
- Use Cases
Auto properties module extends property concatenation expression language with additional macros for text formatting and translation, date manipulations and numeric calculations. When property concatenation expression contains any of the supported macros, it is automatically converted into calculated property and its new value is calculated.
Macro Expressions #
- Date Part Expressions
- Date Format Expressions
- Date Diff Expressions
- Date add parsers
- Filter Lookups Expression
- Format Lookups Expressions
- Math Expressions
- Ternary Operator Expressions
- Text transform parser
- Translation Expressions
- Regex parse
- File metadata parse
- Combining multiple Macros
1. Date Part Expressions #
Expressions
Description
<<YEAR>> Gets current year
<<MONTH>>Gets current month
<<DAY>>Gets current day of month
<<HOUR>>Gets current hour
<<MINUTE>>Gets current minute
<<SECOND>>Gets current second
<<YEAR(<date>)>>Gets the year part of the specified date property or expression.
Eq. <<YEAR(%PROPERTY_20%)>>
<<MONTH(<date>)>>Gets the month part of the specified date property or expression.
Eq. <<MONTH(%PROPERTY_20%)>>
<<DAY(<date>)>>Gets the day part of the specified date property or expression.
Eq. <<DAY(%PROPERTY_20%)>>
<<HOUR(<date>)>>Gets hour part of the specified date property or expression.
Eq. <<HOUR(%PROPERTY_20%)>>
<<MINUTE(<date>)>>Gets the minute part of the specified date property or expression.
Eq. <<MINUTE(%PROPERTY_20%)>>
<<SECOND(<date>)>>Gets the second part of the specified date property or expression.
Eq. <<SECOND(%PROPERTY_20%)>>
Example #
| Property Name | Property ID | Property Value | Data Type |
| StartDate | 42 | December 24, 2020 | Date |
Input: <<YEAR(%PROPERTY_42%)>>
Result: 2020
Input: <<MONTH>>
Result: 12
2. Date Format Expressions #
Expression
Description
<<NOW>>Gets current date and time, formatted according to the default System account format.
<<TODAY>>Gets current date, formatted according to the default System account format.
<<NOW(<format>)>>Gets current date and time, formatted according to the specified custom format.
<<TODAY(<format>)>>Gets current date, formatted according to the specified custom format.
<<DATE('<property>','<format>')>>Gets value of date property or expression, formatted according to the specified custom format.
Eq. <<DATE(‘%PROPERTY_20%’,’yyyy-MM-dd’)>>
Format specifiers that can be used in these expressions are the default C# format specifiers, as specified in https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings.
Example #
| Property Name | Property ID | Property Value | Data Type |
| StartDate | 42 | December 24, 2020 | Date |
| Now = current date = eg. April 1st 2020 | 0 |
Input: <<DATE(‘%PROPERTY_42%’,’MM/dd/yyyy’)>>
Result: 12/24/2020
Input: <<NOW(ddMMyy)>>
Result: 010420
3. Date Diff Expressions #
Expression
Description
<<DATEDIFF('<date>','<date>','<units>')>>Gets difference between two date properties or expressions.
Units can be any of the following:
• YEARS
• MONTHS
• WEEKS
• DAYS
Eq. <<DATEDIFF(‘<<TODAY>>’, ‘%PROPERTY_20%’, ‘DAYS’)>> calculates age of any object (difference between current date and document’s creation date).
Example #
| Property Name | Property ID | Property Value | Data Type |
| StartDate | 42 | December 24, 2020 | Date |
| EndDate | 50 | March 2, 2021 | Date |
| Now = current date = eg. April 1st 2020 | 0 |
Input: <<DATEDIFF(‘%PROPERTY_42%’,’%PROPERTY_50%’,’DAYS’)>>
Result: 41
Input: <<DATEDIFF(‘<<NOW>>’,’%PROPERTY_50%’,’DAYS’)>>
Result: 308
4. Date add parsers #
Expression
Description
<<DATEADD('<date>','<value>','<units>')>>Adds unit of time to the specified date.
Units can be any of the following:
• YEARS
• MONTHS
• WEEKS
• DAYS
Eq. <<DATEADD(‘<<TODAY>>’, ‘%PROPERTY_20%’, 7′,’DAYS’)>> add 7 days to property date (difference between current date and document’s creation date).
Example #
| Property Name | Property ID | Property Value | Data Type |
| SubmittedDate | 42 | November 1, 2021 | Date |
Input: <<DATEADD(%PROPERY_20%’,’7′,’DAYS’)>>
Result: 17.01.2021
5. Filter Lookups Expression #
Expression
Description
<<FIRST('%PROPERTY_ID%')>>Selects the first lookup from multi select lookup property.
<<LAST('%PROPERTY_ID%')>>Selects the last lookup from multiselect lookup property.
Note: For older M-Files versions where %PROPERTY_ID%.%PROPERTY_ID% syntax is not supported, property relations to second, third, etc. level can be achieved like below.
<<FIRST('%PROPERTY_ID%','%PROPERTY_ID%',...)>><<LAST('%PROPERTY_ID%','%PROPERTY_ID%',...)>>Example #
| Property Name | Property ID | Property Value | Data Type |
| Contract | 60 | NDA – Alan | Choose from list |
| Years | 75 | 2018,2019,2020 | Choose from list (multi-select) |
Input: <<FIRST(‘%PROPERTY_75%’)>>
Result: 2018
Input: <<LAST(‘%PROPERTY_60%’.’%PROPERTY_75%’)>>
Result: 2020
Input: <<LAST(‘%PROPERTY_60%’,’%PROPERTY_75%’)>>
Result: 2020
6. Format Lookups Expressions #
Expression
Description
<<FORMATLOOKUPS('<separator>','%PROPERTY_ID%')>>Formats selected multiselect lookup property as text.
Note: For older M-Files versions where %PROPERTY_ID%.%PROPERTY_ID% syntax is not supported, property relations to second, third, etc. level can be achieved like below.
<<FORMATLOOKUPS('<separator>','%PROPERTY_ID%',...)>>Example #
| Property Name | Property ID | Property Value | Data Type |
| Contract | 60 | NDA – Alan | Choose from list |
| Years | 75 | 2018,2019,2020 | Choose from list (multi-select) |
Input: <<FORMATLOOKUPS(‘;’,’%PROPERTY_75%’)>>
Result: 2018;2019;2020
Input: <<FORMATLOOKUPS(‘n’,’%PROPERTY_60%.%PROPERTY_75%’)>>
Result: 201820192020
Input: <<FORMATLOOKUPS(‘n’,’%PROPERTY_60%’,’%PROPERTY_75%’)>>
Result: 201820192020
7. Math Expressions #
Expression
Description
<<SUM('<number>','<number>',…)>>Sums numeric properties or expressions.
<<SUB('<number>','<number>',…)>>Subtracts numeric properties or expressions.
<<MUL('<number>','<number>',…)>>Multiplies numeric properties or expressions.
<<DIV('<number>','<number>',…)>>Divides numeric properties or expressions.
<<AVG('<number>','<number>',…)>>Calculates average of numeric properties or expressions.
<<MIN('<number>','<number>',…)>>Calculates minimum of numeric properties or expressions.
<<MAX('<number>','<number>',…)>>Calculates the maximum of numeric properties or expressions.
| Property Name | Property ID | Property Value | Data Type |
| Price | 66 | 1001 | Real |
| Discount (%) | 69 | 10 | Number (integer) |
Example #
Input: <<SUM(‘%PROPERTY_66%’,’%PROPERTY_66%’)>>
Result: 2002
Input: <<SUB(‘%PROPERTY_66%’,'<<MUL(‘%PROPERTY_66%’,'<<DIV(‘%PROPERTY_69%’,’100′)>>’)>>’)>>
Calculation: 1001- (1001 x (10/100))
Result: 900,9
8. Ternary Operator Expressions #
Expression
Description
<<'<text>' <operator> ' <text>'? '<text>' : '<text>'>>Calculates the value of a given ternary expression (IF-THIS-THAN-THAT operation).
Operator can be any of the following:
• !=
• >=
• <=
• >
• <
• =
Eq: Expression <<‘%PROPERTY_1156%’>=’18’?’true’:’false’>> could be used in order to automatically set IsAdult property from Age property.
Example #
| Property Name | Property ID | Property Value | Data Type |
| Priority | 1039 | High | Choose from list |
| Project | 1038 | Project Test | Choose from list |
| TaskName | 1040 | Learn Macro | Text |
| Adult | 1050 | True/False | Boolean |
| Age | 1156 | 21 | Number (integer) |
Request
If property (Project) is empty, then don’t show it
Input
<<'%PROPERTY_1038%'=''?'%PROPERTY_1040%':'%PROPERTY_1038% - %PROPERTY_1040%'>>Result: Project is empty
Learn Macro
Result: Project is not empty
Project Test – Learn Macro
Request
If property Priority is High, then show it, else don’t
Input
<<'%PROPERTY_1039%'='High'?'%PROPERTY_1039% - %PROPERTY_1040%':'%PROPERTY_1040%'>>Result: Priority is High
High – Learn Macro
Result: Priority is not High
Learn Macro
Request
If Person is over 18 years old, he is an adult, so Adult property should be true, else false
Input
<<'%PROPERTY_1156%'>='18'?'true':'false'>>Result
true
9. Text transform parser #
Expression
Description
<<TOUPPER('<text>')>>Convert given text to uppercase.
<<TOLOWER('<text>')>>Transfer given text to lowercase.
<<TOUPPERINVARIANT('text')>>Converts given text to its uppercase equivalent using the casing rules of the invariant culture.
<<TOLOWERINVARIANT('text')>>Converts given text to its lowercase equivalent using the casing rules of the invariant culture.
<<CONCATENATE(’<text1>’,’<text2>’)>>Merge two or more text strings into one.
<<SUBSTRING('<text>','start','length')>>Extract characters from a string.
Example #
| Property Name | Property ID | Property Value | Data Type |
| Name | 110 | John | Text |
Input: <<TOUPPER(%PROPERTY_110%)>>
Result: JOHN
10. Translation Expressions #
Expression
Description
<<TRANSLATE('<text>')>>Translates the given input based on language configured in the configuration panel. Language codes are defined by ISO standard: https://en.wikipedia.org/wiki/List_of_ISO_639-1_codes
Example #
| Language configured in configuration is German: DE | |||
| Property Name | Property ID | Property Value | Data Type |
| Class | 100 | Contract | Choose from list |
Input: <<TRANSLATE(%PROPERTY_100%)>>
Result: Vertag
11. Regex parse #
Expression
Description
<<REGEX('<input>','pattern','idx'>>Extract value using the given regex pattern.
IDX should be a positive integer, representing zero based index of matched result group to return.
12. File metadata parse #
Expression
Description
<<FILE('filename wildcard')>>Extract only file name without the extension from file name.
<<EXTENSION('filename wildcard')>>Extract file extension from file name.
<<FILENAMEWITHEXTENSION('filename wildcard')>>Extract the whole file name.
Example: <<FILE(‘filename wildcard’)>> #
Property: testfile.pdf
Result: testfile
Example: <<EXTENSION(‘filename wildcard’)>> #
Property: testfile.pdf
Result: pdf
Example: <<FILENAMEWITHEXTENSION(‘filename wildcard’)>> #
Property: testfile.pdf
Result: testfile.pdf
13. Combining multiple Macros #
You can combine multiple macro expressions, by following the rules and adding macro instead of property. For example
<<DATEDIFF('<<NOW>>','%PROPERTY_50%','DAYS')>><<SUB('<<MONTH(%PROPERTY_50%)>>','<<MONTH(<<TODAY>>)>>'>>Configuring Auto properties #
After you have successfully installed the application, you can start adding calculation rules in application configuration. Each property that is configured will be converted to auto calculated property. There can only exist one active (enabled) rule per single property. If multiple rules are configured for a single property, validation error message will be shown and only the first configured enabled rule will be used.
- 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 Auto properties
Field Description #
| Field | Value | Description |
| Enabled | Yes/No | Specifies whether the module is in use. |
| Expression parsers > Date part parser | ||
| Enabled | Yes/No | Specifies whether the parser is in use. |
| Expression parsers > Date format parser | ||
| Enabled | Yes/No | Specifies whether the parser is in use. |
| Expression parsers > Date diff parser | ||
| Enabled | Yes/No | Specifies whether the parser is in use. |
| Expression parsers > Date add parser | ||
| Enabled | Yes/No | Specifies whether the parser is in use. |
| Expression parsers > Filter lookups parser | ||
| Enabled | Yes/No | Specifies whether the parser is in use. |
| Expression parsers > Format lookups parser | ||
| Enabled | Yes/No | Specifies whether the parser is in use. |
| Expression parsers > Math parser | ||
| Enabled | Yes/No | Specifies whether the parser is in use. |
| Expression parsers > Ternary operator parser | ||
| Enabled | Yes/No | Specifies whether the parser is in use. |
| Expression parsers > Text transform parser | ||
| Enabled | Yes/No | Specifies whether the parser is in use. |
| Expression parsers > Regex parser | ||
| Enabled | Yes/No | Specifies whether the parser is in use. |
| Expression parsers > Translation parser | ||
| Enabled | Yes/No | Specifies whether the parser is in use. |
| Translation language | Text | Specifies language to translate into. |
| Expression parsers > Translation parser > Translation service | ||
| Type | MicrosoftTranslator | Specifies type of translation service. |
| Security Key | Text | Optional security key for translation service. |
| Expression parsers > Translation parser > Translations storage | ||
| Type | One of:NoneSQL Server | Optional storage for translations. Currently, only SQL Server database can be used for this purpose. Storing translations fetched from translation service to permanent storage has two advantages: • Caching. No subsequent requests to translation service need to be made for inputs that already exist in storage. • Modification. Stored translations can be modified as appropriate. |
| Connection string | Text | Connection string to the SQL Server database that will be used to store translations. |
| Table | Text | Name of database table that will be used to store translations. |
| Rule groups > Group [n] | ||
| Name | Text | Specifies rule group name. |
| Rule group > Group [n] > Rules [n] > Rule | ||
| Name | Text | Specifies rule group 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. |
| Property | Property | Property whose value will be calculated. |
| Recalculate properties | Yes/No | If it set to yes, it will recalculate chosen properties when clicked Recalculate on dashboard. |
| Property value | Text | Calculated property value expression. |
| Recalculate daily | Yes/No | If yes property will be recalculated between midnight and 1 am. |
| Business critical | Yes/No | If set to true, rule will be logged in SEQ. |
| Use calendar in Date functions | Yes/No | Exclude non working days only. Works only for <> and <> macros. |
| Calendar setting source | One of the following: • FromRule • FromEnviroment | From Rule. Rule will use calendar specified on source. FromEnvironment. Rule will use calendar specified in Environment section of Extension Kit. |
| Calendar alias | Text | Displayed only if From Environment is selected as Calendar setting source. Users must specify calendar alias from environment. |
| Rule group > Group [n] > Rules [n] > Rule > Calendar enviroment>NonWorkingDay[n] | ||
| Occurrence | One of the following: • Weekly • Monthly • Yearly • Once • Holiday | Specifies when the non-working days will be calculated weekly, monthly, yearly, once, or holiday. |
| Rule group > Group [n] > Rules [n] > Rule > Calendar environment>NonWorkingDay[n]>Weekly>Date | ||
| Weekday | One of the following: • Monday • Tuesday • Wednesday • Thursday • Friday • Saturday • Sunday | Specifies which day in the week. |
| Rule group > Group [n] > Rules [n] > Rule > Calendar environment>NonWorkingDay[n]>Monthly>Date | One of the following: • Static • From Vault | Static. Choose specific date for non working day. From vault. Choose date property for non-working day |
| Type | One of the following: • Static • From Vault | Static. Choose specific date for non working day. From vault. Choose date property for non-working day. |
| Type | One of the following: • SpecificDate • Specific Weekday | |
| Day of month | Number | |
| Rule group > Group [n] > Rules [n] > Rule > Calendar environment>NonWorkingDay[n]>Monthly | ||
| Week of month | One of the following: • First • Second • Third • Fourth • Fifth • Last | Which week of the month is used. |
| Day of week | One of the following: • Sunday • Monday • Tuesday • Wednesday • Thursday • Friday • Saturday | Which day of the week is used. |
| Date property | Property definition | Date property on Date object in vault. |
| Rule group > Group [n] > Rules [n] > Rule > Calendar environment>NonWorkingDay[n]>Yearly | ||
| Type | One of the following: • Static • From vault | Static. Choose specific date for non working day. From vault. Choose date property for non-working day. |
| Month | One of the following: January, February, March, April, May, June, July, August, September, October, November, December | |
| Day of the week | One of the following: • Sunday • Monday • Tuesday • Wednesday • Thursday • Friday • Saturday | |
| Search condition | Search condition | Search condition to use for finding date property on Date object in vault. |
| Date property | Property definition | Date property on Date object in vault. |
| Rule group > Group [n] > Rules [n] > Rule > Calendar environment>NonWorkingDay[n]>Once | ||
| Type | One of the following: • Static • FromVault | |
| Date | Date | |
| Search condition | Search condition | Search condition to use for finding date property on Date object in vault. |
| Date property | Property definition | Date property on Date object in vault. |
| Rule group > Group [n] > Rules [n] > Rule > Calendar environment>NonWorkingDay[n]>Holiday>Date[n] | ||
| Country | One of the following: • GB • HR • SI • PL | Select country for national holidays. |
| Use all holidays | Yes/No | Use all national holidays as non-working days or not. |
| Rule group > Group [n] > Rules [n] > Rule > Calendar environment>NonWorkingDay[n]>Holiday>Date[n]>Use holidays [n] | ||
| Holiday[n] | List of holidays. | List of holiday specific for country which user choose in Country section. |
| Rule group > Group [n] > Rules [n] > Rule > Filter conditions | ||
| SearchConditions | Search Conditions | Displayed only if [Advanced] is selected as a Configuration Mode. Optional additional conditions that object needs to satisfy. Conditions inside a single filter are combined with AND, while multiple filters are combined with OR operator. |
| Rule group > Group [n] > Rules [n] > Rule > Filter conditions | ||
| StopConditions | Stop Conditions | Displayed only if [Advanced] is selected as a Configuration Mode. Optional stop condition. When object matches stop conditions, property is no longer calculated. Multiple stop conditions are combined with OR operator. |
| Rule group > Group [n] > Rules [n] > Rule > Default value | ||
| Mode | One of the following: • No action • Static value • Empty value | Displayed only if [Advanced] is selected as a Configuration Mode. Optional default value for property. |
| Static value | Value | Display only if [Static value] is selected as a Default value mode. Specifies constant expected property value. |
| Rule group > Group [n] > Rules [n] > Rule > Advanced settings | ||
| Expression type | One of the following: • Complex • Simple • None | Property referencing type can be used to further control expression capabilities and expansion. Complex. Expression uses advanced functionality of placeholder editor. Simple. Improve performance when using only top-level properties and ObjID or ObjVer keywords in expression. None. Further improve performance if not using any properties or placeholder in expression |
| Time to wait (milliseconds) | Integer | Number of milliseconds to wait before executing any rule in background. |
| Number of retries | Integer | Maximum numbers of times COM errors are retried. |
| Retry Interval | Integer | Number of milliseconds to wait between retries. |
Use Cases #
Calculate how many days till contract expire #
Customer is using M-Files for contracts and document management. He would like to know how many days until the expiration of the contract (Picture 1). Customer has used “DATEDIFF” macro in Macros.
In Picture 2, we can see that Property „Expiring (days)“ has been populated.
Configuring Auto properties #
Track working days and non-working days when employee takes vacation #
Customer wants to track all working days in vacation period of employees.
We created Object type = Vacation in our M-Files Vault and added class Vacation Application. We used it for logging the days someone is out of office for more than one day.
Also we created a property Working days in which we want to calculate working days between start and end day of vacation. In addition, you can add what days are non-working, for precise calculation.
Also, you can define that the system stops calculating holidays as working days, or simply choose the holiday that you don’t want to calculate.
Picture 5. Exclude holidays
