Do Grid Base Filter Arguments (SQL) Refresh?

[nlk10010]

I have a grid based on the following query:

SELECT * FROM LOADHEADERS WHERE ENTRYDATE >= :beginDate AND ENTRYDATE <= :endDate

:beginDate and :endDate get their values from two session variables (session.begindate and session.enddate). This all works fine except when I try to change either or both of the session variables and then refresh the grid: Apparently, the values of the arguments are not refreshed. The query uses the values of the session variables when the grid was initialized. If I close the grid, then reopen, the new session variable values are picked up.

I want to use these arguments (as opposed to setting a filter clientside) because of the number of records involved. I don't want to download everything to the client and THEN apply a base or user filter.

Is this the way Alpha usually operates and, if so, then is there an efficient way to get the query to refresh its arguments? One thing I COULD do, I suppose, would be, in OnSQLSelectQuery, to build my own SELECT query using the new values of the session variables as arguments. Seems kind of inefficient, but I suppose as a last resort I could try it.

Any ideas would be appreciated.

[lvasic]

Re: Do Grid Base Filter Arguments (SQL) Refresh?

I use this embed a lot: OnSearchPartFilterCompute

[nlk10010]

Re: Do Grid Base Filter Arguments (SQL) Refresh?

I use this embed a lot: OnSearchPartFilterCompute

Yea, thanks, but that seems to be fired when the user clicks the "Search" button in the grid and I'm not sure altering anything in that event will affect the "base" SELECT statement I defined at design time.

To be more specific, I've added an Action Button to the grid that calls a Dialog into which the user can enter a new date range (start and end dates). When the form is submitted I simply set the session variables session.beginDate and session.endDate to those new values and refresh the grid. I've verified the values of the session variables have been changed successfully but these values are never read into the base SELECT statement's arguments (that is, until I close and then re-open the grid).

Again, thanks for the info.

[lvasic]

Re: Do Grid Base Filter Arguments (SQL) Refresh?

Have you tried using the dynamic filter? Plus, the OnSearchPartFilter compute will run when the grid is first opened.

[nlk10010]

Re: Do Grid Base Filter Arguments (SQL) Refresh?

Dynamic filter? Hmmm, I'm going to have to search for info on that. Thanks.

What I'm doing now is going into the OnSQLSelectQuery and setting the query's arguments (e.arguments.set()) to the session variables, this way I am certain of always having the arguments current. I don't know what kind of performance hit this is, I may have to institute a "flag" to only do this when those session variables change.

[GGandhi]

Re: Do Grid Base Filter Arguments (SQL) Refresh?

Apparently, the values of the arguments are not refreshed. The query uses the values of the session variables when the grid was initialized. If I close the grid, then reopen, the new session variable values are picked up.

when you build your grid the data is collected from the server and the server is gone. you will be working with the data in your hand, if you want a new set of data, say for different dates then you will have to reissue the query and get the data. that's why it does not work with the first session variables.

I want to use these arguments (as opposed to setting a filter clientside) because of the number of records involved. I don't want to download everything to the client and THEN apply a base or user filter.

is this because you have a lot of data, and you expect the load time will be prolonged?
if you build your grid with a search part and put the dates as a range and hide the grid when search is not active you will have the entire dataset available, but it loads quicker.
will that not work in your case?

[nlk10010]

Re: Do Grid Base Filter Arguments (SQL) Refresh?

when you build your grid the data is collected from the server and the server is gone. you will be working with the data in your hand, if you want a new set of data, say for different dates then you will have to reissue the query and get the data. that's why it does not work with the first session variables.

Well, that's what I'm looking for, information. So you're saying that the query specified in the grid is executed ONCE, when the grid is first run, and that's it? But then what I don't understand is: If I enter new information through the grid and do a refresh (either straight or for a particular row), how then does that information get back to the grid? And what exactly do you mean by "reissue the query"? How do I do that without closing and then reopening the grid? Frankly, I've done some experimenting and this is NOT consistent with what I'm observing; e.g. I can refresh the grid and in the OnSelectQuery event change the value of the arguments in the SELECT query and I will get records that were not in the original resultset. So, somehow, that query IS being rerun, it's just not picking up the new values of the session variables. I'm not saying you're wrong, you are much more experienced than I am and this may just be a misunderstanding on my part, but it's difficult for me to believe that the design-time query is not being rerun at some point.

is this because you have a lot of data, and you expect the load time will be prolonged?

Yes, precisely

if you build your grid with a search part and put the dates as a range and hide the grid when search is not active you will have the entire dataset available, but it loads quicker.
will that not work in your case?

I need to do a lot more research. I have a search part but I'm not sure why then the data would load quicker? Isn't the filter in the search part applied on the client side? I don't want to pull the entire database down from the server and then do filtering on the client side.

Again, my question is, to put it in your terms, how do you exactly "reissue the query" and, if you can, why aren't the session variables in my filter reevaluated? Consistent with what you've said, I've tried resetting the arguments in the OnSQLSelectQuery, and this actually works, but Alpha then, somewhere, resets the arguments to their original value so I keep needing to do this. Also, I get some weird behavior when the resultset is empty so I needed to abandon this method.

Thanks for taking the time to post and try to help me out.

[GGandhi]

Re: Do Grid Base Filter Arguments (SQL) Refresh?

Well, that's what I'm looking for, information. So you're saying that the query specified in the grid is executed ONCE, when the grid is first run, and that's it? But then what I don't understand is: If I enter new information through the grid and do a refresh (either straight or for a particular row), how then does that information get back to the grid?

if you filter out your database table to yield from jauary1 to june 30 and show the grid all the information up to that point will be available via that grid ( something similar to additive filtering)not earlier than jan 1st or later than june 30th. if you want those dates I am sure you will have reissue the query to get the dates by some means like selectQury you mentioned. since you need a different source for the grid.
http://screencast.com/t/4VMuaKj9
see the image, I do not know the reason but this is how I knew.
would you try this method and see if it fits the bill
and when you have the entire table available at your disposal, you can keep on changing the value as many times you want and there won't be a problem

[nlk10010]

Re: Do Grid Base Filter Arguments (SQL) Refresh?

Gandhi:

That's an interesting option. Right now the set is not too large but I know it will grow and become unwieldy. Let me experiment with your suggestion and see if it helps me get to an acceptable solution.

Thanks much for bringing this to my attention.

[GGandhi]

Re: Do Grid Base Filter Arguments (SQL) Refresh?

good, let me also suggest something to you.
in the grid part ( not on the search part) go to properties and scroll down all the way and check this property
http://screencast.com/t/v2We3hdM0
and run your grid and see the select statement that come up, it is only bringing few records from the table not the whole lot. so in the event your data becomes unwieldy still alpha is going to bring small segment your load time will probably not vary.
at least I think.

[nlk10010]

Re: Do Grid Base Filter Arguments (SQL) Refresh?

Yes, you're right, I am not understanding something here. Submitting a search from the Search part is definitely NOT just client-side, it goes back to the server. Further, even if I set the design-time filter to not return ANY records, I can subsequently execute a Search and go back to the server and get whichever set of records I want. So the design-time filter seems ONLY to affect the initial load; thus, there's no need to make it "dynamic"; i.e. change the arguments in the design-time WHERE condition programmatically. Just use the Search part. At least I think. :)

Thanks again.