Force a POST sort on xBrowse

[Rick Lipkin]

To All

I have an Encrypted Sql table with a field called "EmployeeName .. Select * From Rates order on EMployeeName .....

That is fairly straight forward, but the Field "EmployeeName" is Encrypted .. To Unencrypt the EMployeeName Column I use the old Trick Add oCol ( see below ) and Have a user defined function that Dencrypts the EmployeeName field .. so when a user call the program .. all they see is the un-encrypted EMployeeName.. ( great )

However, when the table was opened it was ordered with the encrypted EmployeeName values ... which once xBrowse is created now has the un-encrypted EMployeeName out of senquence with the encrypted table value ..

Is there a way to re-sort the EMployeeNAme column on its dencrypted value ?.. after xprowse has been painted with the encrypted value ? .. The Sort:EmployeeName ( now de-encrypted) does nothing ...

Any Ideas ?

Thanks
Rick Lipkin

Code: Select all  Expand view  RUN

cSql := "Select * From [Rates] "
cSql += "Order by [EmployeeName]"

TRY
   oRsRate:Open( cSQL,xCONNECT )
CATCH oErr
   MsgInfo( "Error in Opening RATES table" )
   RETURN(.F.)
END TRY

.....
.....

REDEFINE xBROWSE oLBX             ;
       RECORDSET oRsRate                 ;
       COLUMNS "EFFECTIVEDATE",          ;
               "EMPLOYEERATE",           ;
               "ACTIVE"                  ;
       COLSIZES 80,80,65                 ;
       HEADERS "Effective Dt",           ;
               "Rate",                   ;
               "Active"                  ;
       ID 111 of oDlg                    ;
       AUTOSORT AUTOCOLS LINES CELL

   oLbx:nMarqueeStyle := MARQSTYLE_HIGHLROW
   oLbx:lRecordSelector := .f.
   oLbx:lHScroll := .f. // turn off horiz scroll bar

   ADD oCol to oLbx AT 1 DATA {|x| x := _ChkUser(oRsRate:Fields("EmployeeName"):Value) }   HEADER "Employee Name" size 200  ;
                         Sort:EmployeeName


//--------------------------------
Static Func _ChkUser( cEmployeeName )

Local cName

cEmployeeName := alltrim(dencrypt(cEmployeeName ))

cName := substr( cEmployeeName+space(36),1,36)

Return( cName )

 

[nageswaragunupudi]

If this is only a readonly browse, please read the data into an array with

Code: Select all  Expand view  RUN

aData := oRs:GetRows( {colnames} )

Then decrypt and browse the array

[Rick Lipkin]

Rao

It appears the sort order is based on the original encrypted data ... When I build the browse I am de-encrypting it on the fly ..

Code: Select all  Expand view  RUN

 ADD oCol1 to oLbx AT 1 DATA {|x| x := _ChkUser(oRsEmp:Fields("UserID"):Value) }   HEADER "UserId" size 85 ;
                              SORT "UserID"
   ADD oCol2 to oLbx AT 2 DATA {|x| x := _ChkCust(oRsEmp:Fields("FullName"):Value) } HEADER "Staff Name" size 150 ;
                              SORT "LNAME"
 

When I click on the USER ID Column header .. it does not sort properly based on the cell data .. it appears to sort on the original encrypted table data

Code: Select all  Expand view  RUN

"Select * From [Staff] Order by UserId" // where user id is encrypted
 

Any thoughts on how to sort ( or resort ) the ( de-encrypted cell ) UserID column so it will display properly .. apparently the sort is being controlled by the way the table was opened in its original encrypted format ..

Thanks
Rick Lipkin

[James Bott]

Rick,

How about de-encrypting into an array, then browsing the array.

[Rick Lipkin]

James

Rao made the same suggestion .. however, I do not want to de-encrypt and copy an entire table to an array or temp table .. because now I compound how many places I have to write updates .. If I add\edit or Delete a record .. I have to update a row or rows in the array and then write the same information back to the Sql Table .. gets really messy.

I was hoping for a simpler solution and the sort is just cosmetic .. however, the legacy application is sorted by Name and I would like to maintain the same look an feel .. was hoping for a simpler solution .. I was hoping the arrow column sort would sort the data you see in the browse.

Thanks
Rick Lipkin

[Rick Lipkin]

Rao

Just a quick thought .. could you create a new xBrowse method oLbx:Sort("Column Name" ) ???

Usage Like
ON PAINT ( oLbx:Sort("EmployeeName"),oLbx:ReFresh() )

Thanks

Rick Lipkin