Hi Kris,
Vendor information can be pretty confusing when trying to connect the
tables.
There are three tables involved.
1. Vendor table: contains Vendor information, including the default
(generic) contact address and an optional PurPoint field (a 4-digit string).
The Primary Purchase Point record selected in the "Purchase Points..."
button determines the value of this 4-digit field. If there is no Primary
Purchase Point record selected, then this field is blank (and the generic
contact address is used on PO forms).
2. VendorPP table: must always contain at least one record with the same
address as the default (generic) contact address for the Vendor. If a user
adds a Purchase Point in the Vendor Maintenance (using the "Purchase
Points..." button), then there will also be a second record in the VendorPP
table for that Vendor. The first record is the blank PurPoint record with
the default (generic) contact address as seen in the opening screen of the
Vendor Maintenance Screen. The second record contains the PurPoint string
(called the Purchase Point). There can be multiple purchase points for each
vendor, but only one can be the Primary Purchase Point (although you don't
have to have any of them be primary). Contacts can also be added to a
Purchase Point. These contacts are stored in the VendCnt table, expained
below.
3. VendCnt table: This table contains all of the contact people for a
vendor, including their function and phone numbers. Contacts can be added
directly from the main Vendor Maintenance "Contacts" button, or from the
Purchase Points/Update/"Contacts" button. If the contact was added by the
former, then the VendCnt.PurPoint field will be blank for the record. If
the contact was added to one of the Purchase Points, then the
VendCnt.PurPoint will contain the corresponding PurPoint value. The
VendCnt.ConNum is a numeric value showing the position of the record in the
list of contacts (ie. 1 for the first contact, 2 for the second contact,
etc.). This can be used to link to the Vendor.PrimPCon field in the Vendor
table to select only the primary contact in that set of records.
LINKING ALL THIS INFORMATION:
To get the Purchase Point address of a Vendor, simply link the Vendor
Table to the VendorPP table using three fields: Company, VendorNum, and
PurPoint as your linked fields.
If you only want to see the list of Contacts for the Vendor, then you only
need the Vendor table and the VendCnt table, linking by with the fields:
company, VendorNum. In this case, there will possibly be multiple records
for each vendor. Alternately, if you have a primary Purchase Point for the
Vendor, you may want to see the contacts for only that Purchase Point
instead. In this case, also add the PurPoint field as a link.
AFTER THAT IT GETS MORE COMPLICATED
If you want the contacts AND the address for a Vendor, and you are using
only the purchase points as their associated contacts, then you can link
Vendor --> VendorPP --> VendCnt (using Company, VendorNum, and PurPoint).
However, if you also want to see contacts that are not associated with a
Purchase point, but want to also see your Primary Purchase Point address,
you are going to have a BIG MESS. You will probably get multiple records
showing for each vendor and for each contact (or PurPoint) depending on how
you link. Or, you will not see all your records. What I would try in this
case is to link Vendor --> VendorPP (using Company, VendorNum, and PurPoint)
and then link Vendor --> VendCnt (using Customer and Vend.Num). This may
work if you have only one contact. If you have more, then you're going to
be duplicating records again. It may be possible to refine this more by
linking the Vendor.PrimPCon to VendCnt.ConNum. This would show only the
primary contact as long as there wasn't a corresponding number of contacts
in the purchase point as well. I know it's confusing. It has taken me a
long time to understand how it all relates together.
Hope this helps, my brain if fried trying to explain it all. I just happen
to be studying the Vendor information for some Vendor performance reports
that I have been working on.
Troy Funte
Liberty Electronics
-----Original Message-----
From: Vantage List [mailto:vlist@...]
Sent: Monday, April 28, 2003 11:39 AM
To: 'vantage@yahoogroups.com'
Subject: [Vantage] Vendor Contact Info
Can someone give me the quick RB joins for Vendor Purchase Point contact
information ? I am getting incorrect contact information and do not know
if
I have a problem in the database or a problem with my joins. I have tried
several things but, get the same result. I want to print a supplier report
card and reference the address of the Purchase Point and the primary
contact
linked to it.
Thanks for any help.
Kris Pollard
American Turned Products
Yahoo! Groups Sponsor
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]
Vendor information can be pretty confusing when trying to connect the
tables.
There are three tables involved.
1. Vendor table: contains Vendor information, including the default
(generic) contact address and an optional PurPoint field (a 4-digit string).
The Primary Purchase Point record selected in the "Purchase Points..."
button determines the value of this 4-digit field. If there is no Primary
Purchase Point record selected, then this field is blank (and the generic
contact address is used on PO forms).
2. VendorPP table: must always contain at least one record with the same
address as the default (generic) contact address for the Vendor. If a user
adds a Purchase Point in the Vendor Maintenance (using the "Purchase
Points..." button), then there will also be a second record in the VendorPP
table for that Vendor. The first record is the blank PurPoint record with
the default (generic) contact address as seen in the opening screen of the
Vendor Maintenance Screen. The second record contains the PurPoint string
(called the Purchase Point). There can be multiple purchase points for each
vendor, but only one can be the Primary Purchase Point (although you don't
have to have any of them be primary). Contacts can also be added to a
Purchase Point. These contacts are stored in the VendCnt table, expained
below.
3. VendCnt table: This table contains all of the contact people for a
vendor, including their function and phone numbers. Contacts can be added
directly from the main Vendor Maintenance "Contacts" button, or from the
Purchase Points/Update/"Contacts" button. If the contact was added by the
former, then the VendCnt.PurPoint field will be blank for the record. If
the contact was added to one of the Purchase Points, then the
VendCnt.PurPoint will contain the corresponding PurPoint value. The
VendCnt.ConNum is a numeric value showing the position of the record in the
list of contacts (ie. 1 for the first contact, 2 for the second contact,
etc.). This can be used to link to the Vendor.PrimPCon field in the Vendor
table to select only the primary contact in that set of records.
LINKING ALL THIS INFORMATION:
To get the Purchase Point address of a Vendor, simply link the Vendor
Table to the VendorPP table using three fields: Company, VendorNum, and
PurPoint as your linked fields.
If you only want to see the list of Contacts for the Vendor, then you only
need the Vendor table and the VendCnt table, linking by with the fields:
company, VendorNum. In this case, there will possibly be multiple records
for each vendor. Alternately, if you have a primary Purchase Point for the
Vendor, you may want to see the contacts for only that Purchase Point
instead. In this case, also add the PurPoint field as a link.
AFTER THAT IT GETS MORE COMPLICATED
If you want the contacts AND the address for a Vendor, and you are using
only the purchase points as their associated contacts, then you can link
Vendor --> VendorPP --> VendCnt (using Company, VendorNum, and PurPoint).
However, if you also want to see contacts that are not associated with a
Purchase point, but want to also see your Primary Purchase Point address,
you are going to have a BIG MESS. You will probably get multiple records
showing for each vendor and for each contact (or PurPoint) depending on how
you link. Or, you will not see all your records. What I would try in this
case is to link Vendor --> VendorPP (using Company, VendorNum, and PurPoint)
and then link Vendor --> VendCnt (using Customer and Vend.Num). This may
work if you have only one contact. If you have more, then you're going to
be duplicating records again. It may be possible to refine this more by
linking the Vendor.PrimPCon to VendCnt.ConNum. This would show only the
primary contact as long as there wasn't a corresponding number of contacts
in the purchase point as well. I know it's confusing. It has taken me a
long time to understand how it all relates together.
Hope this helps, my brain if fried trying to explain it all. I just happen
to be studying the Vendor information for some Vendor performance reports
that I have been working on.
Troy Funte
Liberty Electronics
-----Original Message-----
From: Vantage List [mailto:vlist@...]
Sent: Monday, April 28, 2003 11:39 AM
To: 'vantage@yahoogroups.com'
Subject: [Vantage] Vendor Contact Info
Can someone give me the quick RB joins for Vendor Purchase Point contact
information ? I am getting incorrect contact information and do not know
if
I have a problem in the database or a problem with my joins. I have tried
several things but, get the same result. I want to print a supplier report
card and reference the address of the Purchase Point and the primary
contact
linked to it.
Thanks for any help.
Kris Pollard
American Turned Products
Yahoo! Groups Sponsor
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]