Quick Tip: Using substring() with a text field datatype

  • Article

David Meego - Click for blog homepageAn interesting tip today. Over the last few months there has been a thread on the Partner Forum (GP Notes Lockdown) with the request to prevent changes to the existing text on the Record Notes feature of Microsoft Dynamics GP. 

The functionality request has two parts:

  1. Prevent existing notes from being deleted.
  2. Prevent existing text in the notes from being changed, only additions are allowed.

MVP Sivakumar Venkataraman took on the challenge and set about answering the forum post with a progression of solutions he published on his blog.

The final post used the Support Debugging Tool to create 3 non logging triggers for each of the 5 Notes forms to provide the functionality desired (15 triggers in total).

The customer started using this approach but then noticed that the code did not behave correctly when the text in the note field was long. The problem was the code below, which was used to compare the text in the window field (for the length of the text saved in the table) against the text saved in the table.  The idea is to detect if existing text has been changed.

Initial Code Example

if substring('Text Field' of window 'Form_Note_1' of form 'Form_Note_1', 1, length('Text Field' of table SY_Record_Notes_MSTR)) <>
'Text Field' of table SY_Record_Notes_MSTR then
warning "Fields don't match";
end if;

 

The problem is that the substring() function returns a string with a maximum of 255 characters.  So even though the requested length was larger than 255, only 255 would be returned. This meant that once the text in the note had exceeded 255 characters, it would always fail the comparison.

The solution is to write some code to build up a local text variable to the desired size using substring() to grab sections of 255 characters at a time. The final substring() will be whatever size is needed to reach the total length desired.

Updated Code Example

local text l_text;
local integer l_pos;
local integer l_length

l_length = length('Text Field' of table SY_Record_Notes_MSTR);
l_pos = 1;
clear l_text;
while l_pos < length(l_TextField) do
l_text = l_text + substring('Text Field' of window 'Form_Note_1' of form 'Form_Note_1', l_pos, min(l_pos + 255, l_length+1) - l_pos);
l_pos = min(l_pos + 255, l_length+1);
end while;

if l_text <> 'Text Field' of table SY_Record_Notes_MSTR then
warning "Fields don't match";
end if;

 

You can see we used the min() function to get the length for the final section correct. 

Note: Siva's blog post has been updated to use this solution.

I hope this code is handy if you ever need to use substring() on a text field. 

David

// Copyright © Microsoft Corporation. All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, https://opensource.org/licenses/ms-pl.html.)