SQL Portal Sorting

[DeathRobot]

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

[jbante]

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.

[DeathRobot]

Thanks. I will take a look at that.

[DeathRobot]

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

[jbante]

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?

[DeathRobot]

Hi Jeremy. Yes, that would be fine (and very much appreciated!) Can I send it to you directly?

[Lee Smith]

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.

[robac]

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" ; 

 

"" ; "¶" ; 

""

 )

[eos]

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

 )

[robac]

 

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!