Open PO report

Yahoo Archive
1
Another handy trick I've found for figuring out joins is using the Export Utility in Vantage. If you go through the Query Wizard and select two tables, it will do the joins for you and it will usually grab and joined the indexed fields (including company) first. I've used it to help me find mystery tables. Once you pick the first table, it shortens the list considerably of tables that you can join with. This doesn't mean you CAN'T join to the tables that it drops off the list, but it lets you know the primary tables that you are likely to WANT to join to the first table.

Troy Funte
Liberty Electronics

----- Original Message -----
From: Leonard, Jeremy
To: 'vantage@yahoogroups.com'
Sent: Monday, June 10, 2002 10:40 AM
Subject: RE: [Vantage] Open PO report


Thanks allot Wayne. I think this will be very helpful. I have had allot of
speed issues with ODBC. I think this might be the problem. With SQL Server
you don't have to worry about indexes. SQL does all the work for you. Just
another example of a very relevant and helpful solution that I have seen no
documentation on.
Thanks
Jeremy Leonard
IT Manager
K-T Corporation


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]
2
What field do I need to use to filter for only Open PO's? I have joined
Vendor to PoDetail and PoDetail to PORel.
I tried filtering PoRel.OpenRelease = Yes but still got some old closed
po's. They show up as closed in POTracker. Any better solution?

Thanks.

Jim Moore
GD-OTS
3
Thats a strange one. I would think that your query would be correct. Try
including PoHeader.OpenOrder=Yes. Something may have happened that a
release is marked as open but the PO is closed.

I have seen much stranger things in Vantage.

Thanks
Jeremy Leonard
IT Manager
K-T Corporation


-----Original Message-----
From: Moore, Jim (Anniston) [mailto:jmoore@...-ots.com]
Sent: Thursday, June 06, 2002 1:11 PM
To: 'vantage@yahoogroups.com'
Subject: [Vantage] Open PO report


What field do I need to use to filter for only Open PO's? I have joined
Vendor to PoDetail and PoDetail to PORel.
I tried filtering PoRel.OpenRelease = Yes but still got some old closed
po's. They show up as closed in POTracker. Any better solution?

Thanks.

Jim Moore
GD-OTS





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/
4
Out of curiosity I ran this query on our database.

SELECT POHeader.OpenOrder, PORel.OpenRelease, POHeader.PONum
FROM (POHeader INNER JOIN PODetail ON POHeader.PONum = PODetail.PONUM) INNER
JOIN PORel ON (PODetail.POLine = PORel.POLine) AND (PODetail.PONUM =
PORel.PONum)
WHERE (((POHeader.OpenOrder)=No) AND ((PORel.OpenRelease)=Yes));

I was surprised to see that we too have closed PO's with open releases.
Without further investigation, I cant tell you why.

Hope that helps
Jeremy Leonard
IT Manager
K-T Corporation
5
Thanks to all, on and offline. I have a report that now does what the user
needed.
It seems that some po's were not closed out correctly or maybe somehow
reopened and not closed.

Thanks.

Jim Moore
GD-OTS

-----Original Message-----
From: Leonard, Jeremy [mailto:jleonard@...]
Sent: Thursday, June 06, 2002 1:28 PM
To: 'vantage@yahoogroups.com'
Subject: RE: [Vantage] Open PO report


Out of curiosity I ran this query on our database.

SELECT POHeader.OpenOrder, PORel.OpenRelease, POHeader.PONum
FROM (POHeader INNER JOIN PODetail ON POHeader.PONum = PODetail.PONUM) INNER
JOIN PORel ON (PODetail.POLine = PORel.POLine) AND (PODetail.PONUM =
PORel.PONum)
WHERE (((POHeader.OpenOrder)=No) AND ((PORel.OpenRelease)=Yes));

I was surprised to see that we too have closed PO's with open releases.
Without further investigation, I cant tell you why.

Hope that helps
Jeremy Leonard
IT Manager
K-T Corporation


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/
6
At 01:21 PM 6/6/02 -0500, you wrote:
>Thats a strange one. I would think that your query would be correct. Try
>including PoHeader.OpenOrder=Yes. Something may have happened that a
>release is marked as open but the PO is closed.

In the DB Conversions menu, there is one titled something like "Fix PO
Status Flags" that may fix them up. Including POHeader in the join and
filter is probably a good idea anyway.

BTW, In case you weren't aware, you should also be including the Company
field in all your joins and filters for performance. Otherwise it can't
work from the indexes and has to do it brute force.

-Wayne Cox
7
Thanks Wayne. I wasnt aware of that. So I need to include just the company
name plus what ever fields I need or are there a number of fields I need to
include to properly run my query.
Thanks for the help
Jeremy Leonard
IT Manager
K-T Corporation


-----Original Message-----
From: Wayne Cox [mailto:wmc20@...]
Sent: Thursday, June 06, 2002 3:42 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Open PO report


At 01:21 PM 6/6/02 -0500, you wrote:
>Thats a strange one. I would think that your query would be correct. Try
>including PoHeader.OpenOrder=Yes. Something may have happened that a
>release is marked as open but the PO is closed.

In the DB Conversions menu, there is one titled something like "Fix PO
Status Flags" that may fix them up. Including POHeader in the join and
filter is probably a good idea anyway.

BTW, In case you weren't aware, you should also be including the Company
field in all your joins and filters for performance. Otherwise it can't
work from the indexes and has to do it brute force.

-Wayne Cox



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/
8
At 07:44 PM 6/7/2002 -0500, you wrote:
>So I need to include just the company name plus what ever fields I need or
>are there a number of fields I need to include to properly run my query.

If you check the data dictionary for the indexes on each file, you'll see
the indexes are multiple fields concatenated together. The first field is
always Company. Eg: one of the indexes on POHeader is something like
Comapny*PONum*OpenOrder

Think of it like the phone book - it's indexed on
LastName*FirstName. While FirstName is indexed, it doesn't do you much
good without the other index components to its left (LastName). So anytime
you're doing a join, you should look for indexes with matching component
fields going from left to right.

It's similar with filters; if you have an index like Company*OpenOrder
then you need to specify "Company = 'ABC' AND OpenOrder = true" for report
builder to use that index. If you only specify "OpenOrder = true" it
still has to sequentially scan the entire file and examine the OpenOrder
field on every record.

-Wayne Cox
9
Thanks allot Wayne. I think this will be very helpful. I have had allot of
speed issues with ODBC. I think this might be the problem. With SQL Server
you don't have to worry about indexes. SQL does all the work for you. Just
another example of a very relevant and helpful solution that I have seen no
documentation on.
Thanks
Jeremy Leonard
IT Manager
K-T Corporation