Updating Product Prices / Costs directly into the Table s01_Products

[lesliekirk]

First, don't shoot me, I'm just trying to get answers.

Storeowner has someone that updates price changes directly into the price and cost fields in Table s01_Products using an SQL statement (to ONLY update the price and cost fields in Table s01_Products). This method seemed to work without incident until something changed. I suspect it was when the use of Price Groups Sale Price was added.

My suspicions come from how the products that have been updated with the direct method now appear on the product page.

This snippet is used to display the products on Sale:

<mvt:if expr="l.settings:product:base_price GT l.settings:product:price">
<p>List Price: <span id="price-value-additional" class="bold" style="text-decoration: line-through">
&mvt:product:formatted_base_price;</span>&nbsp;&nbsp;&nbsp;&nbsp;</mvt:if><span id="price-value" class="bold">Our Price: &mvt:product:formatted_price;</span></p>

Every product that has been updated with the direct method display the new "updated" price as the List Price and the old price as "Our Price". If you add the product to the cart the "List Price" displays.

If you look at the product in the product's admin, the updated price is in the Product Price field and nothing is displaying in the Sale Price field as the product has not been assigned to the Price Group to enable the field.

I suspect a "relationship" has not been made with the product and some other field in the Table but I also have to ask if anything may have changed during a recent update that could be causing this behavior.

[Kent Multer]

If the custom script only updates the prices and costs, it won't have any effect on the price-group assignments; of that much, I'm certain. Beyond that, here are a few possibilities.

Do any of the products use variants? If so, those prices may need to be updated too, depending on the specific setup.

To speed up the performance, the store pre-calculates a lot of discounts. The "compliant" ways of updating a price -- such as using the admin page or the JSON API -- will trigger this pre-calculation. Also, there's a scheduled task named "Pre-Calculate Discount Prices" that can do this automatically, every night or at some other convenient times. Since your custom script writes directly into the DB, it doesn't trigger this calculation. You can try manually triggering the task, and see if that fixes the problem. The task may take a while to run; you can check the task log to find out when it's finished.

In general, as the Miva store has grown over the years, it's acquired more and more places where, when you update one table, you need to update some others to keep everything in synch. I'll guess that this custom script has been in use for a long time? It should probably be rewritten to use the JSON API instead of writing directly to the DB. And if the store uses any other custom scripts to directly update other tables, they should probably make the same change for those.

Hmmm, actually, I'm not sure if the JSON API always triggers the pre-calculation. But I do know that the API can be used to trigger a scheduled task; so the update might require two API calls instead of one.

Hope that helps --

[lesliekirk]

Hi Kent,

None of the products use variants and I have a question about the pre-calculations - is that something that is always done (looked for) even if the "Calculate Predicted Discounts and Sale Price" is not checked on the PROD Page Product Display Layout?

Thanks,
Leslie

[Nick]

lesliekirk Updates should never be made directly to the database. Any pricing updates can be done via data management import, product admin or the JSON API.

As for your predicted discounts question I am not sure I am following but here is more information about the feature that might help:

https://docs.miva.com/how-to-guides/...0the%20Display

Note, enabling the predicted discount setting does not impact the actual product price.

[lesliekirk]

lesliekirk Updates should never be made directly to the database. Any pricing updates can be done via data management import, product admin or the JSON API.

As for your predicted discounts question I am not sure I am following but here is more information about the feature that might help:

https://docs.miva.com/how-to-guides/...0the%20Display

Note, enabling the predicted discount setting does not impact the actual product price.

Thanks Nick - I've known of the "Updates should never be made directly to the database" probably since I first started working with the Miva Merchant application. I know enough to know I don't know enough to try and do anything directly in a database. Unfortunately, I just found out that this particular store was using this method.

As for the "Calculate Predicted Discounts and Sale Price" not being checked in the template - I did go back, check it, and update the template. The issue disappeared. I still don't understand why it would have impacted these prices. None of them had a sale price, it should have just been an update to the current pricing. Again, this falls into the "I know enough to know I don't know enough". I do know that whenever I checked the "Calculate Predicted Discounts and Sale Price" box - it always seemed to create confusion (customers being confused) with the price displays on the product page after adding to the basket. Yes, it would work as outlined but the customers (and some store owners) just didn't understand the adjusted price which is why I stopped using it.

I do have a support ticket open but I'm hoping the "check box" fixed the issue.

[lesliekirk]

Also, there's a scheduled task named "Pre-Calculate Discount Prices" that can do this automatically, every night or at some other convenient times. Since your custom script writes directly into the DB, it doesn't trigger this calculation. You can try manually triggering the task, and see if that fixes the problem. The task may take a while to run; you can check the task log to find out when it's finished.

Hope that helps --

I just discovered that there already is a "Pre-Calculate Discount Prices" Scheduled Task already configured for this site. I have no idea when this was set up. Is there any way to find out when something like this was set up? If it's been in place all along, then it didn't solve the problem. Does it set itself up by default when you check the "Calculate Predicted Discounts and Sale Price" box on the PROD page template?

[Bruce - PhosphorMedia]

RE: Direct access to SQL.

I believe this will be killed shortly (if not already but maybe the site's on a legacy server of some sort?

Nick, et al, am i off-base here?

[lesliekirk]

RE: Direct access to SQL.

I believe this will be killed shortly (if not already but maybe the site's on a legacy server of some sort?

Nick, et al, am i off-base here?

Nick - this would definitely be a need-to-know.

[Rick Wilson]

We're not going to cut off people using it without warning, so don't fret.

We already do not allow new customers to do it.

The question (which we wrestle with internally) is how much notice, etc... or is there some other offramp.

At a minimum you'd get 90 days notice, but likely longer.

The real takeaway here is DO NOT keep adding new things via that method.

[lesliekirk]

Thanks, Rick Wilson - I have passed your reply on to the person doing these types of updates.

[alphabet]

The question (which we wrestle with internally) is how much notice, etc... or is there some other offramp.

I think the biggest use case would be third party developer modules that create, alter or update tables.

I moved all of my third party tables to a new DB server and it took a couple months in my free time. However, I still use toolbelt to update basket item prices.

I imagine any module that updates shipping prices would need more time.

You could probably add a log statement with any MvQUERY and track how big of an off-ramp you need. Or reach out to the developers to see how long it would take to have them stop using MvQUERY to an API.

IMHO, the DB should be locked down and move third party tables to a different server. It would improve DB performance, security and probably be less of a problem than you think.

[Bruce - PhosphorMedia]

We're not going to cut off people using it without warning, so don't fret.

We already do not allow new customers to do it.

The question (which we wrestle with internally) is how much notice, etc... or is there some other offramp.

At a minimum you'd get 90 days notice, but likely longer.

The real takeaway here is DO NOT keep adding new things via that method.

Good to know cause fixing this is likely going to require at least assistance from a Miva Developer (and most i know are pretty swamped).

Addition Question: its often remarked that these types of functions should be handle by using JSON APIs. I understand how that could be easier if a outside developer is building something, but is there any benefit to using JSON verses Native APIs (with a secure portal for connection) inside of Miva?

[ILoveHostasaurus]

Bruce - PhosphorMedia
If you're referring to the Miva Merchant API callable via pages, modules, etc., those functions are similarly usable and in many cases the JSON API calls are mapped to them behind the scenes, assuming the token being used has permissions to run the relevant function, similar to how a store administrative user would need the relevant permissions. The activities will also then be subject to auditing and logging as well.

lesliekirk I typically understand why someone went down the path of doing something in the db initially, whether desirable or not. However, in this particular case, I'm curious why someone would have ever needed to go that route for nothing more than price and cost updates? If it's one off stuff, the store admin is easy enough. If it's bulk file, why would you not do an import update of code, price, cost?

[lesliekirk]

lesliekirk I typically understand why someone went down the path of doing something in the db initially, whether desirable or not. However, in this particular case, I'm curious why someone would have ever needed to go that route for nothing more than price and cost updates? If it's one off stuff, the store admin is easy enough. If it's bulk file, why would you not do an import update of code, price, cost?

David, I find myself scratching my head on this too. At one point there were issues getting the file emailed to him (but I think that's been resolved). He does know how to use FTP to retrieve the file. This site also uses Filemaker with a custom orders export file (but that doesn't connect back to Miva). I suspect he's an old-school DBA. I've made the store owner (and the DBA) aware of what's coming down the pike. If you'd like to reach out to them I can put you in contact.

[ILoveHostasaurus]

Was really just curiosity, but the answer is the same either way, and letting them continue to do this for any reason is doing them a disservice, because anyone doing that is heading towards a day when something they think will keep working suddenly won't, even if it's not from us removing the access.