8 Replies Latest reply on Mar 26, 2013 10:48 AM by philmodjunk

    Special Query  PART 2

    ahutler

      Title

      Special Query  PART 2

      Post

           Hi,

           I need help with response to an earlier question/answer.

           See below, where/how do i place the sequel statement? Layout mode? attached a field in a calculation type text???? I don't know how to do the provided solution.

            

           "Special Query

           Post:

           I would like to make a list of all of the artists' names in our collection (in our filemaker db), however if we have multiple pieces by a specific artist, I DO NOT want their name repeated 10 times on that list.

            

           Is there a command or script or a something that will allow me to do this?

      Reply to this Post

           In what format do you need that list?

           A value list based on this name field will list all names a single time. And ValueListItems can be used to pull that list into a single text field if that is of value.

           A summary report can be created with one sub summary part and the body layout part removed. Put the name field in the sub summary part and sort by name (or corresponding ID). Just be sure to sort by the field specified as the "when sorted by" field in the sub summary part's set up. (This layout will appear blank in browse mode until you correctly sort the records.)

      ExecuteSQL can be used to also list the names in a single field if you use the DISTINCT keyword.

           Or you can create a table with one record for each artist. You can set a "unique values, validate always" validation on the name field and import all the records from your existing table into this new table and the validation rule will filter out the duplicates."

        • 1. Re: Special Query  PART 2
          philmodjunk

               It would have been simpler just to use Post A Answer, to post your follow up question. That would have saved you some effort here.

               You haven't answered the question you were asked:

          In what format do you need that list?

               Depending on the format of what you want to see, Execute SQL might not be the ideal method to use here. If you don't have FileMaker 12, it's not even an option to consider.

               In FileMaker 12, ExecuteSQL is a function that you can define in an unstored calculation field to return a list type list of rows of data inside a single field. The result can be quite similar to using a portal to display related records, but the use of SQL makes for many options not easily accomplished with just a portal.

               Consider posting an example of how you want this this to look like.

          • 2. Re: Special Query  PART 2
            ahutler

                 I just want a text list of each unique value in a column

            • 3. Re: Special Query  PART 2
              philmodjunk

                   And nothing else is on the layout?

                   Drawn from all records in the table?

                   What will you do with this list once you have it?

                   Can it have a scroll bar if the list is longer than the space alotted?

                   Three other (than SQL) very simple options to set up for such a list:

                   1) Set up a List view layout with the body layout part removed. Replace it with a sub summary layout part "when sorted by" the name field.

                   Return to browse mode. Select View as List if not already specified. Sort your records by this same name field and you'll get a list of names with every name listed once.

                   2) Define a new table with the same name field. Specify a "Unique values, Validate always"  field option on the name field. Import all your records into the new table. The validation options will filter out the duplicates and any list or table view of this data will list all names without inclusion of duplicates.

                   3) Define a value list using the "use values from a field" option to list the names in this table. Define an unstored calculation field with this calculation:
                   ValueListItems ( Get ( FileName ) ; "ValueListNameInQuotesGoesHere" ) and the field will list all the names, sorted in ascending alphabetical order with no duplicate names.

                   This third option produces results identical to ExecuteSQL but does not require FileMaker 12. Execute SQL, on the other hand can produce more sophisticated results such as records sorted in a different order and filtered by a WHERE clause instead of just listing all unique names in the table.

              • 4. Re: Special Query  PART 2
                ahutler

                     I tried the first option you suggested and i must not be doing it correctly.

                     Please see the attached copy of the layout mode for option 1 that i created. When I return to browse mode nothing is showing up, no artists names. It doesn't recognize the field (it doesn't call it artist's name, it calls it 'field 118'???), also attache. We currently have all records entries attached to an Artists name. There are 7,000+ records in this database/table, we have some Artists repeating in the database, because we have multiples of their work. The object/artwork is the unique identifier. My goal is a list of all of the artists in our collection, but I don't want 7,000+ names on the list.

                      

                      

                      

                      

                      

                • 5. Re: Special Query  PART 2
                  ahutler

                       browse mode

                  • 6. Re: Special Query  PART 2
                    philmodjunk

                         To start, you are in table view instead of list view.

                         Then you should have your records sorted by this field, not Unsorted.

                         I have no idea why it shows field 118 as the column name--but then I've never tried table view in a layout where there is no body layout part so maybe that's a factor in the odd column header. It shouldn't be a factor once you select List View.

                    • 7. Re: Special Query  PART 2
                      ahutler

                           Here is how it looks in List View

                      • 8. Re: Special Query  PART 2
                        philmodjunk

                             And your records are still UNSORTED.

                             Sub Summary layout parts are not visible until you sort the records to group them in a manner compatible with what you specify for that sub summary layout part as the part represents a group of records in your found set.

                             Select Sort Records from the Records menu.

                             Sort them, specifying the same name field that you specified as the "when sorted by" field for the sub summary layout part.