Report Builder Joins

Although I have not worked on any reports regarding the memo table, I
will agree with Sarah that using MS Access to handle difficult
reports has been very helpful to us. When the data is difficult to
report on or you require large amounts of groupings and calculations
I prefer to bring the data into Access using a Make Table Query.
Once the data is in my Access Tables, I can massage it in a variety
of ways to get the results I want, including changing data type. The
make table queries don't take that long to run (from a few seconds to
around 45 seconds depending on table size), and once the data is in
Access the reports generated off it run almost instantly. This
method works really well if the info on the report is only needed to
be updated daily and there are a lot of people who want to look at
the data, it's a lot better than a bunch of people waiting for a
Crystal Web report or a Report Builder Report. I still use those
tools for simpler reports, definately. But I've really been
utilizing Access more and more for the tougher projects.

My 2 cents,

Brian Stenglein
Clow Stamping Co.

--- In vantage@y..., sarah.vareschi@n... wrote:
> We also use memos quite a bit, and I totally agree that when
reporting, it
> is very cumbersome and slow to run, using report builder. I have
created
> some reports using ODBC and MSAccess, which work well and run fairly
> quickly. If it is available, you may want to try it...
>
>
>
> [Non-text portions of this message have been removed]
Could someone help me out with this:
I have a hardware list created from Bill of Materials and from there
a Hardware List, etc. On this List, I want to add if needs
Purchased, Warehouse, and Bin.

I have it working except it gives multiple lines. If I take bin off,
a few extra lines are gone. This happens with the other 2 as well.
All 3 add extra lines and no 1 join takes the extra lines off at
once. This is what I did (by the way, there are 2 companies so I
need to filter each join by company):

Master: Job
Joins: Job to Job Mtl by JobNum
JobMtl to Pary by PartNum
(These 2 are fine to make the report function in general)
Now there is:
JobMtl to QuoteMtl by Co.
by PartNum (to get BuyIt)
Part to PartWhse by Co.
by PartNum (to get Warehouse)
Part to PlantWhse by Co.
by PartNum (to get Primary Bin)

Does anybody have a clue what I am trying to do or what I need to do.
Any ideas would be appreciated.
Thanks.
Hi everyone,

Can someome please help me with a RB join for the Memo Test Files. I'm
doing a Quote Follow Up Report for my salespeople and I want to print
specific memos for specific quotes. I am currently getting all memo
text printed with every quote on the report.

Thanks

Janet Memmelaar
Vice-President Information Systems
Metal Cutting Corporation
973-239-1100 x 28
When I've joined to memos I've had to join on company only then filter for
key matches. Runs slow but works. I could not join with a key due to type
mismatch so filtering was the only way.
-Todd C.

-----Original Message-----
From: Janet Memmelaar [mailto:janet@...]
Sent: Tuesday, July 09, 2002 9:26 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Report Builder Joins


Hi everyone,

Can someome please help me with a RB join for the Memo Test Files. I'm
doing a Quote Follow Up Report for my salespeople and I want to print
specific memos for specific quotes. I am currently getting all memo
text printed with every quote on the report.

Thanks

Janet Memmelaar
Vice-President Information Systems
Metal Cutting Corporation
973-239-1100 x 28



Yahoo! Groups Sponsor

<http://rd.yahoo.com/M=229641.2166546.3626727.1829184/D=egroupweb/S=17050071
83:HM/A=1142327/R=0/*http://promo.yahoo.com/debtscape/>

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/links
<http://groups.yahoo.com/group/vantage/links>

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .




[Non-text portions of this message have been removed]
I've had success in joining memos by using calculated fields to make the
join. It does get hinky though because sometimes it works better converting
the actual field to the MEMO field format. It seems to work best if you
make the Memo file the master file and "PULL" the other records to the memo
record. Also I've used conditional printing to tune a report to the way I
want it

Shirley

-----Original Message-----
From: Todd Caughey [mailto:caugheyt@...]
Sent: Tuesday, July 09, 2002 10:57 AM
To: 'vantage@yahoogroups.com'
Subject: RE: [Vantage] Report Builder Joins


When I've joined to memos I've had to join on company only then filter for
key matches. Runs slow but works. I could not join with a key due to
type
mismatch so filtering was the only way.
-Todd C.

-----Original Message-----
From: Janet Memmelaar [mailto:janet@...]
Sent: Tuesday, July 09, 2002 9:26 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Report Builder Joins


Hi everyone,

Can someome please help me with a RB join for the Memo Test Files. I'm
doing a Quote Follow Up Report for my salespeople and I want to print
specific memos for specific quotes. I am currently getting all memo
text printed with every quote on the report.

Thanks

Janet Memmelaar
Vice-President Information Systems
Metal Cutting Corporation
973-239-1100 x 28



Yahoo! Groups Sponsor


<http://rd.yahoo.com/M=229641.2166546.3626727.1829184/D=egroupweb/S=17050071
83:HM/A=1142327/R=0/*http://promo.yahoo.com/debtscape/>

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/links
<http://groups.yahoo.com/group/vantage/links>

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .




[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/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



[Non-text portions of this message have been removed]
You can not join between the Quote Head table and Memos because the Quote
Number is a numeric field and even if you convert it with a calculated field
to character field report builder will not list is as a field you can use to
do a join. So regarding quotes you will have to filer for memos which means
you can get create a report if it's only filter for or list a single quote.
I hope someone can prove me wrong on this.

Patrick

-----Original Message-----
From: Janet Memmelaar [mailto:janet@...]
Sent: Tuesday, July 09, 2002 9:26 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Report Builder Joins


Hi everyone,

Can someome please help me with a RB join for the Memo Test Files. I'm
doing a Quote Follow Up Report for my salespeople and I want to print
specific memos for specific quotes. I am currently getting all memo
text printed with every quote on the report.

Thanks

Janet Memmelaar
Vice-President Information Systems
Metal Cutting Corporation
973-239-1100 x 28



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/links

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
Patrick is certainly correct, I tried it both ways, and it doesnt fly. I
did have an off thought on how you could "possibly" make a direct link to
memo's. It would mean combining some other tools that we've talked about,
and using up one of the limited number of VB form fields. You could have
the quote number entered into an available character field in VB form's
either by hand, (high likelyhood of keying error) or by one of the automatic
VB field entry techniques we've talked about on list. If you were
consistant in doing this you could then "maybe" get a link. I havent tried
it though.

Shirley

-----Original Message-----
From: Winter, Patrick [mailto:pjw@...]
Sent: Tuesday, July 09, 2002 12:40 PM
To: 'vantage@yahoogroups.com'
Subject: RE: [Vantage] Report Builder Joins


You can not join between the Quote Head table and Memos because the Quote
Number is a numeric field and even if you convert it with a calculated
field
to character field report builder will not list is as a field you can use
to
do a join. So regarding quotes you will have to filer for memos which
means
you can get create a report if it's only filter for or list a single
quote.
I hope someone can prove me wrong on this.

Patrick

-----Original Message-----
From: Janet Memmelaar [mailto:janet@...]
Sent: Tuesday, July 09, 2002 9:26 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Report Builder Joins


Hi everyone,

Can someome please help me with a RB join for the Memo Test Files. I'm
doing a Quote Follow Up Report for my salespeople and I want to print
specific memos for specific quotes. I am currently getting all memo
text printed with every quote on the report.

Thanks

Janet Memmelaar
Vice-President Information Systems
Metal Cutting Corporation
973-239-1100 x 28



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/links

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/


Yahoo! Groups Sponsor
ADVERTISEMENT



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/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



[Non-text portions of this message have been removed]
At 01:04 PM 7/9/2002 -0400, you wrote:
>did have an off thought on how you could "possibly" make a direct link to
>memo's. It would mean combining some other tools that we've talked about,

There is a good way to do it, but you would need to submit it to Epicor as
an enhancement request.

On Memo records where Memo.Key1 is numeric (or for certain
Memo.RelatedToFile records) store the Key1 data in an additional INTEGER
field. This could easily be done in the write triggers for Memo.

Then you could do a nice simple effecient report builder join like:
QuoteHed.Company -> Memo.Company
"QUOTEHED" -> Memo.RelatedToFile
QuoteHed.QuoteNum -> Memo.IntegerKey1


-Wayne
Really a good Idea Wayne, 'cept by the time such an enhancement gets in,
it'll be Vantage 15, and I'll be sleeping next to Henny Youngman. (old time
comedian, "Take my program, PLEASE")

Shirley

-----Original Message-----
From: Wayne Cox [mailto:wmc20@...]
Sent: Tuesday, July 09, 2002 1:30 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Joins


At 01:04 PM 7/9/2002 -0400, you wrote:
>did have an off thought on how you could "possibly" make a direct link to
>memo's. It would mean combining some other tools that we've talked
about,

There is a good way to do it, but you would need to submit it to Epicor as
an enhancement request.

On Memo records where Memo.Key1 is numeric (or for certain
Memo.RelatedToFile records) store the Key1 data in an additional INTEGER
field. This could easily be done in the write triggers for Memo.

Then you could do a nice simple effecient report builder join like:
QuoteHed.Company -> Memo.Company
"QUOTEHED" -> Memo.RelatedToFile
QuoteHed.QuoteNum -> Memo.IntegerKey1


-Wayne



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/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



[Non-text portions of this message have been removed]
I think I spot a CYNIC ...

-----Original Message-----
From: Shirley Graver [mailto:shirleyg@...]
Sent: Tuesday, July 09, 2002 12:40 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Joins


Really a good Idea Wayne, 'cept by the time such an enhancement gets in,
it'll be Vantage 15, and I'll be sleeping next to Henny Youngman. (old time
comedian, "Take my program, PLEASE")

Shirley

-----Original Message-----
From: Wayne Cox [mailto:wmc20@...]
Sent: Tuesday, July 09, 2002 1:30 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Joins


At 01:04 PM 7/9/2002 -0400, you wrote:
>did have an off thought on how you could "possibly" make a direct link to
>memo's. It would mean combining some other tools that we've talked
about,

There is a good way to do it, but you would need to submit it to Epicor as
an enhancement request.

On Memo records where Memo.Key1 is numeric (or for certain
Memo.RelatedToFile records) store the Key1 data in an additional INTEGER
field. This could easily be done in the write triggers for Memo.

Then you could do a nice simple effecient report builder join like:
QuoteHed.Company -> Memo.Company
"QUOTEHED" -> Memo.RelatedToFile
QuoteHed.QuoteNum -> Memo.IntegerKey1


-Wayne



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/links
<http://groups.yahoo.com/group/vantage/links>

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



[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/.
<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/links
<http://groups.yahoo.com/group/vantage/links>

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .




[Non-text portions of this message have been removed]
Todd I resent that, I AM NOT A NETWORK CARD. Besides I only use 3Com

Shirley

-----Original Message-----
From: Todd Anderson [mailto:tanderson@...]
Sent: Tuesday, July 09, 2002 2:09 PM
To: 'vantage@yahoogroups.com'
Subject: RE: [Vantage] Report Builder Joins


I think I spot a CYNIC ...

-----Original Message-----
From: Shirley Graver [mailto:shirleyg@...]
Sent: Tuesday, July 09, 2002 12:40 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Joins


Really a good Idea Wayne, 'cept by the time such an enhancement gets in,
it'll be Vantage 15, and I'll be sleeping next to Henny Youngman. (old
time
comedian, "Take my program, PLEASE")

Shirley

-----Original Message-----
From: Wayne Cox [mailto:wmc20@...]
Sent: Tuesday, July 09, 2002 1:30 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Joins


At 01:04 PM 7/9/2002 -0400, you wrote:
>did have an off thought on how you could "possibly" make a direct link
to
>memo's. It would mean combining some other tools that we've talked
about,

There is a good way to do it, but you would need to submit it to Epicor
as
an enhancement request.

On Memo records where Memo.Key1 is numeric (or for certain
Memo.RelatedToFile records) store the Key1 data in an additional INTEGER
field. This could easily be done in the write triggers for Memo.

Then you could do a nice simple effecient report builder join like:
QuoteHed.Company -> Memo.Company
"QUOTEHED" -> Memo.RelatedToFile
QuoteHed.QuoteNum -> Memo.IntegerKey1


-Wayne



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/links
<http://groups.yahoo.com/group/vantage/links>

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



[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/.
<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/links
<http://groups.yahoo.com/group/vantage/links>

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .




[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/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



[Non-text portions of this message have been removed]
I'M SORRY. I BELIEVE I HAVE DETECTED SOME LEVITY IN THIS THREAD.

PLEASE CEASE AND DESIST AT ONCE.

THANK YOU.

GOD
----- Original Message -----
From: Shirley Graver
To: vantage@yahoogroups.com
Sent: Tuesday, July 09, 2002 2:35 PM
Subject: RE: [Vantage] Report Builder Joins


Todd I resent that, I AM NOT A NETWORK CARD. Besides I only use 3Com

Shirley




[Non-text portions of this message have been removed]
OK,
This is now a serious thread again. :"[

Since I only have to do it "This ONCE"
Shirley

-----Original Message-----
From: Rick Gors [mailto:rgors@...]
Sent: Tuesday, July 09, 2002 2:51 PM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Report Builder Joins


I'M SORRY. I BELIEVE I HAVE DETECTED SOME LEVITY IN THIS THREAD.

PLEASE CEASE AND DESIST AT ONCE.

THANK YOU.

GOD
----- Original Message -----
From: Shirley Graver
To: vantage@yahoogroups.com
Sent: Tuesday, July 09, 2002 2:35 PM
Subject: RE: [Vantage] Report Builder Joins


Todd I resent that, I AM NOT A NETWORK CARD. Besides I only use 3Com

Shirley




[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/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



[Non-text portions of this message have been removed]
You can accomplish this by doing the following join:

QuoteHed.Company -> Memo.Company
"QUOTEHED" -> Memo.RelatedToFile

Create calculated field that turns the quotehed.quotenum to a trimmed text
string and then adding a filter of where the calculated field is equal to
memo.key1.

Tim

-----Original Message-----
From: Wayne Cox [mailto:wmc20@...]
Sent: Tuesday, July 09, 2002 12:30 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Joins


At 01:04 PM 7/9/2002 -0400, you wrote:
>did have an off thought on how you could "possibly" make a direct link to
>memo's. It would mean combining some other tools that we've talked about,

There is a good way to do it, but you would need to submit it to Epicor as
an enhancement request.

On Memo records where Memo.Key1 is numeric (or for certain
Memo.RelatedToFile records) store the Key1 data in an additional INTEGER
field. This could easily be done in the write triggers for Memo.

Then you could do a nice simple effecient report builder join like:
QuoteHed.Company -> Memo.Company
"QUOTEHED" -> Memo.RelatedToFile
QuoteHed.QuoteNum -> Memo.IntegerKey1


-Wayne




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/links

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
Tim,

I tried a calculated field Called MyQuoteNum
Substring(QuoteNum,">>>>>>")
Also
TRIM(SUBSTRING(QuoteNum,">>>>>>")

but when you create a calculated field converting a numeric field to a
character string the field will not list when creating a join.

My master table is QuoteHead then I'm joining to MEMO like you suggested.

What exactly did your Calculated field look like?

Patrick



-----Original Message-----
From: Tim Ligas [mailto:tligas@...]
Sent: Tuesday, July 09, 2002 2:38 PM
To: 'vantage@yahoogroups.com'
Subject: RE: [Vantage] Report Builder Joins


You can accomplish this by doing the following join:

QuoteHed.Company -> Memo.Company
"QUOTEHED" -> Memo.RelatedToFile

Create calculated field that turns the quotehed.quotenum to a trimmed text
string and then adding a filter of where the calculated field is equal to
memo.key1.

Tim

-----Original Message-----
From: Wayne Cox [mailto:wmc20@...]
Sent: Tuesday, July 09, 2002 12:30 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Joins


At 01:04 PM 7/9/2002 -0400, you wrote:
>did have an off thought on how you could "possibly" make a direct link to
>memo's. It would mean combining some other tools that we've talked about,

There is a good way to do it, but you would need to submit it to Epicor as
an enhancement request.

On Memo records where Memo.Key1 is numeric (or for certain
Memo.RelatedToFile records) store the Key1 data in an additional INTEGER
field. This could easily be done in the write triggers for Memo.

Then you could do a nice simple effecient report builder join like:
QuoteHed.Company -> Memo.Company
"QUOTEHED" -> Memo.RelatedToFile
QuoteHed.QuoteNum -> Memo.IntegerKey1


-Wayne




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/links

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/



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/links

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
Tim,

My mistake you were filtering not trying to join.

Patrick

-----Original Message-----
From: Winter, Patrick [mailto:pjw@...]
Sent: Tuesday, July 09, 2002 2:55 PM
To: 'vantage@yahoogroups.com'
Subject: RE: [Vantage] Report Builder Joins


Tim,

I tried a calculated field Called MyQuoteNum
Substring(QuoteNum,">>>>>>")
Also
TRIM(SUBSTRING(QuoteNum,">>>>>>")

but when you create a calculated field converting a numeric field to a
character string the field will not list when creating a join.

My master table is QuoteHead then I'm joining to MEMO like you suggested.

What exactly did your Calculated field look like?

Patrick



-----Original Message-----
From: Tim Ligas [mailto:tligas@...]
Sent: Tuesday, July 09, 2002 2:38 PM
To: 'vantage@yahoogroups.com'
Subject: RE: [Vantage] Report Builder Joins


You can accomplish this by doing the following join:

QuoteHed.Company -> Memo.Company
"QUOTEHED" -> Memo.RelatedToFile

Create calculated field that turns the quotehed.quotenum to a trimmed text
string and then adding a filter of where the calculated field is equal to
memo.key1.

Tim

-----Original Message-----
From: Wayne Cox [mailto:wmc20@...]
Sent: Tuesday, July 09, 2002 12:30 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Joins


At 01:04 PM 7/9/2002 -0400, you wrote:
>did have an off thought on how you could "possibly" make a direct link to
>memo's. It would mean combining some other tools that we've talked about,

There is a good way to do it, but you would need to submit it to Epicor as
an enhancement request.

On Memo records where Memo.Key1 is numeric (or for certain
Memo.RelatedToFile records) store the Key1 data in an additional INTEGER
field. This could easily be done in the write triggers for Memo.

Then you could do a nice simple effecient report builder join like:
QuoteHed.Company -> Memo.Company
"QUOTEHED" -> Memo.RelatedToFile
QuoteHed.QuoteNum -> Memo.IntegerKey1


-Wayne




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/links

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/



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/links

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/



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/links

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
At 02:38 PM 7/9/2002 -0500, you wrote:
>Create calculated field that turns the quotehed.quotenum to a trimmed text
>string and then adding a filter of where the calculated field is equal to
>memo.key1.

That's the only way to do it currently.

Problem is, the performance gets worse EXPONENTIALLY as your file sizes
grow! This is true for all the files with integer IDs if you try to join
to Memo. If you join that way, then for each item in the main file it has
to go through *every* memo item related to that file. If you have 10,000
POHeader and 15,000 Memo records for POs, then report builder goes through
150,000,000 Memo records to run the report (which takes forever and a day
at that point!) If you can filter it down just on things in the header
file it's not so bad, but still a really slow report in most cases.

I suspect that Epicor (like many other software companies) does not have
any test databases in-house that are anywhere near as large as their
customers'. Many of the tech support documents refer to Dalton Mfg in the
regular (little tiny) training DB; so there's a good chance much of the
Epicor staff doesn't have a very good idea of real world DB
performance. Not trying to slam you guys Tim! Just an honest concern that
I know exists in many big software packages.

-Wayne Cox
Twenty Three -- Information Technology Consulting
828-685-2338
I agree with Wayne. The memo fields are a great place to store information,
but it is extremely difficult to design and run reports. Currently, we use
a memo reports to see the status on all open AR invoices, but it takes over
2 hours to run (and growing). I am not able to make a report work for
orders or POs (other than one at a time) which would be very useful to
Circuit Check. Online documentation (memos) are great, but it doesn't work
well for managers to get an overall picture of what is happening in their
department. It is difficult for me to push users to document issues in
memos when I can't write a reports to extract the information.

Making the memo table easier to write reports against would make the users
use the system to keep track of their area of expertise. Fixing this issue
would be HUGE for us.

Alex Neutgens
Controller
Circuit Check Inc.
Phone: 763.694.4110
Fax: 763.694.4151

-----Original Message-----
From: Wayne Cox [mailto:wmc20@...]
Sent: Tuesday, July 09, 2002 3:04 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Joins


At 02:38 PM 7/9/2002 -0500, you wrote:
>Create calculated field that turns the quotehed.quotenum to a trimmed text
>string and then adding a filter of where the calculated field is equal to
>memo.key1.

That's the only way to do it currently.

Problem is, the performance gets worse EXPONENTIALLY as your file sizes
grow! This is true for all the files with integer IDs if you try to join
to Memo. If you join that way, then for each item in the main file it has
to go through *every* memo item related to that file. If you have 10,000
POHeader and 15,000 Memo records for POs, then report builder goes through
150,000,000 Memo records to run the report (which takes forever and a day
at that point!) If you can filter it down just on things in the header
file it's not so bad, but still a really slow report in most cases.

I suspect that Epicor (like many other software companies) does not have
any test databases in-house that are anywhere near as large as their
customers'. Many of the tech support documents refer to Dalton Mfg in the
regular (little tiny) training DB; so there's a good chance much of the
Epicor staff doesn't have a very good idea of real world DB
performance. Not trying to slam you guys Tim! Just an honest concern that
I know exists in many big software packages.

-Wayne Cox
Twenty Three -- Information Technology Consulting
828-685-2338





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/links

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
We also use memos quite a bit, and I totally agree that when reporting, it
is very cumbersome and slow to run, using report builder. I have created
some reports using ODBC and MSAccess, which work well and run fairly
quickly. If it is available, you may want to try it...



[Non-text portions of this message have been removed]
So you can join a numeric and character field with MSACCESS which we cannot
in Report Builder?

-----Original Message-----
From: sarah.vareschi@... [mailto:sarah.vareschi@...]
Sent: Tuesday, July 09, 2002 3:32 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Joins


We also use memos quite a bit, and I totally agree that when reporting, it
is very cumbersome and slow to run, using report builder. I have created
some reports using ODBC and MSAccess, which work well and run fairly
quickly. If it is available, you may want to try it...



[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/links

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/