Rank: Member
Joined: 8/25/2007(UTC)
Posts: 124
|
Google Base (ProductSearch ) requires product variation items to have a price in the BV Commerce General Options (Price). Ours are currently blank! And I am not sure how to populate with out manually going in to all 5000 items.
Catalog --> Edit Product --> General Options Tab --> Field: Price
We have left this price blank because we do not have a SKU number for the general item.
Customers can only order the item with a variation.
When our pricing changes I run a routine in SQL to change our sell prioces on supported SKUs
Does anyone know how I can run a routine to have BV Commerce automatically populate the General Options Tab --> Field: Price based on the lowest Product Variation Cost?
Base Item (No Price)
--> Product Variation 1 --> SKU 0001 (Price $10.00)
--> Product Variation 2 --> SKU 0002 (Price $11.00)
--> Product Variation 3 --> SKU 0003 (Price $12.00)
Can I write a SQL routine to do this? Need a script to update Base Item Sell Price to $10.00 (Based on above example). I need to do this for all items that have a Product Variation.
I have about 140 items I need to do this to. But in the future I will have more.... With price changes, etc... I want to automate this.
Ideas?
Google Base also needed a description in the base item, but we resolved that issue. We only had descriptions in the product variations. The base item description was blank. (It was never seen anywhere so why populate it. We have changed that going forward. The real issue is the pricing of the base item. Without a Base Item Price (Google flags the item and rejects the submission into Google Base)
|
|
|
|
|
|
Rank: Member
Joined: 8/25/2007(UTC)
Posts: 124
|
I have a SQL Table with the following fields (bvc_Product Table)
bvin
SitePrice
ParentID
bvin SitePrice ParentID
1
2 1.25 1
3 2.00 1
4 3.25 1
5
6 1.30 5
Update Query
<H1 class=titleHeader>[color=#000000>]<SPAN style="COLOR: gray">, [/color]<SPAN style="COLOR: fuchsia">MIN<SPAN style="COLOR: gray">([color=#000000>SitePrice<SPAN])<FONT color=#000000> [/color]<SPAN style="COLOR: blue">as[color=#000000>]<o:p></o:p>[/color]</P>
<SPAN style="FONT-FAMILY: 'Courier New'; COLOR: blue; FONT-SIZE: 10pt; mso-no-proof: yes">FROM<SPAN style="FONT-FAMILY: 'Courier New'; FONT-SIZE: 10pt; mso-no-proof: yes">[color=#000000><SPAN] bvc_Product<o:p></o:p>[/color]
<SPAN style="FONT-FAMILY: 'Courier New'; COLOR: blue; FONT-SIZE: 10pt; mso-no-proof: yes">Where<SPAN style="FONT-FAMILY: 'Courier New'; FONT-SIZE: 10pt; mso-no-proof: yes">[color=#000000>]<SPAN style="COLOR: gray">IS<FONT color=#000000> [/color]<SPAN style="COLOR: gray">NOT[color=#000000>]<SPAN style="COLOR: gray">NULL<o:p></o:p>
<SPAN style="FONT-FAMILY: 'Courier New'; COLOR: blue; FONT-SIZE: 10pt; mso-no-proof: yes">Group<SPAN style="FONT-FAMILY: 'Courier New'; FONT-SIZE: 10pt; mso-no-proof: yes"><FONT color=#000000> [/color]<SPAN style="COLOR: blue">By[color=#000000>]</o:p>[/color]
<SPAN style="FONT-FAMILY: 'Courier New'; COLOR: blue; FONT-SIZE: 10pt; mso-no-proof: yes">Having<SPAN style="FONT-FAMILY: 'Courier New'; FONT-SIZE: 10pt; mso-no-proof: yes">[color=#000000>]<SPAN style="COLOR: fuchsia">MIN<SPAN style="COLOR: gray">(<FONT color=#000000>SitePrice[/color]<SPAN style="COLOR: gray">)[color=#000000>]<SPAN style="COLOR: gray">><FONT color=#000000> 0[/color]<SPAN style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"><o:p></o:p>
I think a Stored Procedure is the best way to proceed... But need a little help with the scripting.
|
|
|
|
|
|
Rank: Member
Joined: 8/25/2007(UTC)
Posts: 124
|
I found a way to get the general option site price updated.
Final Script
USE Bvc5
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
/* Temp Table, note IDENTITY column that will be used to loop through rows of table. */
CREATE TABLE #temp (
[RowID] [int] IDENTITY(1, 1) NOT NULL,
[MinChildSitePrice] [numeric](18, 10),
[TempParentID] [varchar](36)
)
GO
DECLARE @NumberRecords int,
@RowCount int,
@MinChildSitePrice numeric(18, 10),
@TempParentID varchar(36)
/* Insert the result set we want to loop through into the temporary table */
INSERT INTO #temp (TempParentID, MinChildSitePrice)
SELECT ParentID AS TempParentID, MIN(SitePrice) AS MinChildSitePrice
FROM bvc_Product
WHERE (ParentID > '""') AND (SitePrice > 0)
GROUP BY ParentID
/* Get the number of records in the temporary table */
SET @NumberRecords = @@ROWCOUNT
SET @RowCount = 1
Print @NumberRecords
Print 'number of rows in temp table'
/* loop through all records in the temporary table, using the WHILE loop construct */
WHILE @RowCount <= @NumberRecords
BEGIN
/* Get one Record */
SELECT @MinChildSitePrice = MinChildSitePrice, @tempParentID = TempParentID
FROM #temp
WHERE RowID = @RowCount
/* What do you want done to the record */
Update bvc_Product
SET SitePrice = @MinChildSitePrice
WHERE bvin = @tempParentID
SET @RowCount = @RowCount + 1
END
GO
DROP TABLE #temp
GO
SET ANSI_PADDING OFF
GO
|
|
|
|
|
|
Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.