BAQ Tildes [Rant follows]

Yahoo Archive
1
I'm going to guess that this "design pattern" is something "natural" in the Progress development toolkit.

Overnormalizing a database can be as painful as undernormalizing it.

I wonder if the multi-valued properties are easily-accessible in 4GL (as an array[]) and just stupid to deal with in table joins?

-bws

--
Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix / Picometrix
    bspolarich@... ~ 734-864-5618 ~ www.advancedphotonix.com


-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Rob Bucek
Sent: Tuesday, June 02, 2009 12:43 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] BAQ Tildes [Rant follows]

Thanks for the comedic break Thom, I needed a good laugh.



Rob Bucek

Manufacturing Engineer

PH: 715-284-5376 ext 311

FAX: 715-284-4084

<http://www.dsmfg.com/>

(Click the logo to view our site)



________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Thomas Rose
Sent: Tuesday, June 02, 2009 8:19 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] BAQ Tildes [Rant follows]





And whichever Epicorean came up with the idea to put a list of sales
reps in one field separated by tildes needs to be sent to the board to
write 1000 times, "I will always normalize my databases". That idea was
the height of stupidity. After that, we can send the Epicorean who
decided to have five fields each for rep commission amounts and splits
to the board for the same punishment. That idea was stupid, too, but
does not achieve quite the same stupidity level as the reps separated by
tildes.

Okay, I'll take my pill and calm down now.

Thom Rose

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf Of Brian W. Spolarich
Sent: Tuesday, June 02, 2009 6:12 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] BAQ Tildes

Use a calculated field whose value is this 4GL expression:

REPLACE(InvcHead.SalesRepList,"~","")

However if you have more than one SalesRep associated with the invoice
your BAQ won't do as you expect.

-bws

--
Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix /
Picometrix
bspolarich@...
<mailto:bspolarich%40advancedphotonix.com>
<mailto:bspolarich%40advancedphotonix.com> ~ 734-864-5618 ~
www.advancedphotonix.com

-----Original Message-----
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>]
On Behalf Of jplehr
Sent: Tuesday, June 02, 2009 8:55 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
Subject: [Vantage] BAQ Tildes

Vantage 8.03.406

I am sure I saw something on this before but have been unable to find
it.

I have a BAQ that is pulling invoice information. I found that when a
invoice is placed on-hold the Sales Rep ID comes in with (4) tildes
following the ID (i.e. XXXX~~~~). This presents a problem for me because
my dashboard is listing the Salesperson Description (Name) matching the
ID. Obviously, I do not have an ID with tildes so the name is then
blank.

I received a small document from support on how to strip the tildes, but
have not been successfull.

Anything?

Jeff

------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder
and Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
<http://groups.yahoo.com/group/vantage/files/> <
http://groups.yahoo.com/group/vantage/files/
<http://groups.yahoo.com/group/vantage/files/> >
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
<http://groups.yahoo.com/group/vantage/messages>
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/linksYahoo
<http://groups.yahoo.com/group/vantage/linksYahoo> ! Groups Links

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]



------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links
2
Vantage 8.03.406

I am sure I saw something on this before but have been unable to find it.

I have a BAQ that is pulling invoice information. I found that when a invoice is placed on-hold the Sales Rep ID comes in with (4) tildes following the ID (i.e. XXXX~~~~). This presents a problem for me because my dashboard is listing the Salesperson Description (Name) matching the ID. Obviously, I do not have an ID with tildes so the name is then blank.

I received a small document from support on how to strip the tildes, but have not been successfull.

Anything?

Jeff
3
Use a calculated field whose value is this 4GL expression:

REPLACE(InvcHead.SalesRepList,"~","")

However if you have more than one SalesRep associated with the invoice your BAQ won't do as you expect.

-bws

--
Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix / Picometrix
    bspolarich@... ~ 734-864-5618 ~ www.advancedphotonix.com


-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of jplehr
Sent: Tuesday, June 02, 2009 8:55 AM
To: vantage@yahoogroups.com
Subject: [Vantage] BAQ Tildes

Vantage 8.03.406

I am sure I saw something on this before but have been unable to find it.

I have a BAQ that is pulling invoice information. I found that when a invoice is placed on-hold the Sales Rep ID comes in with (4) tildes following the ID (i.e. XXXX~~~~). This presents a problem for me because my dashboard is listing the Salesperson Description (Name) matching the ID. Obviously, I do not have an ID with tildes so the name is then blank.

I received a small document from support on how to strip the tildes, but have not been successfull.

Anything?

Jeff



------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links
4
The tildas are to separate the 5 different sales reps that can be listed per invoice. For some bazar reason, Epicor want to put all 5 in one field instead of using 5 different fields.

What I do is create 5 different calculated fields in my BAQ for the 5 reps. I have also seen just one rep listed without tildas, so I have to compensate for that also

REP1 is:IF INDEX(InvcHead.SalesRepList,"~~") > 0 THEN ENTRY(1, InvcHead.SalesRepList, "~~") ELSE InvcHead.SalesRepList
Rep2 is:IF INDEX(InvcHead.SalesRepList,"~~") > 0 THEN ENTRY(2, InvcHead.SalesRepList, "~~") ELSE ""

Rep3 is:
IF INDEX(InvcHead.SalesRepList,"~~") > 0 THEN ENTRY(3, InvcHead.SalesRepList, "~~") ELSE ""
Â
And so forth. I have to use "~~" because the tilda is a special character in Progress OpenEdge.



________________________________
From: jplehr <jlehr@...>
To: vantage@yahoogroups.com
Sent: Tuesday, June 2, 2009 8:54:54 AM
Subject: [Vantage] BAQ Tildes





Vantage 8.03.406

I am sure I saw something on this before but have been unable to find it.

I have a BAQ that is pulling invoice information. I found that when a invoice is placed on-hold the Sales Rep ID comes in with (4) tildes following the ID (i.e. XXXX~~~~). This presents a problem for me because my dashboard is listing the Salesperson Description (Name) matching the ID. Obviously, I do not have an ID with tildes so the name is then blank.

I received a small document from support on how to strip the tildes, but have not been successfull.

Anything?

Jeff


Â




[Non-text portions of this message have been removed]
5
And whichever Epicorean came up with the idea to put a list of sales reps in one field separated by tildes needs to be sent to the board to write 1000 times, "I will always normalize my databases". That idea was the height of stupidity. After that, we can send the Epicorean who decided to have five fields each for rep commission amounts and splits to the board for the same punishment. That idea was stupid, too, but does not achieve quite the same stupidity level as the reps separated by tildes.

Okay, I'll take my pill and calm down now.

Thom Rose


From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Brian W. Spolarich
Sent: Tuesday, June 02, 2009 6:12 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] BAQ Tildes





Use a calculated field whose value is this 4GL expression:

REPLACE(InvcHead.SalesRepList,"~","")

However if you have more than one SalesRep associated with the invoice your BAQ won't do as you expect.

-bws

--
Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix / Picometrix
bspolarich@...<mailto:bspolarich%40advancedphotonix.com> ~ 734-864-5618 ~ www.advancedphotonix.com

-----Original Message-----
From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>] On Behalf Of jplehr
Sent: Tuesday, June 02, 2009 8:55 AM
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>
Subject: [Vantage] BAQ Tildes

Vantage 8.03.406

I am sure I saw something on this before but have been unable to find it.

I have a BAQ that is pulling invoice information. I found that when a invoice is placed on-hold the Sales Rep ID comes in with (4) tildes following the ID (i.e. XXXX~~~~). This presents a problem for me because my dashboard is listing the Salesperson Description (Name) matching the ID. Obviously, I do not have an ID with tildes so the name is then blank.

I received a small document from support on how to strip the tildes, but have not been successfull.

Anything?

Jeff

------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.<http://groups.yahoo.com/group/vantage/files/>
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links



[Non-text portions of this message have been removed]
6
I have a Crystal Report that uses the SalesRepList. This is the formula
that I use to sort them out.



if not(isnull({OrderHed.SalesRepList})) and Len({OrderHed.SalesRepList})
> 0 THEN

(

IF instr({OrderHed.SalesRepList}, "~") < 1 then {OrderHed.SalesRepList}
ELSE

left({OrderHed.SalesRepList},instr({OrderHed.SalesRepList},"~")-1)

)

else "NO REP"



Just swap out the OrderHed for InvcHed and you should be set.



Travis



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Brian W. Spolarich
Sent: Tuesday, June 02, 2009 8:12 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] BAQ Tildes








Use a calculated field whose value is this 4GL expression:

REPLACE(InvcHead.SalesRepList,"~","")

However if you have more than one SalesRep associated with the invoice
your BAQ won't do as you expect.

-bws

--
Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix /
Picometrix
bspolarich@...
<mailto:bspolarich%40advancedphotonix.com> ~ 734-864-5618 ~
www.advancedphotonix.com

-----Original Message-----
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf Of jplehr
Sent: Tuesday, June 02, 2009 8:55 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: [Vantage] BAQ Tildes

Vantage 8.03.406

I am sure I saw something on this before but have been unable to find
it.

I have a BAQ that is pulling invoice information. I found that when a
invoice is placed on-hold the Sales Rep ID comes in with (4) tildes
following the ID (i.e. XXXX~~~~). This presents a problem for me because
my dashboard is listing the Salesperson Description (Name) matching the
ID. Obviously, I do not have an ID with tildes so the name is then
blank.

I received a small document from support on how to strip the tildes, but
have not been successfull.

Anything?

Jeff

------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder
and Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
<http://groups.yahoo.com/group/vantage/files/>
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links





[Non-text portions of this message have been removed]
7
Unfortunately you cannot put a join condition on that field to provide
you the Sales Rep name. Even after stripping the tilde's off in a
calc'd field all you have is the Sales Rep ID.

Steven's idea is the best route to somewhat achieve what you want. But
forget about relating/joining the Sales Rep ID to their Name.

Unless.... you're SQL and want to report on a SQL View and/or you don't
mind using ODBC in your report. Then you can join your Crystal Reports
report from the XML output to the respective table in the database and
join within Crystal Reports.

Solely using BAQ XML it's impossible.

And yes, whatever bonehead database developer intern from 4th grade
thought of this database logic should never be allowed to touch a
keyboard again.




-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of jplehr
Sent: Tuesday, June 02, 2009 8:55 AM
To: vantage@yahoogroups.com
Subject: [Vantage] BAQ Tildes

Vantage 8.03.406

I am sure I saw something on this before but have been unable to find
it.

I have a BAQ that is pulling invoice information. I found that when a
invoice is placed on-hold the Sales Rep ID comes in with (4) tildes
following the ID (i.e. XXXX~~~~). This presents a problem for me because
my dashboard is listing the Salesperson Description (Name) matching the
ID. Obviously, I do not have an ID with tildes so the name is then
blank.

I received a small document from support on how to strip the tildes, but
have not been successfull.

Anything?

Jeff



------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder
and Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links
8
Can't you do a link of invoice line to sales order; sales order to primary rep number; and rep# to rep name?

Sent from my Verizon Wireless BlackBerry

-----Original Message-----
From: Vic Drecchio <vic.drecchio@...>

Date: Tue, 2 Jun 2009 13:29:46
To: <vantage@yahoogroups.com>
Subject: RE: [Vantage] BAQ Tildes


Unfortunately you cannot put a join condition on that field to provide
you the Sales Rep name. Even after stripping the tilde's off in a
calc'd field all you have is the Sales Rep ID.Â

Steven's idea is the best route to somewhat achieve what you want. But
forget about relating/joining the Sales Rep ID to their Name.

Unless.... you're SQL and want to report on a SQL View and/or you don't
mind using ODBC in your report. Then you can join your Crystal Reports
report from the XML output to the respective table in the database and
join within Crystal Reports.

Solely using BAQ XML it's impossible.

And yes, whatever bonehead database developer intern from 4th grade
thought of this database logic should never be allowed to touch a
keyboard again.




-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com <mailto:vantage@yahoogroups.com> ] On Behalf
Of jplehr
Sent: Tuesday, June 02, 2009 8:55 AM
To: vantage@yahoogroups.com
Subject: [Vantage] BAQ Tildes

Vantage 8.03.406

I am sure I saw something on this before but have been unable to find
it.

I have a BAQ that is pulling invoice information. I found that when a
invoice is placed on-hold the Sales Rep ID comes in with (4) tildes
following the ID (i.e. XXXX~~~~). This presents a problem for me because
my dashboard is listing the Salesperson Description (Name) matching the
ID. Obviously, I do not have an ID with tildes so the name is then
blank.

I received a small document from support on how to strip the tildes, but
have not been successfull.

Anything?

Jeff



------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note:Â You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder
and Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/ <http://groups.yahoo.com/group/vantage/files/> .Â
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages <http://groups.yahoo.com/group/vantage/messages>
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/linksYahoo! <http://groups.yahoo.com/group/vantage/linksYahoo!> Groups Links





------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note:Â You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/ <http://groups.yahoo.com/group/vantage/files/> .Â
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages <http://groups.yahoo.com/group/vantage/messages>
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/linksYahoo! <http://groups.yahoo.com/group/vantage/linksYahoo!> Groups Links
9
Thanks for the comedic break Thom, I needed a good laugh.



Rob Bucek

Manufacturing Engineer

PH: 715-284-5376 ext 311

FAX: 715-284-4084

<http://www.dsmfg.com/>

(Click the logo to view our site)



________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Thomas Rose
Sent: Tuesday, June 02, 2009 8:19 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] BAQ Tildes [Rant follows]





And whichever Epicorean came up with the idea to put a list of sales
reps in one field separated by tildes needs to be sent to the board to
write 1000 times, "I will always normalize my databases". That idea was
the height of stupidity. After that, we can send the Epicorean who
decided to have five fields each for rep commission amounts and splits
to the board for the same punishment. That idea was stupid, too, but
does not achieve quite the same stupidity level as the reps separated by
tildes.

Okay, I'll take my pill and calm down now.

Thom Rose

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf Of Brian W. Spolarich
Sent: Tuesday, June 02, 2009 6:12 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] BAQ Tildes

Use a calculated field whose value is this 4GL expression:

REPLACE(InvcHead.SalesRepList,"~","")

However if you have more than one SalesRep associated with the invoice
your BAQ won't do as you expect.

-bws

--
Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix /
Picometrix
bspolarich@...
<mailto:bspolarich%40advancedphotonix.com>
<mailto:bspolarich%40advancedphotonix.com> ~ 734-864-5618 ~
www.advancedphotonix.com

-----Original Message-----
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>]
On Behalf Of jplehr
Sent: Tuesday, June 02, 2009 8:55 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
Subject: [Vantage] BAQ Tildes

Vantage 8.03.406

I am sure I saw something on this before but have been unable to find
it.

I have a BAQ that is pulling invoice information. I found that when a
invoice is placed on-hold the Sales Rep ID comes in with (4) tildes
following the ID (i.e. XXXX~~~~). This presents a problem for me because
my dashboard is listing the Salesperson Description (Name) matching the
ID. Obviously, I do not have an ID with tildes so the name is then
blank.

I received a small document from support on how to strip the tildes, but
have not been successfull.

Anything?

Jeff

------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder
and Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
<http://groups.yahoo.com/group/vantage/files/> <
http://groups.yahoo.com/group/vantage/files/
<http://groups.yahoo.com/group/vantage/files/> >
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
<http://groups.yahoo.com/group/vantage/messages>
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/linksYahoo
<http://groups.yahoo.com/group/vantage/linksYahoo> ! Groups Links

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]