Is there a way to create a calculated fiel from a character string and replace some invalid characters in it?
Here is the SQL command
:
Replace(Replace(Replace(Replace(Replace(Replace(part.PartDescription,char(9),’ ‘),char(10),’ ‘),char(11),’ ‘),char(12),’ ‘),char(13),’ ‘),’"’, ’ ')
how can I do this in a BAQ?
Please advise.
Jeff Henslee
M-B Companies Inc.
Hi @jhenslee,
We’ve had to do the same thing to eliminate some unneeded characters in fields within our Queries. We basically used the exact same code that you have in your original post for a calculated field in a BAQ.
So, for example, we have a Calculated Field that contains the following code:
Replace(Replace(OrderDtl.LineDesc, chr(13), " "), chr(10), " ")
It will first remove all Carriage Returns (chr(13)) then it will remove all New Line Characters (chr(10))…of course I had to make sure that my Calculated field with a Character Data Type was changed to an appropriate Format Size (default is x(8) which chops a lot of Characters off and makes the formula look like it isn’t working right).
I know this Replace within a Replace nesting can get pretty ugly and confusing and I would think there is probably a better way out there which I would love to know about but this is what has worked for us with my limited programming skillz. 
I would definitely recommend (based on experience) starting with one Replace function and building from that - testing each newly added one - just so that it doesn’t get too confusing and you don’t go cross-eyed! 
Cha-ching! Winner! Winner! Chicken Diner!
Worked like a charm!
Thanks a Ba Zillion!
Is there any kind of record size limit on the amount quantity/size of data that can be exported /extracted from a BAQ? As soon as I press the Test Button I get a 4GL stop. I have removed all of my calculated fields and the same thing happens. I remove about 80% of the fields from the Query and then poof all of a sudden it starts working.
Thoughts? Please advise.
Maybe its an issue with showing all the results in the grid.
What happens if you try the BAQ Export process? Does it throw an error, or geneeate the file?
FWIW - when I would make a BAQ in 8.03, I’d throw a temporary table criteria in to limit the results returned during testing. Then remove the critetia when going live.
You are welcome! I’m so glad it worked for you. 
Have you looked at your Log files after you get the 4GL error to see if they tell you anymore about what is happening?
I’ve never run into this issue before so I can’t really give any advice but maybe that’s why Epicor has now limited the initial number of records that a BAQ returns in E10. I think the default is 10,000 which can be changed but maybe that’s the reason they implemented that setting? Can you return fewer records at a time while still keeping all of your Fields/Columns to see what happens?
When something isn’t working I always like to start from the point where it was and then add one thing at a time, testing each newly added link or field one at a time and finding the point where it breaks. It could maybe even be a linking issue and it doesn’t break until you throw in a field from that poorly (for lack of a better word) linked table? Just grasping at straws here…