Alain
Posts: 3
|
| Posted: 01/23/2004, 4:02 PM |
|
Hi,
I'm trying to use a calculated value (grid total) to update a field in a table every time this value is recalculated.
Can't get it to work.
Is it possible?
If yes, would anybody have an idea how to write the SQL statement?
Thanks in adavance
Alain
|
 |
 |
peterr
Posts: 5971
|
| Posted: 01/23/2004, 4:35 PM |
|
I could try to help with this if you provide more details. For example what do you mean by "this value is recalculated"? How is recalculated if you are displaying a grid?
However I can try to propose some generic solution:
In your grid's Before Show event add custom code like the one shown below:
Dim total
total = CCDLookup("SUM(price)", "items", GridName.DataSource.Where, DBConnection1)
CCExecSQL ("UPDATE total_price SET total=" & total & " WHERE ...", DBConnection1, True)
You would need to modify various variables shown above to match what you're working with. For example "DBConnection1" should be replaced with "DB" + the name of your connection in CCS. While "items" is a sample table name, etc.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Alain
Posts: 3
|
| Posted: 01/24/2004, 4:35 AM |
|
Thank you for your answer Peter,
Ok, here is how it looks:
I have a page called "Quotes_include".
The page contains a grid form "Quote" with a calculated label "Total" (= Qty*UnitPrice) in each row and another calculated label "Summ" at the end of the grid for the Quote total.
The page also includes a record form "Quote_Edit" to add/edit the grid "Quote".
All this works fine. (it uses the tables "Quotes" and "Products)
Now I would like to use the calculated value "Summ" to update a field "ProjectBudget" in a table "Projects" every time the "Quote" is updated via "Quote_Edit".
How to use this calculated value in the SQL statement?
Here is what I have done so far:
Dim Connection
Dim ProjectID
DIM SQL
ProjectID = CCGetParam("ProjectID", Empty)
Set Connection = New clsDBProjects_MySQL
Connection.Open
SQL = "UPDATE projects SET ProjectBudget = "????" WHERE ProjectID=" & CCToSQL(ProjectID, "Integer")
Connection.execute(SQL)
Connection.Close
Set Connection = Nothing
Regards,
Alain
|
|
|
|
peterr
Posts: 5971
|
| Posted: 01/24/2004, 12:20 PM |
|
Your base code looks good.
Looks like you only need to make sure that:
- the "ProjectBudget" field is submitted with the form. It should be inside the form and have a name.
- change your SQL to something like:
SQL = "UPDATE projects SET ProjectBudget = " & CCToSQL(Request.Form("ProjectBudget"),"Float") & " WHERE ProjectID=" & CCToSQL(ProjectID, "Integer")
Note that that I found the code Request.Form("ProjectBudget") by creating the action "Retrieve Value for Variable" and then examining the code that this action generated.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Alain
Posts: 3
|
| Posted: 01/24/2004, 4:42 PM |
|
Peter,
What I am trying to do is once the "Summ" label has been calculated or recalculated in the grid "Quote" to use this value to update my "ProjectBudget" field in the "Project" table.
If I use a "ProjectBudget" field in the Edit form, then I won't use the latest calculation (that appears in the grid "Quote" using "before show event")
Could we use the SQL statement right after the calculation is finished?
Thanks
Alain
|
|
|
|
peterr
Posts: 5971
|
| Posted: 01/24/2004, 5:14 PM |
|
Well, your grid is no longer displayed when the Edit form is submitted, therefore you have more limited options. Probably after the form is submitted you'd need to programmatically perform the same calculations that your grid performed earlier.
Though how about:
1. Create a hidden field on your Edit form, then use JavaScript to copy the value of your label from the Grid to the hidden field of the Editable form. Then this field will be submitted automatically and you may be able to use my last solution.
OR
2. Similar to #1, but do the calculations on the server, for example:
a) Dim "MySumm" variable outside of any events, so that it is global. Then in your "Summ" label's events specify "MySumm = GirdName.Summ.Value", which will populate your global MySumm variable with the same value as shown in the label. Then populate a hidden field on your Edit form with this value, like "FormName.Hidden1.Value = MySumm". Then later when the form is submitted it will submit this value and you can use it.
b) or you can populate a hidden field on your Edit form with a value calculated in the same way as in your grid, for example:
FormName.Hidden1.Value = CCDLookup("SUM(Qty*UnitPrice)", "TableName", GridName.DataSource.Where, DBConnection1)
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Edd
Posts: 547
|
| Posted: 01/24/2004, 11:03 PM |
|
Alain,
If you have not got a capable database then you need to follow PeterR's advice but this is a business rule and as such you should really not be doing this at the presenation layer - why not add a trigger to your table to complete your summation - this means that it is always correct.
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
|
