Grid - Sort Orders using Apha when the "where" references int for auto lookup

Scriptcase 8 Discussion
, ,
#1

Thank You to anyone who can help solve this issue.

Grids that have several fields using the grid lookup - lookup method -> automatic -cannot be sorted alphabetically.

The SQL using the ‘where val = {val}’ to lookup the details in another table (where {val} points to an int datatype)

PROBLEM: When the end user uses the sort by options on these fields (for example: employee -> sort by first name) the sort is ALWAYS triggered by the record index of INT datatype.

Is there any way to get the grid to “SORT BY” using the alpha rather than doing the sort by on the index or primary key (see img -> method03.jpg)

Also, Is there a way to call a method (I need to use some logic i.e. ‘if then’ or ‘switch’) from the grid lookup? For example, If an employee’s record is marked “terminated” then select employee records where term = true else select all records. (see img -> method01.jpg and method02.jpg)

Thanks!!

[ATTACH=CONFIG]n69841[/ATTACH] [ATTACH=CONFIG]n69840[/ATTACH] [ATTACH=CONFIG]n69839[/ATTACH]

image_3060.jpg

image_3061.jpg

image_3062.jpg

#2

Has no one every required this feature? Seriously?

#3

kholliday, I’ve asked for the ability to sort by alfa a couple of times…

Now that I’m much more comfortable (by no means -good at) with “how” SC does things, What would be nice is rather than being limited to SQL select statement ONLY in the grid lookup -IF the SC people would allow us to call a php or javascript method THEN based on other criteria within the grid, we could run either if then else or switch to select the SQL select statement we need. It’s frustrating to be limited to the basic SELECT statement ONLY. I don’t like to “figure out” how to do the if logic inside the SQL.

#4

Found a possible approach based on this: http://dba.stackexchange.com/questions/39278/update-column-based-on-the-sort-order-of-another-query

Perhaps there is a way to create an INT column (ordered_names) and update it on changes to the database following the example above:
UPDATE ordered_names SET sort_order = (@x:=@x+1) ORDER BY firstname,lastname; Maybe try to run this as either a database trigger (SC independent) or as part of your SC application before the grid is refreshed and then sort in SC based on INT column ordered_names, which should be the alpha sort you wish.

#5

BradK, Thank you for the help… I will try the solution (currently some of the dba.stackexchange link and your solution is a little over my head).

Frankly, I’m in agreement with kholliday, I find it very difficult to believe I’m the only one on this forum (with consideration that SC NetMake provides NO support) to have this kind of issue.

​IMHO SC could provide a very simple fix simply by providing “sort OPTIONS” in the grid -> fields ->{field} -> Grid Lookup … provide a dropdown (from the sql) to identify which field to provide the ‘sort’ ability.

#6

stubuck: Gave me a headache too. I had to read it a couple of times until it soaked in. Your suggestion is a good one. If it is simple to implement for SC, perhaps we will see it soon in a future release.