Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

need to convert memo field into dynamic array of <=75 character strings 1

Status
Not open for further replies.

betachristopher

Programmer
Oct 25, 2006
54
US
hello world

i have a SQL table with a memo field containing string lengths from 1 to ~6,000 characters. the client system can only accept strings that are <=75 characters. i have searched some examples of arrays, but most people seem to be splitting by a delimiter or searching for specific information in the memo field.

i just want it to put the first 75 characters in one string, move to the next 75 characters, etc. of course i first started creating multiple variables and changing the starting point like

Code:
if length({results}) >75 then mid({results},76,75) else ""
but i got tired of that really fast. i would like something that allows growth so i don't have to define set1, set2,...set80, etc.

also, since the client system will just display these line for line as i sent them, i would like to avoid splitting them in the middle of a word. so if the 75th character is not a blank space, it should return to the previous blank space making the string <75 characters.

does anyone have any code like this already?
 
I am not convinced that this is the most efficient way of doing it, but I tackled the problem this way.

I started by taking the memo field contents and converted it to a StringVar where each line of 75 characters (or less where necessary so as not to break the line mid-word) is separated by "^^" (the choice of characters is not important, as long as it is not a combination that might occur naturally). I then used those characters as the delimiter field to base the "Split" function around. The formulas look like this:

1. Formula to insert delimiter
Code:
WhilePrintingRecords;
Local NumberVar i;
Local NumberVar L := RoundUp(Length({Table.Memo_Field})/75);    // Number of 75 character strings
Local NumberVar S := 1;                                         // Set Start Position 
Local NumberVar E := 75;                                        // Set End Position
Global StringVar N := '';                                       // Result string


For i := 1 to L do
(
    
    If      Mid({Table.Memo_Field},E,1) = ' '
    Then    N := N + Mid({Table.Memo_Field}, S,  75) + '^^'    
    Else    N := N + Mid({Table.Memo_Field}, S,  (InStrRev ({Table.Memo_Field}, ' ', E) - S)) + '^^';

    S := InStrRev ({Table.Memo_Field}, ' ', E) + 1;
    E := S + 75;
    
);

N

[u]2. Formula to extract single line (in this case the 3rd)[/u]
[Code]
WhilePrintingRecords;
Global StringVar N;

Split(N,'^^') [3]

However, it is not clear that this will really help, as the only way I can see to extract the individual lines would be with enough of the second formula to cover the maximum number of lines (at least 80) which I am not sure is really a workable solution. The upside is that the repeated formula is very simple, with only the number representing the array element changing from formula to formula.

Hope this helps at least a bit.

Cheers
Pete
 

thank you Pete!!!! i took some time off during the holidays, but i'm going to play with this right now.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top