Data-shaping and same-page sort (2nd post)

Results 1 to 2 of 2

Thread: Data-shaping and same-page sort (2nd post)

  1. #1
    Join Date
    Dec 1969

    Default Data-shaping and same-page sort (2nd post)

    I&#039;ve got a data-shaped page that has a parent column, child column and grandchild column. In the child and grandchild columns, I&#039;d like people to be able to click on data to re-sort the entire page by that data. For example: <BR><BR>column 1 &#124&#124 column 2 &#124&#124 column 3 <BR>employee &#124&#124 employee expertise &#124&#124 employee clients <BR><BR>So, when someone clicks on "Acme, Inc." in column 3, the page would be re-sorted so that everyone on the Acme, Inc. client team would appear. Similary, if someone clicks on "Technology" in the expertise column, everyone with technology expertise would appear on the page. <BR><BR>Here&#039;s my SQL: <BR><BR>&#060;% <BR>Dim DataShapeSQL <BR>DataShapeSQL = "SHAPE {SELECT * FROM employees ORDER BY lname ASC}" <BR>DataShapeSQL = DataShapeSQL & "APPEND ({SELECT * FROM clients LEFT join clientsandemployees on clients.clientID LIKE clientsandemployees.clientID ORDER BY clientname}" <BR>DataShapeSQL = DataShapeSQL & "RELATE empID TO employeeID) AS rsClients_Link," & _ <BR>" ({SELECT * FROM expertise LEFT join expertiseandemployees on expertise.expertiseID LIKE expertiseandemployees.expertiseID ORDER BY expertise}" & _ <BR>"RELATE empID TO emploID) AS rsExp_Link" <BR>set rsMedia = Server.CreateObject("ADODB.Recordset") <BR>rsEmployees.Open DataShapeSQL, MM_connOLEDB_TCubed_STRING <BR>set rsExp = Server.CreateObject("ADODB.Recordset") <BR>rsExp.Open DataShapeSQL, MM_connOLEDB_TCubed_STRING <BR><BR>%&#062; <BR><BR>The problem is that to sort the parent column, I need to join it somehow to the expertise and clients columns, but when I do a left join in the first "Select * from employees..." statement I get duplicate records. So, how can I get the client ID and expertise ID associated with the employee record so that sorting can occur on the same page, and avoid duplicate records? <BR><BR>Hope that makes sense... and THANKS for any help. <BR>

  2. #2
    Join Date
    Dec 1969

    Default Seems to me

    beacuse of the way you need to sort the data you are esentially overriding the parent chile type relationship inherent in the shaped recordset. i would think a series of inner joins instead of the shaping would serve you better. Then use code like<BR><BR>lastFieldval = rs("fieldname")<BR>response.write lastFieldval<BR>Do while not rs.eof<BR> If lastFieldval &#060;&#062; rs("fieldname")<BR> lastFieldval = rs("fieldname") <BR> response.write lastFieldval<BR> End If<BR> rs.Movenext<BR>loop<BR><BR>obviously expanded to handle the multiple columns you have. Use the Order By clause in the select to pick out correct data for page (eg 10 records per page say)<BR><BR>Make sense??<BR><BR>The problem with the shaped data is that it automatically limits sorting by child recordsets as their sorting is ovridden by parent sort.<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts