JimbobGilb
Posts: 28
|
Posted: 01/15/2013, 9:44 AM |
|
I have a grid which from an access database pulls OrderedQuantity and OrderShipped. i have a formulae field in the grid which tells me the remaining orders, eg OrderRemaining = orderedQuantity - OrderShipped. I want to filter the grid so i only see the orders remaining to ship eg OrderRemaining > 0 . How can i do this either in a before show event or something similar.
Thanks in advance,
James
|
|
|
Lucius
Posts: 220
|
Posted: 01/16/2013, 4:23 AM |
|
Use Before Select event, check CCS help file and this topic: "Dynamically Modify the WHERE Clause"
|
|
|
JimbobGilb
Posts: 28
|
Posted: 01/16/2013, 6:41 AM |
|
Thanks for this Lucius,
I can get this working when the Field data is from the database table as below
SODetail.DataSource.Where = SODetail.DataSource.Where & "QuantityOrdered > 0"
But not when it is a calculation in the webpage. Calculation example QuantityRemaining = QuantityOrdered - QuantityShipped
I then want to Dynamically Modify the WHERE Clause based on the QuantityRemaining field
SODetail.DataSource.Where = SODetail.DataSource.Where & "QuantityRemaining > 0"
Any help would be appreciated
|
|
|
Lucius
Posts: 220
|
Posted: 01/16/2013, 4:13 PM |
|
You need to either do this server-side, so after the calculation is done, user presses some form button, and then you read what is the QuantityRemaining value, and use it in Where clause.
Alternatively you can do it client side, without page refresh. Again look at the CCS help file and check the "RemoteDLookup" topic. This is built-in feature of CCS, however I have not used it myself, so I don't know in detail how it works, but should be pretty simple.
For client side you can always use jQuery to do the same as RemoteDLookup, if you need more control over what is happening.
|
|
|
Lucius
Posts: 220
|
Posted: 01/16/2013, 4:16 PM |
|
On second thought RemoteDLookup might not be what you are looking for. It retrieves only single value it seems.
So what you can do here is:
- QuantityRemaining is a form field that has the value inputed
- user presses the Submit form button
- on Before Build Select event you get the param of QuantityRemaining field (CCGetParam function)
- you build custom WHERE clause
After refresh your grid should be filtered
|
|
|
JimbobGilb
Posts: 28
|
Posted: 01/17/2013, 2:28 AM |
|
Thanks for this, I think i am on the right lines now.
I can retrive the parameter in a simple sodetail.Label1.value = CCGetParam("remaining","")
but i think iam now doing something wrong in my where clause
SODetail.DataSource.Where = SODetail.DataSource.Where & "remaining=" & CCGetParam("remaining","")
This does not filter the grid, i just get the error:
Form: Grid SODetail
Error: No value given for one or more required parameters. (Microsoft JET Database Engine)
Error: No value given for one or more required parameters. (Microsoft JET Database Engine)
am i missing something simple?
Thanks in advance,
|
|
|
Lucius
Posts: 220
|
Posted: 01/17/2013, 2:51 AM |
|
Try to output your WHERE statement and you will see what is the problem, print the SODetail.DataSource.Where to the screen and stop the script execution. In PHP it would be:
print_r(SODetail.DataSource.Where);
die();
Anyway if you pass your value so, there are few problems. First is that your query will look something like:
Where some value = 'aaaa' AND someothervalue = 'bbbb'remaining=xxxxxx
First of all you didn't add an operator (AND or OR), secondly you passed the xxxxxx just as it is. It is not quoted so SQL treats it as a column name or parameter.
Use the ToSQL Method of DB connection
Set myRemaining = YourDBConn.ToSQL(CCGetParam("remaining",""))
SODetail.DataSource.Where = SODetail.DataSource.Where & " AND remaining=" & myRemaining
Or something like the above (I don't know ASP).
|
|
|
JimbobGilb
Posts: 28
|
Posted: 01/18/2013, 6:21 AM |
|
Hi there, thanks for your help fo far,
Still not sure where im going wrong
Can't get
Set myRemaining = DBConnection1.ToSQL(CCGetParam("remaining",""))
working
response.write "WHERE:" & SODetail.datasource.Where seem to work and bring up the correct result as below
WHERE:PartMaster.DepartmentCode in ('Man', 'Sub') and SODetail.ClosedFlag = false and SOHeader.ClosedFlag = false and SODetail.ScheduledShipDate <= #2013-01-17 00:00:00# AND Remaining = 1000
and i am using the clause below in the BeforeExecuteSelect
SODetail.DataSource.Where = SODetail.DataSource.Where & " AND Remaining = " & CCGetParam("remaining","")
But it is still not filtering the grid.
Hopefully just a few tweeks now.
Thanks,
James
|
|
|
Lucius
Posts: 220
|
Posted: 01/18/2013, 11:25 AM |
|
Ahh my mistake, wrote that quickly, try:
Set myRemaining = DBConnection1.ToSQL(CCGetParam("remaining",""), ccsText)
(or check the ToSQL function for ASP in CCS help if it gives you more trouble)
|
|
|
JimbobGilb
Posts: 28
|
Posted: 01/21/2013, 9:56 AM |
|
Hi Lucius,
My 'remaining' formula is in a before show row event. Does this occur after the BeforeExecuteSelect event. If so this maybe why I cannot get it to work.
What event would be best to calculate the remaining field in?
Thanks,
James
|
|
|
|