Best way to update Product Variant Price

[cgatlin08]

I need to automate product price changes from our accounting/inventory system to Miva. I currently am able to do the inventory from our system using powershell to make an API call into the Miva using the Function Product_Update. I need to update the Product Variant Pricing in the same manor but i cannot find an API fuction that does this. My first thought was just to make and ODBC connection the database and update the table directly. But this has seem to be somewhat troublesome due to how the database architecture was designed and how our products had to be uploaded to miva. Are there any other ways to update the Product Variant Price?

[Brennan]

It sort of depends on how your variant products are setup. The most common setup is what is called "sum of parts" where the variant part product determines the price. If this is the case, you an use the same Product_Update function to update the variant product's price. You will need to variant product code which you can get using: https://docs.miva.com/json-api/funct...t_load_product

[cgatlin08]

So most of the products are not set up using the Sum of Parts feature. They utilize the Price override function, which i found in the DB in the table ProductVariantPricing. I need to find a way to update these prices. I have been trying to join multiple tables to get to find a unique identifier to associate the the data with for the input variables i have to work with. Is there anyone at your company that is a Solution Architect i can talk to to figure this out?

[Brennan]

If you're using specific values, you can still use the JSON api but you need to pass in XML data using:

https://docs.miva.com/json-api/funct...rovision_store

<ProductVariant_Update product_code="shirt">
            <Options>
                <Attribute_Option attribute_code="size" option_code="small" />
                <Attribute_Option attribute_code="color" option_code="red" />    
            </Options>

            <ProductVariantPricing>
                <Method>specific</Method>
                <Price>5.43</Price>
                <Cost>4.31</Cost>
                <Weight>3.21</Weight>
            </ProductVariantPricing>
</ProductVariant_Update>

Reach out to our sales team and we can get you additional help if needed.

[cgatlin08]

This is what I am looking for, thanks for the help.

[cgatlin08]

Hello

I have to circle back on this topic. The way i update products automated is by looking at the database in miva to get specific ID's of the product variants in the multiple tables to perform the update using the XML

It looks like there is a an API to do this now, but I need to perform a get on the product to get the product ID. How can i perform a get on a specific product

Here is my payload

{
"Store_Code": "CCTP",
"Count": 1,
"Miva_Request_Timestamp": 1693409419,
"Function": "ProductList_Load_Query",
"Filter": [
{
"name": "search",
"value": [
{
"code": "8550-100"
}
]
}
]
}

[cgatlin08]

I have the main product get figured out, what I need now is how can i grab this data see my code below on the queries I use to build the XML how will the queries translate using the rest calls?


###This is to update the Product Variant

#This loads the Product query into a dataset and parses out the product number to be checked to see if it is a variant
$dataset.Tables[0] | ForEach-Object {
$ProductsId = $_.id
$ProductsCode = $_.code
$ProductsPrice = $PriceUpdate
$ProductsActive = $_.active


$string = $ProductsCode
$a,$b,$code = $string.split('-')[0,1,2]
$ProdVar = $a + "-" + $b

$conn.open()


#This gets the Parent Prod ID so that we can check the attributes of the parent product to build the XML body to insert the new price and weight
$GetParentProdId = "Select id From s01_Products Where s01_Products.Code = '$ProdVar'"

$cmdPPID = New-Object System.Data.Odbc.OdbcCommand($GetParentProdId,$con n)
$dataAdapterPPID = New-Object System.Data.Odbc.OdbcDataAdapter($cmdPPID)
$dataSetPPID = New-Object System.Data.DataSet
$dataAdapterPPID.Fill($dataSetPPID)

$conn.Close()

$dataSetPPID.Tables[0] | ForEach-Object {
$ParentProdId = $_.id
}

If($dataSetPPID.Tables[0].Rows.Count -eq 0){

$GetProdId = "Select id From s01_Products Where s01_Products.Code = '$ProductsCode'"

$cmdPPID = New-Object System.Data.Odbc.OdbcCommand($GetProdId,$conn)
$dataAdapterPPID = New-Object System.Data.Odbc.OdbcDataAdapter($cmdPPID)
$dataSetPPID = New-Object System.Data.DataSet
$dataAdapterPPID.Fill($dataSetPPID)

$conn.Close()

$dataSetPPID.Tables[0] | ForEach-Object {
$ParentProdId = $_.id
$ProdVar = $ProductsCode
}

}



#This qry checks to see if there is a specific product variant for the pricing
$ProdVarQry = "Select * From s01_ProductVariantPricing Where s01_ProductVariantPricing.product_id = '$ParentProdId' and method = 1"


$conn.Open()
$cmd2 = New-Object System.Data.Odbc.OdbcCommand($ProdVarQry,$conn)
$dataAdapter2 = New-Object System.Data.Odbc.OdbcDataAdapter($cmd2)
$dataSet2 = New-Object System.Data.DataSet
$dataAdapter2.Fill($dataSet2)
$conn.Close()

#This Condition checks to see if the product is a Variant
If($dataSet2.Tables[0].Rows.Count -ne 0){

#This qry checks to see what the Attributes of the products are
$AttributesQry = "Select code,prompt From s01_Attributes Where s01_Attributes.product_id = '$ParentProdId'"

$conn.Open()
$cmdAttGet = New-Object System.Data.Odbc.OdbcCommand($AttributesQry,$conn)
$dataAdapterAttGet = New-Object System.Data.Odbc.OdbcDataAdapter($cmdAttGet)
$dataSetAttGet = New-Object System.Data.DataSet
$dataAdapterAttGet.Fill($dataSetAttGet)
$conn.Close()

$AttBuild = @"

"@

$dataSetAttGet.Tables[0] | ForEach-Object {
$ParentProdCode = $_.code
$ParentProdPrompt = $_.prompt

if($ParentProdCode -eq 'Unit' -Or $ParentProdCode -eq 'Quantity' -Or $ParentProdCode -eq 'UM' ){
$AttBuild += "<Attribute_Option attribute_code='$ParentProdCode' option_code='$Unit' />"
}
Else{
$AttBuild += "<Attribute_Option attribute_code='$ParentProdCode' option_code='$code' />"
}


#Ending for ForEach on $dataSetPPID
}



$body = @{
Store_Code = $storeCode
Function = 'Provision_Store'
xml = "<ProductVariant_Update product_code=$ProdVar>
<Options>
$AttBuild
</Options>
<ProductVariantPricing>
<Method>specific</Method>
<Price>$ProductsPrice</Price>
<Weight>$UnitWeight</Weight>
</ProductVariantPricing>
</ProductVariant_Update>"
}

if ( $includeTimestamp ){
$body["Miva_Request_Timestamp"] = [DateTimeOffset]::Now.ToUnixTimeSeconds()
}

$body = $body | ConvertTo-Json