2 Replies Latest reply on Jan 25, 2015 6:39 AM by philmodjunk

    Execute Sql SELECT query output show in a listview or portal

    naiefmadathadi

      Title

      Execute Sql SELECT query output show in a listview or portal

      Post

      I am selecting some fields from different related tables and some where conditions ,now i need to show the result in a layout as a listview. can any one help me to do this,i am new to filemaker,please help me.

      now my result is in a global variable.

       

        • 1. Re: Execute Sql SELECT query output show in a listview or portal
          ChrisJohnston

          I am thinking that you are trying to understand how to write the ExecuteSQL statement to grab what you want, and then how to populate a field in the table you want to show, correct? A script can take something like this and set it equal to a variable and then write that to a field. Something like in the picture when you can then populate the fields because of the criteria in you $$globlaField. Imagine (use picture) to think of a scenario where your Activity:: and Company:: tables are not related but you have some global data that relates in some way. That data could be what ExecuteSQL uses to find the data you want in a non-related table.

          http://pctechtv.com/friend/fmforum/xSQLhelp/xSQLstart.txt

          • 2. Re: Execute Sql SELECT query output show in a listview or portal
            philmodjunk

            If you want to produce a set of records to see in list view or a set of records in a a portal, it will be much easier to do that without using SQL in FileMaker, but it is possible to get that result using executeSQL()--the function, not the script step.

            For an example of how to use a script to produce a found set without using SQL:Scripted Find Examples

            To use ExecuteSQL, to pull up a found set IS possible if you define a relationship to use with that field in Manage | Database | Relationship:

            Table1::cESQLField = Table2::PrimaryKey

            Your query might be:

            ExecuteSQL ("SELECT PrimaryKey FROM Table2 WHERE Table2.Field = 'Apple' " ; "" ; ¶ )

            This produces a return separated list of values from PrimaryKey in the cESQLField. You can then use GoToRelatedRecords to pull up the found set of matching records on a layout or a portal to Table2 on your Table1 layout could list those same record. Note that ORDER BY is not a meaningful result in this technique as it will not effect the sort order of the found set of records nor the order of the records shown in the portal.