In a BAQ, how would you display the 3rd line in calculated field?

Epicor ERP 10
1

Hello,
I have not seen this anywhere so I thought I would ask here.
I have a BAQ the needs the 3rd line displayed in a field.
The table is Erp.HDCase, the field is: Description.
It looks like it is broken apart in the field by carriage returns.
Is there a way to pull a single line from the field?

Ex: Text of Line 1(carriage return)Text of line 2(Carriage return)Text of Line 3(carriage return)Text of line 4(Carriage return), etc…

I would like the Field to only display: Text of Line 3
Any and all help is appreciated.

2

entry
entry.png933×583 41.8 KB

Did in another BAQ but you get the idea.

3 Likes
3

It looks close but when I hit Check syntax, it gives me the error “Invalid column name ‘HDCase’.”.
Any ideas?

4

Yes, I did this a BAQ for Operations. Erase HDCase and insert the field from the list in Available Tables.

5

Hold on, I figured it out. Where you put HDCase, I needed HDCase.Description.

I tested and it worked!!!

Thanks! You are a life saver @Mark_Wonsil

1 Like
6

To make it a little more resilient, do an IF statement and use the numentries function to make sure you have at least three lines of text, if so, use the function as you have it, if not then ‘’.

7

I was playing with it and I noticed something that threw me off. The line I wanted is the 3rd line, but to get it I had to point to the 2nd line. [Ice].entry(2,HDCase.Description , char(13))

Does that make sense?
I thought maybe it was based off of starting at 0, but there was nothing showing at 0. The 2nd line shows when I do:
[Ice].entry(1,HDCase.Description , char(13))

If in the future, I wanted the 1st line only, how would I pull that?

8

Put the string under a HEX (editor) and make sure it’s all CRs in there.

9

Here is an example from the database:

XXXX High School(Carriage Return)
YYYY High School(Carriage Return)
Off Chris ZZZZ(Carriage Return)
Address Line 1(Carriage Return)
City, STATE ZIP(Carriage Return)
PHONE #(Carriage Return)
EMAIL

When I do line 1 it pulls: YYYY High School instead of XXXX High School

By the way, the (Carriage Return) shows CRLF in Notepad++.

10

We might want to open a case with Epicor on this one. Entry is a 1-based array and you should be able to access any element as needed.

One thing you could try is to replace the Char(13) with a visible character (^ or |) and then do the entry using the new character as the separator. (Use the Replace function … Replace(HDCase.Description, char(13), ‘|’) for example and then pass that to Entry using the ‘|’ as the third parameter.

Mark W.

11

Using the 1 and chr(13) works for me to display the first line but I’m working off of Part.PartDescription…what happens when you use a blank (’ ') as the separator/splitter? Does it show the XXXX? Maybe a chr(10) instead?

[Ice].entry(1, HDCase.Description , ’ ')

I honestly have never used that function before…thanks @Mark_Wonsil! :wink:

1 Like
12

For the 1st line, it worked to use the separator of: char(10).
Weirdly, the char(10) didn’t work for the 3rd line, so I used the char(13) and pointed to line 2.
Kinda weird but it works, so I am going with it.

I just want to thank both of you for your help. It is nice to get an issue resolved quickly.

1 Like
13

So, you can use the REPLACE function to remove the Char(10), newline, and then maybe the Entry will work as originally coded.

1 Like
14

Now, to be a bit of a turd, I need to ask: “Why not just create UD fields?” They are so much easier to process and enforce usage than user-error-prone data entry?

:slight_smile:

Mark W.

15

I agree. My problem is that they have already populated their data into this field and there are a couple hundred already done. I am not going to move their data and I doubt they will. Also, I don’t know if that field is being used for any reports pre-built by Epicor. I try to work within Epicor to make sure I don’t break to many things when I do a change.

16

Was reminded of splitting up the SalesRepList… using 4 tildes in the statement
So I tried 4 blanks for splitting the description and that seemed to work.

Ref E10 SalesRep1
case when
[Ice].num_entries(OrderHed.SalesRepList,‘~~~~’)>=1

then
[Ice].entry(1, OrderHed.SalesRepList,‘~~~~’)
else ‘’
end

1 Like
17

Understood. The Case Description is indexed and used by the search to help find cases easily. It appears that the users are using it as a data store, which is fine if each and every entry is done correctly in the free-form field. :thinking:

If, in the future, they want to do some reporting on this data, you may want to recommend moving it to UD field and make your lives so much easier. You can add validations to the UD fields that ensure a valid state is in the state field, that a zip code is valid, etc but that’s almost impossible using a free-form field. I know the position you’re in, just planting the seed. :wink:

Have a great day!

Mark W.

1 Like