|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss the use of the SalesLogix Web Platform, Client and Customer Portals, and the Application Architect (For version 7.2 and higher only). View the code of conduct for posting guidelines.
|
|
|
|
Grids and paging...
Posted: 05 Jan 10 10:40 AM
|
I have this as my SQL
string strSQL = "";
strSQL = "SELECT A3.CONTACTID, A3.NAMELF, A3.ACCOUNT, A3.WORKPHONE, A3.EMAIL, A2.CONTACTID, A2.MODIFYDATE, A2.SPEHISTORYID, A2.SPEASSIGNEDUSERNAME, A2.SPESUBJECT, A2.SPETEMPLATE, A2.SPEQUERYTAG, A2.SPESTATUS, A2.SPESCORE, A2.SPECLICKS, A2.SPEACTION, A2.SPEOPENS, A4.ADDRESS1, A4.CITY, A4.STATE, A4.POSTALCODE ";
strSQL += "FROM SPEHISTORY A2 ";
strSQL += " INNER JOIN CONTACT A3 ON (A2.CONTACTID=A3.CONTACTID) ";
strSQL += "INNER JOIN ADDRESS A4 ON (A3.ADDRESSID=A4.ADDRESSID) ";
if (SpeEmailTagsLB.SelectedIndex > 0)
strSQL += "AND (A2.SPEQUERYTAG = '" + SpeEmailTagsLB.SelectedItem.Text + "') ";
for (int s = 0; s < SpeEmailStatusFilterLB.Items.Count; s++)
{
if ((s > 0) && (SpeEmailStatusFilterLB.Items[s].Selected))
strSQL += "AND (A2.SPESTATUS = '" + SpeEmailStatusFilterLB.SelectedValue + "') ";
}
// filter senders
for (int s = 0; s < SpeUsersLB.Items.Count; s++)
{
if ((s > 0) && (SpeUsersLB.Items[s].Selected))
strSQL += "AND (A2.SPESENDER = '" + SpeUsersLB.SelectedValue + "') ";
}
if (SpeScoreFiltersLB.SelectedIndex > 0)
strSQL += "AND (A2.SPESCORE " + SpeScoreFiltersLB.SelectedValue + ") ";
I then create a command and excute query. I get a result set of 21. My page size is 15.
I was looking into ways of only retrieving 15 rows per query.
I look into the use of 'row_number()' but I couldnt come up with valid working SQL statement with row_number().
I know about 'TOP' but that only works for the first page...
anyone know how to combine or add a SELECT statement to only retrieve X rows that qualify all my filters?
|
|
|
|
Re: Grids and paging...
Posted: 05 Jan 10 10:57 AM
|
Seems as if you are looking for Custom ways to handle paging.
What would you do if the user navigates to the Second page? What if the user changes the Sort order of the Grid?
As is, SLX has already built into their code (for Groups) some of this functionality, I am not quite sure if that has also been added to the Grid.
That being said, you are workign on an ASP.Net application, and you can certainly search within Google and find several examples of Custom Paging/Sorting that would include the type of Data Retreival that you are looking for, but as stated, there are more issues to consider than just the Page Size when using such an approach.
|
|
|
|
Re: Grids and paging...
Posted: 05 Jan 10 1:31 PM
|
Yep, custom paging. I have some SQL that will run perfectly in SQL manager but when run through SLX no records are returned...
sql... (minus the sysdba. prefix on database tables for slx)
SELECT LASTNAME, FIRSTNAME, ACCOUNT, WORKPHONE, EMAIL, SPEACTION, SPESCORE, SPESTATUS, SPECLICKS, SPEOPENS, SPEASSIGNEDUSERNAME, SPEQUERYTAG, CITY, STATE, POSTALCODE FROM(SELECT h.spehistoryid, c.contactid, c.LASTNAME, c.firstname, c.ACCOUNT, c.WORKPHONE, c.EMAIL, h.SPEACTION, h.SPESCORE, h.SPESTATUS, h.SPECLICKS, h.SPEOPENS, h.SPEASSIGNEDUSERNAME, h.SPEQUERYTAG, a.CITY, a.STATE, a.POSTALCODE, Row_number() OVER (ORDER BY h.SpeScore DESC) as Rank FROM sysdba.SPEHISTORY h INNER JOIN sysdba.Contact c ON (h.CONTACTID = c.CONTACTID) INNER JOIN sysdba.Address a ON (c.ADDRESSID = a.ADDRESSID) AND (h.MODIFYDATE >= '12/14/2009 7:21:45 PM') ) as HistRows WHERE Rank BETWEEN 1 AND 10
any ideas? is Row_Number() not supported?
also, to answer you other questions, i dont allow the sorting of columns (and if i did, i would reset grid to page 0 and resubmit sql with new ORDER BY clause rather than contactid in the above example) |
|
|
|
Re: Grids and paging...
Posted: 05 Jan 10 2:11 PM
|
The syntax for paging on the SLX provider is something like:
SELECT TOP 15 * FROM CONTACT WITH FIRSTROW 16
(you can see it in SLX Profiler when SLX pulls a group)
I have never tried using that on a custom form - but I imagine it might be handy if you have a lot of records in a grid. |
|
|
|
Re: Grids and paging...
Posted: 05 Jan 10 2:25 PM
|
| Nicolas that worked! Thanks a lot. You saved me hours of headaches, I'm sure! |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|