🔧 Slate User-Defined SQL Functions
The official documentation has a helpful page on User Defined SQL Functions. However, as the page stresses, it's only a partial list. This article will attempt to document additional functions.
Table-Valued Functions
| Function Name | Description |
dbo.getRights(id) |
Return a list of user rights, including rights inherited from roles. |
dbo.getFieldMultiTable(record, field) |
Like dbo.getFieldTable(), except that it returns multiple rows. |
dbo.getFieldExtendedMultiTable(record, field) |
Return the Extended prompt value from a field on multiple rows. |
dbo.getTranslationExportTable(key, value) |
Return exports from a translation table. Has some unknown logic for value; seems to be exact match on minimum for String-typed translations keys and >= minimum, < maximum for other types. Appears to perform try_cast() on value, as string values stop working in translation key type is changed to non-string. |
dbo.encode(data) |
URL encodes a string (nvarchar max). |
dbo.getPaymentDueTable(id, account) |
@id is uniqueidentifier, @account is varchar(256). Returns a single row with column [due]. Can be a negative, positive, or zero dollar amount. If the record has no payment due in specified account, returns 0, not NULL. |
dbo.getFormResponseTopTable(fr_id, export_key) |
Given a form response GUID and a field export key, return the top field value (aka label). If you want the prompt key (GUID, bit, etc.), use getFormResponseIndexTable(). |
dbo.getFormResponseIndexTable(fr_id, export_key) |
Given a form response GUID and a field export key, return a single field value's prompt GUID or text value. Which value? Idk. |
dbo.getFormResponseMultiTable(fr_id, export_key) |
Given a form response GUID and a field export key, return the text values of all field values. |
dbo.getMaterialsByApplication2(application, person) |
Return a table of materials similar to the CJ Document base. Appears to be deprecated. Potentially returns duplicate rows: a single transcript will show up "attached" to each application for the person. Includes decision letters per decision code. |
You cannot retrieve the SQL logic from these user-defined functions to see exactly how they work.
No Comments