Editing pricing in database, advice needed

[sfeher]

Hi Mazhar --
Thought maybe you'd see this and have some input on this one.

I just performed my first "raw" DB editing and found a strange thing. Before I get in trouble for editing the "raw" DB, bear with me -- I have a client with 9800+ products and 5 different pricing plans for various groups. Do the math. There are over 45,000 different pricing rules that have to be included (lots-o-typing) so importing directly to the DB was required. I've worked with and run my fair share of SQL statements, so I'm OK with actually working IN the DB.

I have 5 custom groups defined in the site (added through the AC Admin). I then imported my Special Prices (into the ac_Specials table, of course). ProductID, Price included, dates left Null. For each record, a new, unique SpecialID was assigned in the table, as expected. Then I updated the ac_SpecialGroups table to associate the Special Price to the Group membership. When I did this, I chose to import one group's specials at a time so that I could spot-check the various imports. So -- my ac_SpecialsGroups table has the SpecialID in sequence, but the associated ProductIDs (while present) are out of order. See screen capture attached.

Price_Rules_Specials.jpg

Price_Rules_Specials2.jpg

The problem is that I've imported 45,000+ pricing rules and updated both the ac_Specials and ac_SpecialGroups tables as I thought that they should be and everything in the DB appears to be fine. However, when I login to the Admin of the product and review the product, I cannot see any of the pricing rules and I'm prompted to create a new pricing rule. Huh??

Not really sure what I did wrong here, and why the various Pricing Rules will not show up correctly.

1.) In AC 5.5, there was a table that had to be incremented for each 'next' value index for things like options. I know that AC 7.0x doesn't use this.... but.... am I missing something on importing new pricing rules?

2.) I don't see any other table that requires data to be added to it and I have reviewed the DB schema from the wiki pages -- does someone know something that I don't ?

3.) Does the 'sequence' of the ProductID really need to in order with the SpecialIDs (see the yellow records in the first graphic above)?

4.) Should I have performed the update with a Data Transport session using XML to associate the Special with the product? What about updating the GROUP with that specific Special? Is the XML format for the data update really preferred?

Thanks!

[kastnerd]

I think there is another field on the product table to say what specials that product uses?
Are you trying to update pricing rules or create them with the DB? Iv been able to update pricing rules but creating them did not work for me.

[jmestep]

Those are the only two tables involved and the order of the product id's wouldn't make any difference. The code used on the product specials page to pull the specials is SpecialDataSource.LoadForProduct, which is just a query the pulls the specials by product id.
Do you have the following on your Admin/Products/Specials/Default.aspx page?

<asp:ObjectDataSource ID="SpecialDs" runat="server" OldValuesParameterFormatString="original_{0}" SelectMethod="LoadForProduct" TypeName="CommerceBuilder.Products.SpecialDataSource" DataObjectTypeName="CommerceBuilder.Products.Special" DeleteMethod="Delete" SortParameterName="sortExpression">
<SelectParameters>
<asp:QueryStringParameter Name="productId" QueryStringField="ProductId" Type="Object" />
</SelectParameters>
</asp:ObjectDataSource>

[mazhar]

Also make sure that product ids are same. I am not sure but may be product ids you imported in special and special group table doesn't exist or match to the ones in actual product table.