Implementation handbook – useful pre-existing SQL functions

Facebooktwittergoogle_pluspinterestlinkedinmail
applies to version: 8.2.x, author: Kamil Nędza

Tables are not the only thing created when installing a database, there is also plenty of functions and procedures which can be freely used when working with WEBCON BPS Designer Studio. Many of them can save a good deal of time. In this article, we will cover three very convenient functions, and demonstrate their use in practical situations.

Function description:

 1) ClearWFElem(@Value nvarchar(1000)),  ClearWFElemID(@Value nvarchar(1000))

Some form fields in a database are stored in the form of “ID#Display name”. This includes form fields like “Choice field” and “Person or group” type fields. Thanks to ClearWFElem you can return the right side of the text field – the fragment after the “#” sig. Analogously, ClearWFElemID returns the left part instead.

 1_eng

Fig.1. Example of how the functions works

 

  2) IsUserInADGroup(@login nvarchar(255), @groupDisplayName nvarchar(255))

Sometimes it necessary to check if the given user belongs to some specific AD group, this where IsUserInADGroup comes in handy. Based on the user’s login (in the form of: DOMAIN\login) and the name of the AD group, this function returns “1” if the user belongs to the group, “0” otherwise. A practical use for this would be displaying certain form fields, paths or “menu button” type actions depending on the user’s affiliation with a group.

Note: This function uses CacheOrganizationStructure, a table which is updated periodically at certain hours. If a user has just been added to a certain AD group, the function will not work correctly before a synchronization.

2_ENG

Fig.2. Example of how the function works

 

 3) SplitToTable(@value nvarchar(max), @delimiter nvarchar(5))

The function: SplitToTable separates or divides the entered string of characters based on the delimiter. It returns a table where the segments of text cut up by the delimiter are placed in individual rows. This function is invaluable when working with a “Choice field” form field with the option of selecting multiple values – as the values picked by a user are stored in a single cell, divided by semicolons.

 3_eng

Fig.3. Example of how the function works

 

Practical use case:

Using the functions mentioned above, we will add a “SQL Grid” form field to our form, which will display the logins, names and affiliation with certain departments of individual users, picked from a separate “Person or group” form field.

4_eng

Fig.4. Query used in the SQL Grid’s Data source SQL/CAML query field.

 

The query only accesses values found in one other form field – a „Person or group” field named Employees. This type field uses SharePoint picker popup field by default, and can be configured to allow multiple values to be selected by marking a checkbox in the advanced configuration. The final effect looks like this:

5_eng

Fig.5. A form with a SQL Grid configured like above

 

A database obviously comes with many more functions, however I believe the ones mentioned in this article are used most often and have the widest range of applications. I recommend taking a look at the other functions to have a general idea of what tools are already available when working with WEBCON BPS Designer Studio. This way, time can be saved by employing existing solutions instead of searching for new ones.

Leave a Reply

Your email address will not be published. Required fields are marked *