3 Replies Latest reply on Oct 16, 2012 12:43 PM by philmodjunk

    Where do I put my ExecuteSQL calculation?



      Where do I put my ExecuteSQL calculation?


           Do I create a new field the table that I want to query in and use an autocalculation where I use ExecuteSQL? Or do I create a new layout where I can put my query fields as I continue to select various pieces of data? I find it confusing to wrap my head around where I put my ExecuteSQL statement because it will only produce a single result of text. For a simple example I want to find the distinct values in a field called 'Tray Name'. How and where do I do this? 


        • 1. Re: Where do I put my ExecuteSQL calculation?

               You have multiple options, but the results of ExecuteSQL will not produce a found set of records so that is not an option you can pursue here.

               It's a lot like setting up a portal to display results on your layout. The results will be listed in a single field or variable so you do have to put such an object on a layout and then size the field to be large enough to list the results. You can specify a scroll bar for this field if that helps. You can get properly aligned columns of data by using the tab character as your field delimitter and then specifying the appropriate tab stops on the Inspector's appearance tab.

               You can use a calculation field for this or use a script to assign the results to a variable.

               I don't recommend using an auto-enter calculation, however. That approach will not update automatically as values are added/removed/changed.

          • 2. Re: Where do I put my ExecuteSQL calculation?

                 If I would like to for example "SELECT DISTINCT ShippedTrays FROM DataSheet" where would I make this field? Right now I have it as an extra field in DataSheet, but now I have the query results repeated 1000 times when I look at the DataSheet in table view. Do I have to make another table to put this field so that it doesn't do the calculation for every record that already exists?

            • 3. Re: Where do I put my ExecuteSQL calculation?

                   Why would you want to see this list in TableView?

                   What result are you trying to produce on your screen?

                   ExecuteSQL, unlike other calculations is not restricted to any specific table as it forms it's own relationships as part of the SQL, but in order for the contents of the calculation field to be visible, it must be from a table occurrence that works for your current layout. That depends on what relationships exist.

                   If you use Layout Setup to make the header visible, you could put this field in the header of your table view and it will then list these items once.

                   IF you want to list all unique records from ShippedTrays once, there is a way to do that with a list view and sub summary parts that does not use ExecuteSQL.