Query Help for No Vendor Associated with Items

SELECT * FROM ITEM WHERE SupplierID=0

- Evan Culver New West Technologies

Thanks.

I imported my Database and used > SELECT * FROM ITEM WHERE SupplierID=0

Thanks.

I imported my Database and used

Supplier you wish.

Bill,

Are you sure that the SupplierID in the SupplierList is a valid ID number of a supplier?

Is your import routine putting a number in the SupplierID in the Item table?

Like I said before, you _should_ be looking at the ID number in the Supplier table to confirm the correct one.

Understand what relationships you have/need;

The Supplier table has the complete info about who the supplier is. That ID number is the identifier for that one supplier.

The Item table's SupplierID references the Primary supplier ID of that item. The SupplierID relates back to the Supplier table.

The SupplierList table is the supplier's re-order number, price, minimum buy and MPQ for that item. There may be more than one entry per item. The SupplierID, again, relates back to the Supplier table. The ItemID relates back to the Item table.

When I try to run the query, I get en error depending on the ILC that I use. IE: CIR-ILC CF0161-8RH returns Line 1: Incorrect syntax near 'RH', ILC BRU-59104142 returns Invalid colum name 'BRU'

I use the first three to identify the manufacturer, then the mfg p/n. If there are options like shoe size and style (right or left hand) I use the 8RH or what ever the size and style is.

Bill

"Jeff" wrote:

Right now we are attempting to find out who the Primary Supplier is for a particular ILC.

item purchased from your Supplier in question?

correctly, otherwise it will show 0 for the SupplierID.

Right now we are attempting to find out who the Primary Supplier is for a particular ILC.

for an item purchased from your Supplier in question?

correctly, otherwise it will show 0 for the SupplierID.

Hi Jeff,

I ran the query and it does show the correct vendor name. I did the database check and re-indexed successfully.

When I check the ILC again, nothing still is in the Supplier Tab.

Anyother suggesti> Bill,

correct supplier and note the ID. That's the number you want to use in your import.as your SupplierID.

database check and re-index in SO Admin.

CF0161-8RH'

SupplierList! Right now we are attempting to find out who the Primary Supplier is for a particular ILC.

correctly, otherwise it will show 0 for the SupplierID.

number of

the Supplier

that item.

minimum buy

message

message

the database

select sl.* from supplierlist sl inner join item i on sl.itemid = i.id where i.itemlookupcode = 'myILCGoesHere'

Glenn Adams Tiber Creek C> Hi Jeff,

correct supplier and note the ID. That's the number you want to use in your import.as your SupplierID.

database check and re-index in SO Admin.

SupplierList! Right now we are attempting to find out who the Primary Supplier is for a particular ILC.

correctly, otherwise it will show 0 for the SupplierID.

directly into SO

ID number of

Item table?

the Supplier

is. That ID

that item.

minimum buy

Supplier table of

newsgroup reader

newsreader"

message

number when I

Manager, there is

message

the database

correct supplier and note the ID. That's the number you want to use in your import.as your SupplierID.

database check and re-index in SO Admin.

SupplierList! Right now we are attempting to find out who the Primary Supplier is for a particular ILC.

imported correctly, otherwise it will show 0 for the SupplierID.

is in the

supplier. It is

directly into SO

ID number of

the Item table?

the Supplier

is. That ID

of that item.

price, minimum buy

ItemID relates

Supplier table of

newsgroup reader

with newsreader"

message

number when I

Manager, there is

message

news: snipped-for-privacy@microsoft.com...

the database

just do the test item that you've been looking at. If you get results, then suppliers should be listed in the Item Properties.

Glenn Adams Tiber Creek C> Do I have to do this for each ILC? I have over 6000.

correct supplier and note the ID. That's the number you want to use in your import.as your SupplierID.

database check and re-index in SO Admin.

SupplierList! Right now we are attempting to find out who the Primary Supplier is for a particular ILC.

imported correctly, otherwise it will show 0 for the SupplierID.

is in the

supplier. It is

directly into SO

ID number of

the Item table?

the Supplier

is. That ID

of that item.

price, minimum buy

ItemID relates

Supplier table of

newsgroup reader

with newsreader"

message

number when I

Manager, there is

message

news: snipped-for-privacy@microsoft.com...

the database

select i.itemlookupcode, i.description from item i left join supplierlist sl on i.id = supplierlist.itemID where sl.id is null

That will return all items that have no matching records in Supplier List.

Glenn Adams Tiber Creek C> just do the test item that you've been looking at. If you get results,

When I run the test, I see the column headings but nothing shows up. When I go back into SO Manager, Supplier Tab, nothing their either.

I ran the sec> select i.itemlookupcode, i.description

Sorry, here's the corrected query to display items with no supplier list entries.

select i.itemlookupcode, i.description from item i left join supplierlist sl on i.id = sl.itemID where sl.id is null

If you ran the first query and got no results, that is why there are no suppliers listed in the properties window. Even the default supplier needs an entry in SupplierList.

Here's a query that will add any suppliers that are set up in Item.SupplierID, but are not in Supplier List.

ALWAYS BACKUP YOUR DATABASE BEFORE RUNNING ANY UPDATE, INSERT OR DELETE QUERY!

INSERT INTO supplierlist (MinimumOrder, ItemID, SupplierID, Cost, ReorderNumber, MasterPackQuantity, TaxRate) SELECT MinimumOrder = 0.00, ItemID = i.id, SupplierID = s.id, Cost = i.cost, ReorderNumber = '', MasterPackQuantity = 0, TaxRate = 0.00 FROM item i inner join supplier s on i.supplierid = s.id left join supplierlist sl on i.id = sl.itemid AND s.id = sl.supplierid WHERE sl.id is null AND i.supplierid 0

Glenn Adams Tiber Creek C> When I run the test, I see the column headings but nothing shows up. When I

Thanks Glenn that worked.

Next questi> Sorry, here's the corrected query to display items with no supplier list > entries. >