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.
I just want a text list of each unique value in a column
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.
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.
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.
Here is how it looks in List View
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.