Help with SQL statement- Supplier List cost into cost field in Ite

Posted by Tara on February 12, 2008, 9:46 am

Please log in for more thread options

Hi, I've a customer who has a number of items in their HQ database at zero
cost. However, for these items there is a cost price detailed on the Supplier
tab. Items carried only have one supplier. They'd like to get this cost
copied into the cost price field on the General tab and because we're talking
about a few thousand items, this isn't something they want to do by hand! I'm
familiar to a point with SQL but because we're talking two seperate tables, I
really wouldn't be sure where to start with the syntax. Could someone please
point me in the right direction? Or if there's an easier way using Wizards
etc, even better! Thanks!

Posted by convoluted on February 12, 2008, 2:27 pm

Please log in for more thread options

Hi Tara - backup your db first....

first run
select * from supplierlist order by cost asc
-- this will show you the contents in the supplierlist table first listing
items with zero cost (or the lowest cost) - fix zero cost entries first by
editing item properties and setting the correct cost in the supplier tab

then run
update item
set item.cost = supplierlist.cost
from item inner join supplierlist on item.id = supplierlist.itemid
-- the inner join will only update items that are both in the item table and
supplierlist table

to see items that are not in the supplier list table run
select itemlookupcode, description
from item
where id not in (select itemid from supplierlist)

Hope this helps....

"Tara" wrote:

> Hi, I've a customer who has a number of items in their HQ database at zero

> cost. However, for these items there is a cost price detailed on the Supplier

> tab. Items carried only have one supplier. They'd like to get this cost

> copied into the cost price field on the General tab and because we're talking

> about a few thousand items, this isn't something they want to do by hand! I'm

> familiar to a point with SQL but because we're talking two seperate tables, I

> really wouldn't be sure where to start with the syntax. Could someone please

> point me in the right direction? Or if there's an easier way using Wizards

> etc, even better! Thanks!

Posted by Tara on February 14, 2008, 5:01 am

Please log in for more thread options

Hi, thanks for that- it worked perfectly. There is just one snag however and
I didn't think of it until I was testing. Many of the suppliers use specific
currencies so the information I need copied from the Supplier tab is actually
the Local Cost. When I view the SupplierList table, Local Cost doesn't
appear as a column. Is it just a calculation based on exchange rates for
information or does it actually exist somewhere within the database? Your
help once again would be much appreciated.

T.

"convoluted" wrote:

> Hi Tara - backup your db first....

> first run

> select * from supplierlist order by cost asc

> -- this will show you the contents in the supplierlist table first listing

> items with zero cost (or the lowest cost) - fix zero cost entries first by

> editing item properties and setting the correct cost in the supplier tab

> then run

> update item

> set item.cost = supplierlist.cost

> from item inner join supplierlist on item.id = supplierlist.itemid

> -- the inner join will only update items that are both in the item table and

> supplierlist table

> to see items that are not in the supplier list table run

> select itemlookupcode, description

> from item

> where id not in (select itemid from supplierlist)

> Hope this helps....

> "Tara" wrote:

> > Hi, I've a customer who has a number of items in their HQ database at zero

> > cost. However, for these items there is a cost price detailed on the

Supplier

> > tab. Items carried only have one supplier. They'd like to get this cost

> > copied into the cost price field on the General tab and because we're

talking

> > about a few thousand items, this isn't something they want to do by hand!

I'm

> > familiar to a point with SQL but because we're talking two seperate tables,

> > really wouldn't be sure where to start with the syntax. Could someone please

> > point me in the right direction? Or if there's an easier way using Wizards

> > etc, even better! Thanks!