I would like some help getting started converting an old Progress 4gl calculated field to Kinetic 2022. The old Progress Code was ‘’‘IF INDEX(QuoteDtl.LineDesc, CHR(10) ) > 0 THEN TRIM(REPLACE(SUBSTRING(QuoteDtl.LineDesc, 1, INDEX(QuoteDtl.LineDesc, CHR(10) ) - 1), “,”, " ") ) ELSE QuoteDtl.LineDesc ‘’’
Assuming the calculated field is in a baq those are sql statements so you can google for the sql commands. C# also has a replace.
This will be Replace(QuoteDtl.LineDesc,CHAR(10),‘’)
1 Like
And if it’s in a BPM or something it will be this:
string whatever = QuoteDtl.LineDesc.IndexOf("\n") > 0 ? QuoteDtl.LineDesc.Substring(0, QuoteDtl.LineDesc.IndexOf("\n")).Replace(",", " ").Trim() : QuoteDtl.LineDesc;
What is this for exactly? Might be a better way to handle it.
I am trying to use it in a calculated field in a BAQ. This way when the data is exported for reporting purposes, it is nice and clean and limits the width of the column, and does not create funny line wraps in excel.
to
int newlineIndex = QuoteDtl.LineDesc.IndexOf('\n');
string result;
if (newlineIndex > 0)
{
result = QuoteDtl.LineDesc.Substring(0, newlineIndex)
.Replace(",", " ")
.Trim();
}
else
{
result = QuoteDtl.LineDesc;
}
Curious if this worked lol
When I check syntax, I get this error.
I tried replacing the double quotes with single quotes, as I saw that somewhere. Thanks for the help. I’m just a noob trying to get started!
hold i goofed that up but I made the chat bot apologize for my transgressions
CASE
WHEN CHARINDEX(CHAR(10), QuoteDtl.LineDesc) > 0
THEN RTRIM(LTRIM(REPLACE(SUBSTRING(QuoteDtl.LineDesc, 1, CHARINDEX(CHAR(10), QuoteDtl.LineDesc) - 1), ',', ' ')))
ELSE QuoteDtl.LineDesc
END
@jgiese.wci
Your code returns the following error:
Yeah ignore that first one. It wasn’t initially clear if you were talking BPM or BAQ I assumed BPM. Try the second set of code.
The correct answer is GPayne’s first answer up there… It’s just a replace. The rest is irrelevant.
It’s not though the original code is doing something with a substring before commas I believe. This is how I interpret it.
Consider QuoteDtl.LineDesc has the following different values:
- “Hello, world\nThis is a test.”
The newline character is at position 12, so the substring before the newline is “Hello, world”. The comma is replaced with a space, and the result is “Hello world”.
- “This string has no newline character.”
There is no newline character in this string, so the original string is returned: “This string has no newline character.”
- “Another\n,example.”
The newline character is at position 8, so the substring before the newline is “Another”. There are no commas to replace, so the result is “Another”.
- “Yet,another,example\nwith commas.”
The newline character is at position 20, so the substring before the newline is “Yet,another,example”. The commas are replaced with spaces, and the result is “Yet another example”.
- “\nA string that starts with a newline.”
The newline character is at position 1, so the substring before the newline is empty. Therefore, an empty string is returned.
So these are some examples of how the provided SQL code would work given different inputs for QuoteDtl.LineDesc.
I could be wrong it has been a long time since I have used 4GL but something besides replace new line is occuring in the original code.
This is getting closer. (the syntax checker liked it, and it tested without error.)
However, I wish to only return the description up to the first carriage return if a carriage return is present.
for example, for this description since there is a carriage return after the word CART, I would only want the first line highlighted. Otherwise if there isn’t a return, then the result should be the entire description. I will use the charachter limit to trim the overall length x(50)
Can trim it right in SQL if you want, not sure if there is any difference in performance but here you are. I believe this should work.
LEFT(
CASE
WHEN CHARINDEX(CHAR(10), QuoteDtl.LineDesc) > 0 THEN LEFT(QuoteDtl.LineDesc, CHARINDEX(CHAR(10), QuoteDtl.LineDesc) - 1)
ELSE QuoteDtl.LineDesc
END
, 50)
I adjusted it slightly by changing the CHAR(10) to CHAR(13) and it behaves as I was expecting it to!
Thank you so much!!!
LEFT(
CASE
WHEN CHARINDEX(CHAR(13), QuoteDtl.LineDesc) > 0 THEN LEFT(QuoteDtl.LineDesc, CHARINDEX(CHAR(13), QuoteDtl.LineDesc) - 1)
ELSE QuoteDtl.LineDesc
END
, 50)
1 Like
You might be able to reduce the complicated part to this:
This says, grab the first element of a string separated by a return, I mean newline. Change to ‘\r’ for a return. You would still take the LEFT(,50) of this.
BTW, the original 4GL could have been written this way too because [ice].index is an Epicor implementation of the Progress 4GL Index function.
2 Likes