CodeCharge Studio
search Register Login  

Web Reporting

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> ASP

 Filter grid by calculated field

Print topic Send  topic

Author Message
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
View profile  Send private message
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"
View profile  Send private message
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
View profile  Send private message
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.
View profile  Send private message
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
View profile  Send private message
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,
View profile  Send private message
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).


View profile  Send private message
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
View profile  Send private message
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)
View profile  Send private message
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


View profile  Send private message

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

PHP Reports

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright © 2003-2004 by UltraApps.com  and YesSoftware, Inc.