Advanced Variables

Variable

Action or Value

Example

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)]

OBSOLETE: This variable is considered obsolete. It will continue to work for the time being but will be removed at a later date. If you require this functionality, please contact support to find an alternative.

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)]

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 - 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.

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 - 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)

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.

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.

[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"}]

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)]

[OCRCOORDS(DocID|Page|Pattern|MatchNum|X|Y|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

[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