What version of FileMaker are you using?
In FileMaker 12, ExecuteSQL with the DISTINCT keyword can produce such a list
In older versions, a value list can be defined that lists only values from the related records and then the ValueListItems function can list each value from the related records a single time.
Filemaker 10. I started to try what you suggest, but I think I need to be walked through the steps. So far, I have no been able to make it work.
Since I don't know the details, I'll have to make up some names for fields and tables and you'll need to then substitute actual names from your file:
Let's say you have this relationship:
LayoutTableOccurrence::PrimaryKey = Shapes::Foreignkey
The typical "one to many" relationship needed for a portal...
You can then set up a conditional value list that only lists values from the related Shapes records for each record in LayoutTable:
Open Manage | Value Lists and create a new value list. Name it "Shapes". Select the "use values from a field" option. This opens another dialog box.
In the drop down at the top left of the dialog, select Shapes. Click the ShapeName field from which you want to produce this list of unique values.
At the bottom of the dialog, select "include only related values, starting from LayoutTableOccurrence.
Now define an unstored calculation field in LayoutTable, with LayoutTableOccurrence selected in the "context" drop down at the top of the Specify Calculation dialog box. Select Text as the return type.
enter this expression:
Substitute ( ValueListItems ( Get ( FileName ) ; "Shapes" ) ; ¶ ; ", " )
ValueListItems will separate each unique value with a return, producing a vertical list. I've used substitute here to replace the return with a comma and space to produce a horizontal list as you have requested.
This works great, thank you. I have taken the principle and applied it to numerous fields. I would be interested to know what each part of that calculation means/does, but if you don't have time to explain, I am just super glad I was able to achieve the effect I was looking for.
Each of the functions used are documented in FileMaker help. I suggest researching them there first, then feel free to post back here with questions about any specifics that still aren't clear to you. (Unless you know how/why it's set up this way, you may have trouble using it elsewhere...)
Oh, I've read the help document a few times and refer back to it often. However, I get the feeling that there is an underlying programing logic that I don't know and that makes it hard to figure out how to make the functions work. For instance, I have only just understood that different functions are usually nested like the one above. This is nowhere apparent in the help document. Thank goodness for this forum!
Nesting functions one inside another is basic mathematical notation. In math class, you might write sin ( cos theta ) as a way to take the sine of the cosine of the angle theta. You would evaluate the expression from the inside out. The same is true with calculations expression in most computer programming languages and also with Database Systems such as FileMaker.
Perhaps you could recommend something for me to read on basic programming notation. I have neither a background in programming nor in math, although what you just said makes sense to me according to what I remember from High school math.
Just explaining why so many systems don't document this one aspect of calculation expressions--they tend to assume that you already know this detail. I don't really have any specific recommendations that I can make specific to calculation expressions.
I was wondering if you (or anyone) could help me expand this calculation a little bit. I happen to use this calculation all the time in my rapidly expanding database, but now I want to count how many of each value occurs in the related field. So to take the example from above, the new field would read: "square (3),semi-square (1),round (1)" or anything that conveys the same information. Conversely (or maybe as well), I would like to see the values sorted (descending) by how many of each occurs in the related field. Is either option possible?
Thanks in advance.----------------------Cora