Advanced Variables

Variable

Action or Value

Example

[SQLGET(Connection|Username|Password|key|

Command|sqlparam|...)]

Executes a command (query) using the connection, username and password parameters to connect to the SQL server. If any data is returned it is brought back as a JSON object in string format. You can use the OBJPROP function below to get data out of the objects.

connection: Valid SQL connection string. This may contain user id and password as well.

username: The user that is being used to authenticate to the SQL server. If omitted then the connection string can supply the user name. If this is filled in then it will override the connection string user.

password: The password of the user that is being used to authenticate to the SQL server. If omitted then the connection string can supply the password. If this is filled in then it will override the connection string password.

key: In order to allow SQLGET to run on your server, this key value must match the server's SQLGetKey setting. If not then the SQLGET call will be aborted. See the examples for more information.

command: the actual query or command to run. All variables must be contained in the query as there is no method to add SQL command parameters.

sqlparam: A name and value pair separated by an equal sign (=), The left side of the = is the parameter name and the right side is the parameter value. These will be added to the SQL command parameter list before executing the SQL command. These are repeatable so you can add as many of these as you need in your call. Be sure you have added the parameter name to the command string or they will be ignored. See the third example for guidance.

[SQLGET(server=(local);database=xyz;uid=dmUser;pwd=xyz;|||YourKeyHere|select FirstName, LastName from Users where ID=15)]

[SQLGET(server=(local);xyz=dm;uid=dmUser;pwd=xyz;|||YourKeyHere|select FirstName, LastName from Users where ID=[UserID])]

[SQLGET(server=(local);xyz=dm;uid=dmUser;pwd=xyz;|||YourKeyHere|select FirstName, LastName from Users where ID=@ID|ID=[UserID])]

Notes:

1) Be careful that the username/password you use only has rights to perform what you wish them to perform. This will help mitigate your risks of abuse if you use variables. If you construct your SQL queries without parameters you must either NOT use variables in your string or lock down your access to not allow anything malicious. See this article on SQL injection for some good information.

2) SQLGET is only available on premise-based installations. It is not available for cloud systems.

3) You must enable SQLGET by adding a new AppSetting entry to your web.config file. This entry must have a secret key that is used to allow users to execute SQLGET on that server via the key parameter.

The "YourKeyHere" value needs to be replaced with your secret key. This comparison is case sensitive to make sure you use the proper casing.

If you change your key value then you must also update all your SQLGET calls or they will fail.

[RESTGET(URL|Username|Password|reserved|

isXml)]

Calls a REST GET endpoint and returns the data as a JSON object in string format. You can use the OBJPROP function below to get data out of the objects.

URL: The URL of the REST endpoint. Only GET commands are supported at this time so all your data must be in the URL.

username: The user that is being used to authenticate to the REST service. You can omit this and password if the REST service is open and does not require authentication.

password: The password of the user that is being used to authenticate to the REST service. You can omit this and username if the REST service is open and does not require authentication.

reserved - Reserved option. Do not use.

IsXML - true/false - Specifies that the return value from the REST call will be XML. The system will then attempt to convert the XML into JSON so the return from this call is still a JSON format.

Open REST calls (without authentication):

[RESTGET(http://api.tvmaze.com/search/shows?q=the+golden+girls)]

[RESTGET(http://echo.jsontest.com/return/me)]

REST calls with authentication:

[RESTGET(https://beta.xyz.com/rest/records/search?searchString=test&pageNum=1&numPerPage=1|admin|admin)]

REST calls with XML returned:

[RESTGET(https://beta.xyz.com/rest/records/search?searchString=test&pageNum=1&numPerPage=1||||true)]

[DMGET(RecordTypeID|MaxRecords|var=val|

var=val=valto|...|FieldName|LI:FieldName)]

Runs a search on the local DocMgt server to retrieve Records. The records will be JSON encoded.

RecordTypeID: The ID number of the Record Type to search. If blank or global then a global search is performed.

MaxRecords: The maximum number of records to return. If blank this will be set to 25. Keep this as low as possible for best results.

var=val: Field name and value combination to search for. Can be repeated as many times as necessary. Can be interchanged with var=val=valto as needed.

var=val=valto: Field name and value range to search for. Can be repeated as many times as necessary. Can be interchanged with var=val as needed.

* Note: Add as many var=val and/or var=val=valto entries as needed to limit the search down as much as possible.

FieldName: Name of the Field to return in the collection. You can add as many as you like using the pipe delimiters. If none specified then all fields are returned.

LI:FieldName: Name of the Line Item Field to return in the collection. You can add as many as you like using the pipe delimiters. If none specified then all line item fields are returned.

* Note: If you don't want any Line Item fields returned use LI:x or some other field name that doesn't exist.

Search record type # 1 for a maximum of 2 records. Limit search to only those records where the VendorName field is equal to ACME.

[DMGET(1|2|VendorName=ACME)]

Search record type # 4 for a maximum of 3 records. Limit search to only those records where the Amount field is from 0 to 200.

[DMGET(4|3|Amount=0=200)]

Search record type # 4 for a maximum of 3 records. Limit search to only those records where the Amount field is from 0 to 200. Return only the Vendor, InvoiceDate and Amount fields and no line item fields.

[DMGET2(RecordTypeID|MaxRecords|

var=val^var=val=valto|Field1^Field2|

Custom1^Custom2|SortOrder|MultiAsArray)]

[DMGETIN(RecordTypeID|MaxRecords|

var=val^var=val=valto|Field1^Field2|

Custom1^Custom2|SortOrder|MultiAsArray)]

DMGET2 - Runs a search on the local DocMgt server to retrieve Records. The records will be JSON encoded.

DMGETIN - Returns field values from the resulting records using the -IN- separator for use in searches.

----

RecordTypeID: The ID number of the Record Type to search. If blank or 0 then a global search is performed.

MaxRecords: The maximum number of records to return. If blank this will be set to 25. Keep this as low as possible for best results.

var=val / var=val=valto: Field name and value combination to search for. Can be repeated as many times as necessary using ^ as a separator per field. valto is used for range searching only. If you are doing an exact search leave this out. You can use RecordID as a field name with a numeric value to search a specific Record.

Field1^Field2: Names of the Fields to return in the collection. You can add as many as you like using ^ delimiters. If none specified then all fields are returned. Can be repeated as many times as necessary using ^ as a separator per field.

Custom1^Custom2: Custom fields that can be returned based on variables. You must use << and >> instead of [ and ] for the variables to be sure they are interpreted at the proper time. For instance, to return a field named ALL that combines Name and Address fields you could specify "ALL=<<Name>> - <<Address>>" here. Can be repeated as many times as necessary using ^ as a separator per field.

SortOrder: The field by which to order your query to ensure Records are returned in the order required.

MultiAsArray: If true and you are returning values from a field that is marked as allowing multiple then we return them as an array of values. If False then the values are returned as a string delimited by the ^ character. Default is true. Not used for DMGETIN.

InField - the name of the field to use for the -IN- string. Using DMGETIN is a shortcut to using [JOIN([DMGET2(xxxxxxx)])] variables.

DMGET2 Examples

Search record type # 1 for a maximum of 2 records. Limit search to only those records where the VendorName field is equal to ACME.

[DMGET2(1|2|VendorName=ACME)]

Search record type # 4 for a maximum of 3 records. Limit search to only those records where the Amount field is from 0 to 200.

[DMGET2(4|3|Amount=0=200)]

Search record type # 4 for a maximum of 3 records. Limit search to only those records where the VendorName field is equal to ACME and Amount field is from 0 to 200. Return only the Vendor, InvoiceDate, Amount and Paid fields. Add an additional custom field named Remaining that calculates the Amount of the invoice minus the Paid amount.

[DMGET2(4|3|VendorName=ACME^Amount=0=200|Vendor^InvoiceDate^Amount^Paid|Remaining=<<math(<<Amount>>-<<Paid>>)>>)]

DMGETIN Example

Search all Vendor records (ID = 12) and return the VendorID field values formatted using -IN- logic.

[DMGETIN(12|0||VendorID||||VendorID)]

returns:

ACM123 -IN- TON545 -IN- OTH766

* You can use this in a search field to find all Records where that field contains any of the values returned. In database terminology, this is similar to a JOIN searching operation.

[DMGETLI(RecordTypeID|MaxRecords|

var=val^var=val=valto|

livar=val^livar=val=valto|Field1^Field2|

LIField1^LIField2|SortOrder|MultiAsArray)]

DMGETLI - Runs a search on the local DocMgt server to retrieve Records and their line items in JSON format.

----

RecordTypeID: The ID number of the Record Type to search. If blank or 0 then a global search is performed.

MaxRecords: The maximum number of records to return. If blank this will be set to 25. Keep this as low as possible for best results.

livar=val / livar=val=valto: Line Item Field name and value combination to search for. Can be repeated as many times as necessary using ^ as a separator per field. valto is used for range searching only. If you are doing an exact search leave this out.

LIField1^LIField2: Names of the Line Item Fields to return in the collection. You can add as many as you like using ^ delimiters. If none specified then NO line item fields are returned. Can be repeated as many times as necessary using ^ as a separator per field.

SortOrder: The field by which to order your query to ensure Records are returned in the order required.

Example: Return invoice details for all invoices from January 2024 including the Vendor, Invoice Date and Amount from the header info and GL Code, Price, and Total from the line item info. Sort the results by Invoice Date.

[DMGETLI(1|0|InvoiceDate=1/1/2024=1/31/2024||Vendor^InvoiceDate^Amount|GLCode^Price^Total|InvoiceDate)]

[DMGETCOUNT(RecordTypeID|

var=val^var=val=valto)]

Counts the number of Records matching criteria.

RecordTypeID: The ID number of the Record Type to search. If blank or 0 then a global search is performed.

[DMGETCOUNT(1|VendorName=ACME) = 10 (# Records)

[DMGETOPTIONS(RecordTypeID|MaxRecords|

var=val^var=val=valto|ValueField|

DisplayField|SortOrder|Unique|FilterField|

AmountField|DocumentID)]

Runs a search on the local DocMgt server to retrieve Records. The records will be formatted to fill Radio, Option and Multi-Option lists in E-form.

RecordTypeID: The ID number of the Record Type to search. If blank or 0 then a global search is performed.

MaxRecords: The maximum number of records to return. If blank this will be set to 25. Keep this as low as possible for best results.

ValueField: Name of the Field That is to be used for the VALUE setting in the field options.

DisplayField: Name of the Field That is to be used for the DISPLAY setting in the field options.

SortOrder: The field by which to order your query to ensure Records are returned in the order required.

Unique: Whether to return items that are unique. It there are duplicates then only the first item of the duplicates will be returned. This is based on the Value Field only.

FilterField: Name of the Field That is to be used for the FILTER setting in the field options.

AmountField: Name of the Field That is to be used for the AMOUNT setting in the field options.

DocumentID: ID of a CSV document to use for the data source. You can use this as a more efficient method for populating options. NOTE: this method is more simplistic when using var=val pairs. It does not support range searches and -OR- searches.

Returns a list of the first 2 vendors using the ID as the value and VendorName as display. The list is sorted by VendorName.

-----

DMGETOPTIONS(1|0||InvoiceNum||VendorName|||VendorID)]

Returns a list of ALL invoices using the InvoiceNum as the value, InvoiceNum as display and VendorID as the Filter value. This is useful for creating cascading option lists where list 1 would be the Vendor and list 2 would be their invoices. Just set list 2's 'Filter Options By' setting to list 1.

[DMGETCHARTDATA(RecordTypeID|MaxRecords|

var=val^var=val=valto|LabelField|ValueField|

SortOrder|Unique|GroupingField|reserved|

DocumentID)]

Runs a search on the local DocMgt server to retrieve Records. The records will be formatted for display in a Chart field in E-forms.

RecordTypeID: The ID number of the Record Type to search. If blank or 0 then a global search is performed.

MaxRecords: The maximum number of records to return. If blank this will be set to 25. Keep this as low as possible for best results.

LabelField: Name of the field That holds the LABEL value for the chart data.

ValueField: Name of the field That holds the VALUE to be charted.

SortOrder: The field by which to order your query to ensure Records are returned in the order required.

Unique: Whether to return items that are unique. It there are duplicates then only the first item of the duplicates will be returned. This is based on the Value Field only.

GroupingField: Name of the Field That is to be used for grouping the charted data.

Reserved: This will be ignored.

[DMGETCHARTDATA(1|2|VendorName=ACME|InvoiceDate|Amount|||VendorName)]

Returns a list of the first 2 invoices using the Invoiced Date for the label, AMount to be charted and grouped by Vendor Name.

[DMGETPROP(RecordTypeID|

var=val^var=val=valto|Field^Field|Delim)]

Runs a search on the local DocMgt server to retrieve a specific property (field value) from the first Record that matches.

RecordTypeID: The ID number of the Record Type to search. If blank or 0 then a global search is performed.

Field: The field whose value to return. If you have multiple you can split them with the ^ character. The return will be a string with all values in all fields separated by the Delim value below.

Delim: The character to use to delimit the returned values. If you are only returning one field then you will get that value back. If you specify more than one field to return the values will be separated by the character you set here. If you leave it blank then the delimiter will be ^.

Search record type # 1 where the VendorName field is equal to ACME and return the value from the Email field.

[DMGETPROP(1|VendorName=ACME|Email)]

Search record type # 1 where the VendorName field is equal to ACME and return the Email and Address values separated by a hyphen.

[DMGETPROP(1|VendorName=ACME|Email^Address|-)]

[PROP(JSON|el#|property|...)]

Traverses a string-based JSON object to retrieve the requested property.

JSON: This is the actual JSON object. Typically you would refer to a variable here but for samples we use static objects as well.

el#: For JSON arrays you need to use an array index to determine which element number to return. This is an integer value that is zero-based.

property: The name of the property to return.

Use el# and property as many times as you need to get to the property you want. For example, if you had a User object that had an array of teams as a property and you want to get the name of the 2nd team in the list it might look like this:

[PROP({id: 1, teams: [{id:5, name:"This Team"},{id: 6, name: "That Team"}]}|teams|1|name)]

which would return the value "That Team".

The hardest thing to do is to visualize the JSON data to be able to extract the element number and properties in the proper way to code them into the call. Here are a couple of third party utilities that allow you to paste in example JSON code and see the data in a structured way:

https://jsonformatter.curiousconcept.com/

https://jsoneditoronline.org/

* These are 3rd party links and are in no way affiliated with DocMgt.

[PROP({id: 1, name: "This User"}|id)] = 1

[PROP({id: 1, name: "This User"}|name)] = "This User"

[PROP({id: 1, name: "This User", teams: ["Team One","Team Two"]}|0)] = "Team1 One"

[PROP({id: 1, teams: [{id:5, name:"This Team"},{id: 6, name: "That Team"}]}|teams|1|name)] = "That Team"

[PROP([RESTVALUE]|teams|1|name)] = "That Team"

(if RESTVALUE is a variable holding the above JSON data)

[PROP([RESTGET(http://api.tvmaze.com/search/shows?q=the+golden+girls)]|0|show|name)] = "The Golden Girls"

[JSONWHERE(JSONArray|prop|val)]

Returns subset of JSON array by matching property values. Only checks top level JSON object's properties.

JSONArray - Array of JSON items.

prop - Property to check for match

val - Value to match on. This supports using *. For example: *smith

This is useful with COUNT and other variables to get a count of the items in the array where a certain property matches a value.

You can combine multiple property comparisons by making successive JSONWHERE calls.

[JSONWHERE([JSONWHERE([arr]|id|1)]|name|smith)] will return all items where id=1 and name=smith.

Assuming this array named [arr]:

[{"id": 1, "name":"smith"},{"id": 2, "name":"jones"}]

[JSONWHERE([arr]|name|smith)] = [{"id": 1, "name":"smith"}]

[JSONWHERE([arr]|id|1)] = [{"id": 1, "name":"smith"}]

[JSONWHERE([arr]|name|*s*)] = [{"id": 1, "name":"smith"},{"id": 2, "name":"jones"}]

[JSONSORT(JSONArray|prop|numeric|desc)]

Sorts a JSON array by the specified property values. Only checks top level JSON object's properties.

JSONArray - Array of JSON items.

prop - Property to sort by.

numeric - Boolean of whether to sort the values using numeric sorting.

true - sort as numbers

false - sort as strings (default)

desc - Boolean of whether to sort the values descending (largest first).

true - sort descending

false - sort ascending (default)

Assuming this array named [arr]:

[{"id": 1, "name":"smith"},{"id": 2, "name":"jones"}]

[JSONSORT([arr]|name)] =

[{"id": 2, "name":"jones"},{"id": 2, "name":"smith"}]

[JSONSORT([arr]|name||true)] =

[{"id": 1, "name":"smith"},{"id": 2, "name":"jones"}]

[JSONSORT([arr]|id|true)] =

[{"id": 1, "name":"smith"},{"id": 2, "name":"jones"}]

[JSONSORT([arr]|id|true|true)] =

[{"id": 2, "name":"jones"},{"id": 2, "name":"smith"}]

[JSONRECORD(id|fields|lifields)]

Returns a JSON object of the current or specified Record.

id = ID of the Record for which to return JSON. Leave blank to use the active Record.

fields = The names of the header fields to return. Separate multiple field names by ^.

lifields = The names of the line item fields to return. Separate multiple field names by ^.

[RECORD(|InvoiceNum^Amount|Cost^Description^Qty]

[OCRDATA(DocID|Page|X|Y|W|H|Pattern|

MatchNum|word|type)]

[BARCODEDATA(DocID|Page|X|Y|W|H|Pattern|

MatchNum|word|type)]

Looks up OCR or Barcode data for the given document/page in the given region (X, Y, W, H). If a REGEX pattern is provided then the specified match number is returned.

DocID = ID of document in which to look for values

Page = Page number in the document to look

X = The Left coordinate of the region in which to look for values. *

Y = The Top coordinate of the region in which to look for values. *

W = The Width of the region in which to look for values. *

H = The Height of the region in which to look for values. *

Pattern = The REGEX pattern to use to look for a match.

IMPORTANT: Be sure to wrap the REGEX in ignore codes - [~ ~] - so it is not replaced as a variable. For instance [~[a-zA-Z]~] will specify a REGEX pattern that will only return alpha characters.

MatchNum = The REGEX match number to return. If more than one set of characters in the region matches your pattern then you can specify which one you want. MatchNum of 0 means all matches, 1 = first match and so on.

word = Return values word by word instead of line by line

type = return only words of the specified type

- 0 = ALL

- 1 = Number

- 2 = Date

* To look in the entire page, leave thee value blank

[OCRDATA(123|1|1560|175|80|35|\d+)] - Look in the OCR data for doc id 123 / page 1 / region 1560,175,80,35 and return only numbers.

[BARCODEDATA(123|1|1560|175|80|35|\d+)] - Look in the BARCODE data for doc id 123 / page 1 / region 1560,175,80,35 and return only numbers.

[OCRDATA(123|1)] - Return all OCR data for doc id 123 / page 1.

[COORDS(DocID|Page|Pattern|MatchNum|X|Y|W|

H)]

W|H)]

[BARCODECOORDS(DocID|Page|Pattern|MatchNum|

X|Y|W|H)]

Returns the coordinates on the page that match the text supplied. Returns 6 comma-delimited values of left, top, width, height, right, bottom.

COORDS looks for both OCR/Barcode values

OCRCOORDS only looks for OCR values

BARCODECOORDS only looks for Barcode values

DocID = ID of document in which to look for values

Page = Page number of the document to look in

Pattern = The text to find or the REGEX pattern to use to look for a match.

IMPORTANT: If you use a REGEX pattern, be sure to wrap the REGEX in ignore codes - [~ ~] - so it is not replaced as a variable. For instance [~[a-zA-Z]~] will specify a REGEX pattern that will only return alpha characters.

MatchNum = The REGEX match number to return. Leave this blank if you are just doing a standard text match. If more than one set of characters in the region matches your pattern then you can specify which one you want. MatchNum of 0 means all matches, 1 = first match and so on. Default = 0.

X = The Left coordinate of the region in which to look for values. *

Y = The Top coordinate of the region in which to look for values. *

W = The Width of the region in which to look for values. *

H = The Height of the region in which to look for values. *

* To look in the entire page, leave these values blank

Text match:

[COORDS(123|1|Please Sign Here)] = 10,20,30,40,40,60

REGEX match:

[COORDS(123|1|This (is a test)|1)] = 10,20,30,40,40,60

[OCRTABLE(DocID|Page|X|Y|W|H|

RowPattern|IncludeeRowMarker|Col^Col)]

Extracts table data from a page's OCR data based a defined row delimiter and column pixel offsets.

DocID = ID of document in which to look for values

Page = Page number of the document to look in

X = The Left coordinate of the region in which to look for values. *

Y = The Top coordinate of the region in which to look for values. *

W = The Width of the region in which to look for values. *

H = The Height of the region in which to look for values. *

* To look in the entire page, leave these values blank

RowPattern = The text to find (including wildcards) or the REGEX pattern to use to find a row delimiter. Rows can be multiple lines and this gives yo a way to specify when a new row starts.

IncludeRowMarker - A true/false value specifying whether or not to include the row marker as a line in the returned table.

Col^Col - A ^ delimited list of starting pixel offsets for each column. Add as many as you need for all your columns. Be sure to separate each with a ^ character.

[OCRTABLE(123|1|||||Line*|false|1^100^125^300^550)]

[UNIQUEDATA(rtid|field|del)]

Returns the unique values from a field in a Record Type

rtid - ID of the Record Type to check

field - The field whose values you wish to return

del - The delimiter to place between each value. The default value is ; (semicolon)

Use this with the SPLIT variable to loop through each value

[UNIQUEDATA(1|VendorName)] - ACME;Test Co;RonCo

[UNIQUEDATA(1|VendorName|^)] - ACME^Test Co^RonCo

[MATCHLINES(lines|match)]

Returns the lines from a multi-line string that match a specific pattern or text string.

lines - The lines in which to search for matches

match - The text or pattern to use for matching. Any liens that match this will be returned.

match can be a simple text match, pattern match or a Regex match.

Text Match - Any text string

Pattern Match - You can use wildcards of * or ? to look for matches. * represents any number of any charafter where ? represents any single character.

Regex - Use a value that starts with REGEX: and anything after that is the Regex pattern to match. If you use brackets [ or ] in your pattern then be sure to enclose the pattern in [~ and ~] so the brackets are not interpreted as variables.

This is sometimes used with [DOCTEXT()] variables to look for lines in a text document.

[MATCHLINES([Doctext(123|1)]|*signature*)] - Returns the lines in Document ID 123, page 1 that contain "signature" in them.

[MATCHLINES([PageText]|REGEX:\$\d{1,10}\.\d{1,10})] - Returns the lines in the 'PageText' field that contain dollar amounts.