Developing for Dynamics GP

by David Musgrave (Perth, WA, Australia) and the Microsoft Dynamics GP Developer Support Team (Fargo, ND, USA)

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

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

Rate This
  • Comments 5

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

  • Thanks for the awesome code snippet David!

  • Posting from Jivtesh Singh at About Dynamics, Development and Life

    www.jivtesh.com/.../everything-dynamics-gp-31.html

  • Post from Mark Polino at DynamicAccounting.net

    msdynamicsgp.blogspot.com/.../quick-tip-using-substring-with-text.html

  • Post from Mark Polino at DynamicAccounting.net

    msdynamicsgp.blogspot.com/.../quick-tip-using-substring-with-text.html

  • 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;

Page 1 of 1 (5 items)
Comments Information

PLEASE READ BEFORE POSTING

Please only post comments relating to the topic of this page.

If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.

Leave a Comment
  • Please add 3 and 2 and type the answer here:
  • Post