Formatting data displayed on the form

Facebooktwitterpinterestlinkedinmail
Applies to version 8.3.x; Author: Szymon Patacz

Data on the SharePoint form should be presented in a way that is clear and understandable. This is especially important when dealing with long strings of alphanumerical characters, like Bank Account Numbers.

In Poland, account numbers are made up of 26 digits. To make it more pleasant to the eye, the block of digits is spaced out according to the following format:

XX  XXXX  XXXX  XXXX  XXXX  XXXX  XXXX

In this short walkthrough, I will present several ways to display data in this format on the workflow form.

1. Data obtained from an external source.

The field containing the Bank Account Number can be set to fill out automatically when the user selects a Client name from a choice field. A data source will usually contain the account number with no spaces between digits. If we are using an SQL data source we can simply modify the query that returns data – just replace the column with the account number the with the following:

(STUFF(STUFF(STUFF(STUFF(STUFF(STUFF(replace(NRB, ' ', ''),3, 0, ' '),8,0,' '),13,0,' '),18,0,' '),23,0,' '),28,0,' ')) as NRB

1

Fig.1. Sample configuration of a picker field that fills in the account number

In the above example, the data in the column named NRB is made up of 26 digits. This query will return those digits in a more understandable format with spaces.

2

Fig.2. A view of the form

2. Data entered manually

In this example, the Bank Account Number is entered manually by a user. The data isn’t always entered with spaces however. In order to unify how the data is displayed, we will use a Change field value action which will trigger on path usage (when the element is moved to the next step).

3

Fig.3. Action configuration

The first thing this action does, is it removes all existing spaces. Once all spaces are removed, and the form field contains exactly 26 characters, spaces are added in the correct places. If the field contains a different number of characters, its value remains unchanged.

Both methods shown so far have used the STUFF function.

Function syntax:

STUFF(text to be changed, beginning, length, inserted text)

Read more about this function here: https://msdn.microsoft.com/en-us/library/ms188043.aspx

 

3. JavaScript executed on value change

The third option is to register a JavaScript which will format the value found in a field.

This method will work regardless if the value of the form field is set using a picker, or if it is typed in manually.

In the workflow configuration, go to the ‘Behavior’ tab and add two functions:

 

function reverse(s) {

  return s.split('').reverse().join('');

}




function add_spaces(str){

str = (reverse(str)).replace(/ /g,'');

replaced = str.replace(/.{4}/g, function(value) {

  return value + ' ';

});

replaced = (reverse(replaced));

return replaced;

}

 

Additionally, we must decide when this function should be executed.

When setting this form field’s vale through a picker, we should enter the following Script to be executed on value change in the picker field’s configuration:

 

SetValue('AttText1',add_spaces(GetValue('AttText1')));

 

4

 

When entering the account number manually, we should add an identical Script to be executed on value change but this time to the actual form field containing the said account number. In order for the function to work properly, we must provide it with the correct column name – it can be found in the ‘General’ tab of the form field configuration menu.

5

Leave a Reply

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