Dynamic update

[#1]

Hi All,

In script, I load products.qvd:

Products:
LOAD ProductID,
ProductDescription,
StartDate,
EndDate,
CostPrice
FROM
..\..\DataSources\Products.qvd
(qvd);
------------------------------------------------
Now, i want to create a button when click on this button
the CostPrice will be update with formula
Ex: CostPrice=CostPrice*1.15

I tried with subroutine:

sub update
ActiveDocument.DynamicUpdateCommand("UPDATE Products SET CostPrice=CostPrice*1.15")
end sub

but there are no change

How can i solve this problem?

[Anonymous]

I guess is what you really need is not a button but a variable (in input box or slider). If the variable name is Var, the expresions in your chart / tables will include
CostPrice*Var

[#3]

Hi Michel,

You are right. It means that CostPrice=CostPrice*Var and Var may be a input or slider.

But my problem is I want to update data in qv file and I can not update column CostPrice in table Product"

Regards,

Thai

[Anonymous]

Thai,

I don't know of any way of updating data other than reload. Probably it's not what you need, but you can use varibale (or constant 1.15 if you wish) in the load script:
Products:
LOAD
ProductID
..
CostPrice,
CostPrice*$(Var) as NewCostPrice,
...
FROM...
If you want to keep the same field name:
DELETE FIELD CostPrice FROM Products;
JOIN (Products) LOAD
ProductID,
NewCostPrice as CostPrice
RESIDENT Products;
So your button should assign value to Var, run reload, and save. Simpe enough, except it make take long time depending on your data.

[#5 · Accepted solution]

Hi Michael,

Thank you for your help that is ok.

In my case, I want only change products which were selected.

My problem is I didn't turn on dynamic update attribute of document

The problem is solved as below:

sub vbCostPriceUpdate
dim NoSelected, answer,strValue
' Get product list which are selected
set possibleValues = ActiveDocument.Fields("ProductID").GetPossibleValues

NoSelected=possibleValues.Count
if (NoSelected>0) then

' Set DynamicDataUpdate=true for current document
set curDoc = ActiveDocument.GetProperties
curDoc.EnableDynamicDataUpdate=true ' <---- Here is my problem when I can not UPDATE
ActiveDocument.SetProperties curDoc

' Get new costprice from variable
SET objSource = ActiveDocument.Variables("vNewCost")
strValue = objSource.GetContent.String

' Update each selected row in table Products
for i=0 to NoSelected-1
query="UPDATE Products SET CostPrice="& strValue & " WHERE ProductID='"& possibleValues.item(i).text & "'"
set sqlerror=ActiveDocument.DynamicUpdateCommand(query)
if (sqlerror = false) then ' send message when error
MsgBox sqlerror.ErrorMessage
exit sub
end if
next
end if
' save all change in current document
ActiveDocument.Save
end sub

----------------------------------------------------------------------------------------------------------------

I want to say thanks again.

Regards,

Thai

[mongolu]

i saw that the WHERE clause is a must.

meaning, you must put the " WHERE CostPrice=CostPrice" in your query, at the end.

it worked for me.

[#7]

Hi Thai,

Does your application work on the server. I have a similar application. I am getting a "Macro parse failed. Functionality was lost." when the DynamicUpdateCommand is executed. The application runs fine locally on my computer.

Any ideas what causing this error?

Thanks

[partenope]

Hi,

when the application runs on QVServer, with Ajax, plug-in or other client, I know that there is a special Server licence to install...

bye