Caclulated Field in BAQ - Substring inside text

Epicor ERP 10
,
1

How can I extract a portion of text from inside a field. I’m using the JobHead.PartDescription field.
I would like to be able to extract the Dim Dwg: shown in bold below.

Trailer Unit
High Fuel, Low Fuel Pre-Alarm
Bonded Neutral
Processed By: Jim 7-25-17
Dim Dwg: ABC-01-SAE-12
AC Diagram: E-A-01234
DC Diagram: E-D-04321

Unfortunately, I cannot simply count the characters since the preceding text varies. I was thinking about chopping it up into smaller pieces then doing a substring at character position.

Something with substring and charindex? Pattern index?

I can use PATINDEX() to identify the start of the word
PATINDEX(’%Dwg:%’, JobHead.PartDescription)

Maybe able to use that information to do my substring.

2

Well solved my own problem by talking it out haha.

Not super concise but it works. I’m sure you could build it into one command.

Calc_IndexLoc
PATINDEX(’%Dwg:%’, JobHead.PartDescription)

Calc_DimDwg
SUBSTRING(JobHead.PartDescription, Calc_IndexLoc, 18)

Concise.

SUBSTRING(JobHead.PartDescription, PATINDEX(’%Dwg:%’, JobHead.PartDescription), 18)

3

You can key off of your patindex and the next newline character if the dwg is not always same length.

4

How do i do that? Very interested.

5

Here is example of one that parses OrderDtl.OrderComment field for the text ‘BOAT MFG’ and ‘:’; termination is based on the NewLine char(10)

[Ice].entry(2, 
(case when CHARINDEX('BOAT MFG', OrderDtl.OrderComment ) > 0 then  
case 
    when CHARINDEX(CHAR(10), OrderDtl.OrderComment, CHARINDEX('BOAT MFG', OrderDtl.OrderComment )) > 0 then
    SUBSTRING(OrderDtl.OrderComment,CHARINDEX('BOAT MFG', OrderDtl.OrderComment ),CHARINDEX(CHAR(10), OrderDtl.OrderComment, CHARINDEX('BOAT MFG', OrderDtl.OrderComment ))-CHARINDEX('BOAT MFG', OrderDtl.OrderComment ))
    else ''
end
    else '' 
end)
,':')

Horribly inefficient, but I could not think of another way to do this.

6

I cant really understand it. I think the idea is there though. I’ll see what I can do with it.
maybe its searching in the first 10 characters?

Here is my attempt.

(Integer)DWG
PATINDEX(’%Dwg:%’, JobHead.PartDescription)

(Integer)AC
PATINDEX(’%AC Diagram:%’, JobHead.PartDescription)

(Integer)DWGAC
DWG - AC

(nvarchar)DimDraw
SUBSTRING(JobHead.PartDescription, PATINDEX(’%Dwg:%’, JobHead.PartDescription), DWGAC)

Concise (the +4 and -4 are for removing “Dwg:” from the start of the string.)

SUBSTRING(JobHead.PartDescription,
PATINDEX(’%Dwg:%’, JobHead.PartDescription) + 4,
(PATINDEX(’%AC Diagram:%’, JobHead.PartDescription)-4) - PATINDEX(’%Dwg:%’, JobHead.PartDescription))

7

CHAR(10) is for a new line char and delimits an end of line and not 10 characters. Where your sample data had

At the end of each line there is a CHAR(10) and usually a CHAR(13) on Windows systems one denotes a NEWLINE and the other is CARRIAGE RETURN. These are hidden but part of the actual strings and that is how I am grabbing entire line containing my search string and using the Ice.Entry for the part after the ‘:’

My data looks like this

Boat Year: 2021
Boat Mfg: Bennington
Boat Model: etc…

I’m grabbing the ‘line’ with “Boat Mfg: Bennington” and using the Ice.Entry function to grab whatever is after the “:” on the Boat Mfg line.

Is there a line with Boat Mfg?
case when CHARINDEX('BOAT MFG', OrderDtl.OrderComment ) > 0 then

Make sure there is a ‘LINE’ ending with CHAR(10) having ‘BOAT MFG’ in it

case 
    when CHARINDEX(CHAR(10), OrderDtl.OrderComment, CHARINDEX('BOAT MFG', OrderDtl.OrderComment )) > 0 then

Return that line

SUBSTRING(OrderDtl.OrderComment,CHARINDEX('BOAT MFG', OrderDtl.OrderComment ),CHARINDEX(CHAR(10), OrderDtl.OrderComment, CHARINDEX('BOAT MFG', OrderDtl.OrderComment ))-CHARINDEX('BOAT MFG', OrderDtl.OrderComment ))

You could replace the string BOAT MFG with your ‘DIM DWG’ or ‘Dim Dwg’ either should work and that will return the drawing number no matter how long it is provided there is an end of line character at the end of its line :slight_smile: where your code is using the ‘AC Diagram’ as long as no other data pieces use ‘AC’ before the ‘DIM DWG’ you’ll never get an error…