Jump to content

SQL Portal Sorting


This topic is 2532 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I am using a slightly modified version Mr_Vodka's SQL sorting method to sort a portal based on a field in the table:

 

ExecuteSQL (
"SELECT
     id
 
FROM " 
     &  $$portal  &
 
" ORDER BY "
     & Case ( MiddleWords ( FieldType ( Get ( FileName ) ; $$p ) ; 2 ; 1 ) = "Text";
          "LOWER(" & $field & ")";
          $field )
     & Case ( $$sort_desc; " DESC" );
 
""; ""; "" )

 

I've also created a variation using LEFT JOIN so that I can sort on fields in the portal that are from a related table:

 
ExecuteSQL (
"SELECT "
     & $$portal & ".id
 
FROM " 
     &  $$portal  &
 
" LEFT JOIN " &
     $$relatedTable & " ON " & $$portal & "." & $$relatedID & " = " & $$relatedTable & ".id
 
ORDER BY "
     & Case ( MiddleWords ( FieldType ( Get ( FileName ) ; $$p ) ; 2 ; 1 ) = "Text";
          "LOWER(" & $$relatedTable & "." & $field & ")";
          $$relatedTable & "." & $field )
     & Case ( $$sort_desc; " DESC" );
 
""; ""; "" )

 

I now have one field I would like to sort on that is from a related table two tables away and connected via a join table. Is that possible with SQL syntax? Is it possible to specify when table1.id = table2.id1 AND table2.id2 = table3.id?

 

Thank you,

Michael

Link to comment
Share on other sites

Yes, this is possible. You might want to look at the Virtual Sort module on ModularFileMaker.org. It encapsulate's John's technique so you don't have to worry about the SQL if you don't want to. It uses the design functions to get the information it needs to build the SQL query from the relationships graph. Even if you don't want to use the module itself, you might look at how it works to inform your own approach.

Link to comment
Share on other sites

Hi Jeremy,

 

I downloaded your brilliant Virtual Sort module, and am having trouble incorporating it. It seems to break during the script:

 

"Set Virtual Sort Variables ( sortedValues ; tableName ; orderBy { ; sortID } )"

 

at step:

 

"If [isEmpty ( $sortedValues ) or IsEmpty ( $tableName ) or IsEmpty ( $orderBy ) or IsEmpty ( $sortID )]

 

$sortedValues IS empty. Wondering if you can see anything I've done incorrectly.

 

1) Imported custom functions and all scripts (I'm using FM13A)

 

2) I have a layout based on table "Projects" with a portal on it called "Projects_ProjectsFilter"; a self join TO for showing found/filtered records from the main table.

 

3) In the Projects table definitions, I added the unstored calculation "virtualSort01 [value: VirtualSortPosition ( id ; "" )]"; copied directly from your sample database, so type and storage match yours.

 

4) On the portal layout, I included an onRecordLoad script trigger of "Refresh Virtual Sort by SQL… [value: "$primaryKeyName = " & Quote ( GetFieldName ( Projects_ProjectsFilter::id ) ) & " ;¶"]"

 

5) The portal is sorted by "virtualSort01"

 

6) On the portal layout is a button (outside of the portal ) that calls the script "Virtual Sort by SQL with New Field [value: "$primaryKeyName = " & Quote ( GetFieldName ( Projects_ProjectsFilter::id ) ) & " ;¶"

& "$sortFieldName = " & Quote ( GetFieldName ( Projects_ProjectsFilter::jobNumber ) ) & " ;¶"
& "$sortID = 1 ;¶"]"
 
When sorting, I get error 10. When I switch from Layout to Browse mode, I get error 8309 (both are your custom dialogs). I've been going over everything, and can't see what I'm doing wrong.
 
Thank you!
Michael
Link to comment
Share on other sites

Everything you've described appears to be in order. Are you be willing to share a copy of your file with me so I can take a closer look?

Link to comment
Share on other sites

It is preferred that files being used to help a member be attached to a Reply here..

 

After all, this is a learning Forum, and having the Attachments available here can be of help to others having a similar question.

Link to comment
Share on other sites

  • 2 months later...
  • Newbies

Hi guys, I'm hoping you can help me out with a similar issue. I'm having some trouble specifying my sort order (desc or asc). I'm trying to pass this into the ExecuteSQL function but I can't get it to work.

 

The following calculation is working. I simply want to replace the ASC with a variable that is already stored in my script as $$SortOrder (either ASC or DESC). 

 

Thank you for any help

 

-Rob

 

 

ExecuteSQL ( 

 
"SELECT a__kp_NoteID
FROM Notes
ORDER BY CustomerNameFull ASC" ; 
 
"" ; "¶" ; 
""
 )
Link to comment
Share on other sites

The SELECT statement is just a text string that you pass as first argument to the ExecuteSQL function; so you need to observe Filemaker syntax for building a string from literals and references: 

 

ExecuteSQL ( "
  SELECT a__kp_NoteID
  FROM Notes
  ORDER BY CustomerNameFull "$$SortOrder
  ; "" ; "" // CR is default record delimiter
 )
  • Like 1
Link to comment
Share on other sites

  • Newbies

 

The SELECT statement is just a text string that you pass as first argument to the ExecuteSQL function; so you need to observe Filemaker syntax for building a string from literals and references: 

 

ExecuteSQL ( "
  SELECT a__kp_NoteID
  FROM Notes
  ORDER BY CustomerNameFull "$$SortOrder
  ; "" ; "" // CR is default record delimiter
 )

 

 

 

Thank you, so simple. I was placing the quotation mark after $$SortOrder. 

 

I appreciate your help very much!

Link to comment
Share on other sites

This topic is 2532 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.