Sorting Interface using virtual sort

[pdipotet]

In reference to: http://fmforums.com/forum/topic/83953-portal-sorting-with-fm12/?p=408698

 

 

Hi, I am doing a task and I am thinking to use this Virtual Sort from Jeremy, by the way, excellent!

But I don´t have any portal and what I need is to make something like the Sort Dialog Window, I know how to coose fields, maybe comments clarify them better for the end user and with the field list I´d like to do the same you did in your work jbante, but, have you anything more to help me? any advise or something to use your modules in a normal layout and sorting data with your scripts, I have no buttons, I´d have the list of fields and the order (ASC / DESC) of each one separated by return in each line. I think there is a core but I need something else. I´ve been testing but I got nothing yet. any help?

i watched another similar post at the forum but decided to make a portal and just order by one field, I guess we can do everything with this VirtualSort tool.

Thanks in advance!

Edited October 7, 2013 by mr_vodka
Split topic since it was not about portal sorting - mr_vodka

[jbante]

Why is it that you'd like to create a sort dialog window for your users? From the way you've been talking about portals, I get the impression that you're trying to sort a found set of records instead, but it sounds like you think the interface needs to change. In the Virtual Sort demo file, the last example demonstrates sorting a found set using the same field-label-clicking interface as the portals. The "Virtual Sort: README" script includes notes on how to achieve that effect.

 

If you really want your users to have an interface similar to FileMaker's sort dialog, why don't you just give them FileMaker's sort dialog by using the Sort Records [] script step without disabling the dialog? It's a lot less work. If you're really intent on re-inventing the wheel, once you have the desired sort criteria from your users, you can feed that into the "Refresh Virtual Sort by SQL ..." script.

[pdipotet]

Dear Jeremy,

I agree with you, but orders are not discussed, as you know!
I am not dealing with ListView or portal sorts, no, I have to do my own sort window.

The refresh script, as you said, doesn´t work with a list of fields separated by return, it doesn´t work for me, I´ve been trying several times. I am thinking that maybe if I make a Loop calling to the SQL New Field script for each field to order I could get what I need but I have to change several instructions in your scripts and that is not easy for me as a developer with few experience in FM.

I would like to use the way you make the SQL script step and I like your solution but I wonder if is it possible to add something for sorting just giving the field list separated by return.
I´d appreciate all the help you can give me.

Thanks again for everything!
 

[jbante]

Are you sure the "Refresh Virtual Sort by SQL ..." script isn't working for you? The "Virtual Sort by SQL with New Field ..." script in the Virtual Sort module works by delegating all the real work to the "Refresh Virtual Sort by SQL ..." script.

 

Are you sure you have the script parameter formatted correctly, using fully-qualified field names (using both the table name and the field name), in Let notation? To see what it's supposed to look like, try running the "Virtual Sort by SQL with New Field ..." script with the Script Debugger, and stop execution at a breakpoint immediately after the point where the "Get Virtual Sort Field List ..." script has run, and look at Get ( ScriptResult ) in the Data Viewer. Can you post an example of what the script parameter you're sending looks like?

 

Since it sounds like you're sorting a found set of records (rather than a portal), are you sure you have an OnModeEnter script trigger setup to sort on the Virtual Sort field? When you run through everything in the debugger, is that getting triggered?

 

If none of those is getting you closer to a solution, what are you seeing happen in the $$~VirtualSort* global variables when you try to sort?

[pdipotet]

Well, I used something like this as a parameter for Refresh...... script: "$primaryKeyName = " & Quote ( GetFieldName (t_rp_judges::id ) ) & " ;¶"
& "$sortFieldName = " &  Quote( GetFieldName (t_rp_judges::SSN ) )  & "¶" &  Quote( GetFieldName (t_rp_judges::DOB ) ) &  " ;¶"

Global values get the last value for order, I mean DOB, I can´t get these two fields together for sorting, no way!

anything wrong? Maybe I do not use the data serialization (Let Notation) is that the problem? I have the page opened and I´ll start to read it and learning too.

 

I also have been suffering making the UI as you said, I´ll have to use portals for simulate the list of fields and move and clear buttons. It´s a very hard task as you well imagine, but I don´t understand because I can see every body using List view, ordering with it and later go to the details layout, in each FM solution is always the same!!!

 

I´ll see the Let Notation, i hope to understand because I speak spanish... ) maybe you got that before! In spanish forums there is nothing like in this one.

[jbante]

It looks like you have mostly the right idea about how to format the Let notation, but there are 2 issues I see what what you're showing:

1. $sortFieldName is not a parameter accepted by the (current version of the) "Refresh Virtual Sort by SQL ..." script. (Perhaps a past version did; I don't remember that detail, but it's worth comparing your copy to the version currently on GitHub.) It's the $orderBy parameter that's expecting a list of fields to sort.
2. With the list of fields to sort, you only have to Quote() the whole list, not each value in it: Quote ( GetFieldName ( Judge::ssn ) & ¶ & GetFieldName ( Judge::dateOfBirth ) ), not Quote ( GetFieldName ( Judge::ssn ) ) & ¶ & Quote ( GetFieldName ( Judge::dateOfBirth ) ).

[pdipotet]

Ok Jeremy, I´ll try and I´ll tell you. But I am sure I am using the last version, I download the last version you have given here as a link and in show .... script it says the second parameter is this list separated by return. I will check everything and then I´ll tell you. Thank you very much!

[pdipotet]

Dear Jeremy, you were right, just changing the parameter variable and just one Quote function and everything OK, as I supposed  it works perfectly. Here it´s the parameter i passed then:

"$primaryKeyName = " & Quote ( GetFieldName (t_rp_judges::id ) ) & " ;¶"
& "$orderby = " &  Quote( GetFieldName (t_rp_judges::SSN )  & "¶" & GetFieldName (t_rp_judges::DOB ) ) &  " ;¶"

 

I also tested with field names with intermediate spaces and the way to order descending and everything OK, for example:

"$primaryKeyName = " & Quote ( GetFieldName (t_rp_judges::id ) ) & " ;¶"
& "$orderby = " &  Quote( GetFieldName (t_rp_judges::Last Name )  & "::DESC¶" & GetFieldName (t_rp_judges::SSN )  & "¶" & GetFieldName (t_rp_judges::DOB ) ) & " ;¶"

 

So, I guess everything is OK now and I just need to deal with the UI and make the parameters list depending on what the user chooses, but I have to put them in just one variable because the script parameter must be the same always. Any tips would be appreciated too.

By the way, i didn´t find a solution with an UI similar to the Sort dialog programmed in FileMaker, I mean moving values from a list to another one, you know, something usually seen in other softwares. I feel lucky I found your solution, thanks a lot!

[pdipotet]

Uffff, an 8309 Error at the end of the road!

When I include a related table field in the list of parameters I got a big problem and that number doesn´t appear in the list of errors help.

Jeremy, another thing I´m missing? If I include & Getfieldname(t_phones::number) the Refresh....... script does not work anymore and I receive 8309 Error when sorting!

[kigmaker]

Hello Jeremy,

 

I have duplicated the 500 records in your demo for a total of 1000.

Sorting more than 997 records returns:

 

“there was an error sorting records.

Error: 1202”

 

I am trying to incorporate your module into other files and have found the same error.

 

Any suggestions?

[jbante]

pdipotet, would you be willing to share the file you're working on? If not, would you be willing to show it to me in a GoToMeeting screen sharing session? That's a new one to me; I'd like to see that. Message me off-thread.

 

kigmaker, When sorting the current found set of records, Virtual Sort passes each primary key of each record to the ExecuteSQL function as a parameter, so the technique is limited by how many parameters the ExecuteSQL function accepts. Judging from your results, that limit is 1000 arguments (1 query + 1 column delimiter + 1 row delimiter + 997 primary keys = 1000). "Stick to sorting smaller found sets" is the only recommendation I have for you. Sorry. I'm adding better error reporting for this particular problem to the module roadmap for future development.

[kigmaker]

Jeremy,

 

Thanks for the quick answer.