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

David Meego - Click for blog homepageThis is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.

An 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, http://opensource.org/licenses/ms-pl.html.)

This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.

5 thoughts on “Quick Tip: Using substring() with a text field datatype

  1. This also helped me with some code to strip out CR and other symbols from a text field.  We take data from some text fields and pass it on to external systems that didn't like CRs in their data.  This example helped me write some code to cleanup the data on the save.  Till I found this post, I didn't realize that replace() also returned/worked with 255 character chunks.
    inout text inout_text;
    local text l_text;
    local string l_string;
    local integer l_pos;
    local integer l_length;
    if pos(inout_text,char(13),1) <> 0 or pos(inout_text,char(64),1) <> 0 then
    l_length = length(inout_text);                        
    l_pos = 1;
    clear l_text;
    while l_pos < l_length do
    l_string = substring(inout_text, l_pos, min(l_pos + 255, l_length+1) – l_pos);
    if pos(l_string,char(13),1) <> 0 or pos(l_string,char(64),1) <> 0then
    {cleanup CR}
    while pos(l_string,char(13),1) <> 0 do
    l_string = replace(l_string," ",pos(l_string,char(13),1),1);
    end while;
    {cleanup Copyright}
    while pos(l_string,char(64),1) <> 0 do
    l_string = replace(l_string," ",pos(l_string,char(64),1),1);
    end while;
    l_text = l_text + l_string;
        l_pos = min(l_pos + 255, l_length+1);
    else
    l_text = l_text + l_string;
    end if;
    end while;
    inout_text = l_text;
    end if;

    Like

Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.