Crystal logic question

Yahoo Archive
1
I think I'm missing something... I still can't get it to work.

If anyone is willing to help out, please e-mail me off line
mhow@...

Thanks!

--- In vantage@yahoogroups.com, "Ace_Man" <ace_man@...> wrote:
>
> Yes that worked; I inserted a group by Customer and have the detail
> section suppressed with this formula:{InvcHead.OpenInvoice} = False
>
> I hope this helps with the original poster, because it sure helped
> me :)
>
>
2
Hi, all...

I am writing a report for the sales department. The tables I am using
are Cust, InvcDtl, and InvcHead. The report shows all our sales for
particular product groups in a specific time frame.

Here's the catch... they want me to include in this report all the
customers that did NOT have any sales. Can that even be done?

If anyone has any ideas, please share!

Thanks!

Missi
3
You sure can. Make your join from Customer table to Invc Head a Left
Outer Join. You will get a list of all customers and any related
invoices.

HTH,

Brian Stenglein
Clow Stamping Co.


--- In vantage@yahoogroups.com, "how2wow" <mhow@...> wrote:
>
> Hi, all...
>
> I am writing a report for the sales department. The tables I am
using
> are Cust, InvcDtl, and InvcHead. The report shows all our sales
for
> particular product groups in a specific time frame.
>
> Here's the catch... they want me to include in this report all the
> customers that did NOT have any sales. Can that even be done?
>
> If anyone has any ideas, please share!
>
> Thanks!
>
> Missi
>
4
That doesn't seem to work... I wonder if it is because I have the
invoice num linked between the invoice header and detail files and
that overrides it?

--- In vantage@yahoogroups.com, "Brian Stenglein" <bstenglein@...>
wrote:
>
> You sure can. Make your join from Customer table to Invc Head a
Left
> Outer Join. You will get a list of all customers and any related
> invoices.
>
> HTH,
>
> Brian Stenglein
> Clow Stamping Co.
>
>
5
I am having the same result:
Customer Table Left Outer Joined to InvcHead Table by just the
Company and CustNum Fields. I get the same result as Equal Join.
Really weird.

--- In vantage@yahoogroups.com, "how2wow" <mhow@...> wrote:
>
> That doesn't seem to work... I wonder if it is because I have the
> invoice num linked between the invoice header and detail files and
> that overrides it?
>
> --- In vantage@yahoogroups.com, "Brian Stenglein" <bstenglein@>
> wrote:
> >
> > You sure can. Make your join from Customer table to Invc Head a
> Left
> > Outer Join. You will get a list of all customers and any
related
> > invoices.
> >
> > HTH,
> >
> > Brian Stenglein
> > Clow Stamping Co.
> >
> >
>
6
Hello,

Not certain which version your on. I think you need to insert the
orderhead table between Cust & Invcdtl, and then do a left outer join
between customers and orders.

Dan Shallbetter
States Electric Mfg.


________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of how2wow
Sent: Friday, May 12, 2006 8:36 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Crystal logic question


Hi, all...

I am writing a report for the sales department. The tables I am using
are Cust, InvcDtl, and InvcHead. The report shows all our sales for
particular product groups in a specific time frame.

Here's the catch... they want me to include in this report all the
customers that did NOT have any sales. Can that even be done?

If anyone has any ideas, please share!

Thanks!

Missi









[Non-text portions of this message have been removed]
7
I am not sure about crystal, but it would be pretty simple to do in MS
Access using sub queries...

In access... first, create a query that inner joins invchead to invcdtl,
filtered by the date range you want, and add all the fields that you want to
use in your report (make sure you also include Company and Custnum as fields
for the next step).

Then, make another query that left joins customer table to your existing
query and add all the fields you want on your report to this query. Then
write your report off this query.

There is probably something similar you can do in crystal, but I am not
familiar enough with Crystal for this type of thing.

-Sarah


[Non-text portions of this message have been removed]
8
I was able to get all the customers using Left Outer Join from
Customer Table to InvcHead Table, with or without invoices against
them, if I don't use a selection criteria. Which means that you'll
get all invoices; Open and Close. When I set a criteria with
InvcHead.OpenInvoice set to True, Crystal ignored my Left Outer Join
and I lost all the customers that didn't have any invoice.

Hope this helps


--- In vantage@yahoogroups.com, "Brian Stenglein" <bstenglein@...>
wrote:
>
> You sure can. Make your join from Customer table to Invc Head a
Left
> Outer Join. You will get a list of all customers and any related
> invoices.
>
> HTH,
>
> Brian Stenglein
> Clow Stamping Co.
>
>
> --- In vantage@yahoogroups.com, "how2wow" <mhow@> wrote:
> >
> > Hi, all...
> >
> > I am writing a report for the sales department. The tables I am
> using
> > are Cust, InvcDtl, and InvcHead. The report shows all our sales
> for
> > particular product groups in a specific time frame.
> >
> > Here's the catch... they want me to include in this report all
the
> > customers that did NOT have any sales. Can that even be done?
> >
> > If anyone has any ideas, please share!
> >
> > Thanks!
> >
> > Missi
> >
>
9
You might have been able to get around this by using a negative filter...
NOT InvcHead.OpenInvoice = FALSE.
In effect you must have a invoice head for any part of it to be true, but
for anything that does "not" match might work.
This is just a theory, so if someone knows otherwise, please correct for the
greater good.
Good luck,
Aaron Hoyt
Vantage Plastics

-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]On Behalf
Of Ace_Man
Sent: Friday, May 12, 2006 4:10 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Crystal logic question


I was able to get all the customers using Left Outer Join from
Customer Table to InvcHead Table, with or without invoices against
them, if I don't use a selection criteria. Which means that you'll
get all invoices; Open and Close. When I set a criteria with
InvcHead.OpenInvoice set to True, Crystal ignored my Left Outer Join
and I lost all the customers that didn't have any invoice.

Hope this helps
10
Tried it and still, lost all the Customers that didn't have any
Invoice.


--- In vantage@yahoogroups.com, "Aaron Hoyt" <aaron.hoyt@...> wrote:
>
> You might have been able to get around this by using a negative
filter...
> NOT InvcHead.OpenInvoice = FALSE.
> In effect you must have a invoice head for any part of it to be
true, but
> for anything that does "not" match might work.
> This is just a theory, so if someone knows otherwise, please
correct for the
> greater good.
> Good luck,
> Aaron Hoyt
> Vantage Plastics
>
> -----Original Message-----
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]On
Behalf
> Of Ace_Man
> Sent: Friday, May 12, 2006 4:10 PM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Re: Crystal logic question
>
>
> I was able to get all the customers using Left Outer Join from
> Customer Table to InvcHead Table, with or without invoices against
> them, if I don't use a selection criteria. Which means that you'll
> get all invoices; Open and Close. When I set a criteria with
> InvcHead.OpenInvoice set to True, Crystal ignored my Left Outer
Join
> and I lost all the customers that didn't have any invoice.
>
> Hope this helps
>
11
Your filter logic need to be: InvcHead.OpenInvoice set to True or
InvcHead.OpenInvoice set to Unknown


Patrick Winter

-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
Ace_Man
Sent: Monday, May 15, 2006 8:54 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Crystal logic question

Tried it and still, lost all the Customers that didn't have any Invoice.


--- In vantage@yahoogroups.com, "Aaron Hoyt" <aaron.hoyt@...> wrote:
>
> You might have been able to get around this by using a negative
filter...
> NOT InvcHead.OpenInvoice = FALSE.
> In effect you must have a invoice head for any part of it to be
true, but
> for anything that does "not" match might work.
> This is just a theory, so if someone knows otherwise, please
correct for the
> greater good.
> Good luck,
> Aaron Hoyt
> Vantage Plastics
>
> -----Original Message-----
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]On
Behalf
> Of Ace_Man
> Sent: Friday, May 12, 2006 4:10 PM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Re: Crystal logic question
>
>
> I was able to get all the customers using Left Outer Join from
> Customer Table to InvcHead Table, with or without invoices against
> them, if I don't use a selection criteria. Which means that you'll
> get all invoices; Open and Close. When I set a criteria with
> InvcHead.OpenInvoice set to True, Crystal ignored my Left Outer
Join
> and I lost all the customers that didn't have any invoice.
>
> Hope this helps
>








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
Yahoo! Groups Links






This e-mail and any attachments may contain confidential and
privileged information. If you are not the intended recipient,
please notify the sender immediately by return e-mail, delete this
e-mail and destroy any copies. Any dissemination or use of this
information by a person other than the intended recipient is
unauthorized and may be illegal.
12
What I do in this case is I use a Suppression Formula on the Details
section to eliminate those records.




Andrew Reed
Crestview Aerospace Corp.

________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Ace_Man
Sent: Friday, May 12, 2006 3:10 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Crystal logic question


I was able to get all the customers using Left Outer Join from
Customer Table to InvcHead Table, with or without invoices against
them, if I don't use a selection criteria. Which means that you'll
get all invoices; Open and Close. When I set a criteria with
InvcHead.OpenInvoice set to True, Crystal ignored my Left Outer Join
and I lost all the customers that didn't have any invoice.

Hope this helps


--- In vantage@yahoogroups.com, "Brian Stenglein" <bstenglein@...>
wrote:
>
> You sure can. Make your join from Customer table to Invc Head a
Left
> Outer Join. You will get a list of all customers and any related
> invoices.
>
> HTH,
>
> Brian Stenglein
> Clow Stamping Co.
>
>
> --- In vantage@yahoogroups.com, "how2wow" <mhow@> wrote:
> >
> > Hi, all...
> >
> > I am writing a report for the sales department. The tables I am
> using
> > are Cust, InvcDtl, and InvcHead. The report shows all our sales
> for
> > particular product groups in a specific time frame.
> >
> > Here's the catch... they want me to include in this report all
the
> > customers that did NOT have any sales. Can that even be done?
> >
> > If anyone has any ideas, please share!
> >
> > Thanks!
> >
> > Missi
> >
>



[Non-text portions of this message have been removed]
13
Yes that worked; I inserted a group by Customer and have the detail
section suppressed with this formula:{InvcHead.OpenInvoice} = False

I hope this helps with the original poster, because it sure helped
me :)


--- In vantage@yahoogroups.com, "Andrew Reed" <areed@...> wrote:
>
> What I do in this case is I use a Suppression Formula on the
Details
> section to eliminate those records.
>
>
>
>
> Andrew Reed
> Crestview Aerospace Corp.
>
> ________________________________
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On
Behalf
> Of Ace_Man
> Sent: Friday, May 12, 2006 3:10 PM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Re: Crystal logic question
>
>
> I was able to get all the customers using Left Outer Join from
> Customer Table to InvcHead Table, with or without invoices against
> them, if I don't use a selection criteria. Which means that you'll
> get all invoices; Open and Close. When I set a criteria with
> InvcHead.OpenInvoice set to True, Crystal ignored my Left Outer
Join
> and I lost all the customers that didn't have any invoice.
>
> Hope this helps
>
>
> --- In vantage@yahoogroups.com, "Brian Stenglein" <bstenglein@>
> wrote:
> >
> > You sure can. Make your join from Customer table to Invc Head a
> Left
> > Outer Join. You will get a list of all customers and any
related
> > invoices.
> >
> > HTH,
> >
> > Brian Stenglein
> > Clow Stamping Co.
> >
> >
> > --- In vantage@yahoogroups.com, "how2wow" <mhow@> wrote:
> > >
> > > Hi, all...
> > >
> > > I am writing a report for the sales department. The tables I
am
> > using
> > > are Cust, InvcDtl, and InvcHead. The report shows all our
sales
> > for
> > > particular product groups in a specific time frame.
> > >
> > > Here's the catch... they want me to include in this report all
> the
> > > customers that did NOT have any sales. Can that even be done?
> > >
> > > If anyone has any ideas, please share!
> > >
> > > Thanks!
> > >
> > > Missi
> > >
> >
>
>
>
> [Non-text portions of this message have been removed]
>