What's that Function to get a List of values...

[Wickerman]

I can't seem to find it, but I know its here . . .

I need to get a return-delimited set of the unique values for a particular Field -- for instance if I had a Table of Members with a COUNTRY field and I wanted just a simple list of the Countries represented in my Found Set, without repetitions. . . .

Canada

Egypt

France

etc.

I'm needing this to provide the Y-Axis values for a bar chart that shows how many Members are from each country . . . I'm figuring defining a Field like this is the best way?

thanks

[bcooney]

Too bad you don't have a Countries table, especially if this Country field is a simple text field. You might have US, USA, United States, America, etc.

I'd look at Dunning's site for a CF since you indicate that you want to limit the result by found set. Otherwise, set a calc text field to a value list.

[Kris M]

List ( field {; field...} )

The syntax of the list function is List ( field {; field...} ) and it was introduced with the release of FileMaker 8.5 and it will return a text base result.

The list function will provide you a return-delimited data list from one or more related data field(s) or a repeating field(s).

[Wickerman]

Actually, bcooney, I do have a Countries Table -- (I'm actually having to do a bunch of charts involving a variety of fields and tables, some involving Join Tables, others not, so I'm trying to get my head around the various means of getting these y-axis values).

1) " . . . Otherwise, set a calc text field to a value list. " -- whats the calc function for pulling values from a Value List?

2) Kris -- in the simple Table situation I described, would I have to make a self-join Table Occurrence to do that? So I make a field in the Members Table with List(Country) where there 'Country' field is targeted at that field in a related TO of the same Table? (you can't use List just within a single table, right?) -- and this yields unique values only?

[Wickerman]

Kris -- never mind my followup Q -- I've got that List function working fine.

bcconey -- still would appreciate figuring out the Value thing for cases where I don't have a separate Table for the list of values.

I understand how to make a Value List based on Field Values . . . but not sure I've ever made a Field calculated out of a Value list!

[Echo33029]

To use the List function to pull in the values as you described, the parameters have to be related records. Since you are using FMA 12 you can use the ExecuteSQL function to do the exact same thing, regardless of which TO context you are in currenty. I have had to do this a couple of times to generate a list of IDs for a given search criteria in a table, then stuffing the result into a global field for use in a relationship to display a portal.

[Wickerman]

Hmmm . . . okay, I'm aware of the ExecuteSQL function, but trying to figure out in one case whether I need to use it....

Let's Say I have a join-table situation like this:

Event < Attendance > Member

(where an Event is attended by many members and each Member can attend many events)

And I want to make a Bar chart with the Members running down the Y-axis, and the X-axis providing a Count of how many events they attended. I've tried using the 'Quick Chart' example in the Help Menu example, but it doesn't seem to work... so to do it by hand...

1) I make the chart on a Layout based on an Attendence TO, right?

2) I have a COUNT summary field in Attendance, and have set up a sub-summary layout part which, in List View, is accurately showing me the summarized count of Events attended by each Member.

3) THe same COUNT field, placed in a Leading Grand Summary, is shown me the Total attendance for all events. THat's good too.

So, I seem to have all the info I need for the Chart, but am having a hard time getting it to work.

Q1) What Part do I put the desired Chart in?

Q2) For the Y-Axis (Member) values, do I just select the Related MemberName Field from the related Table? Or do I need to pull those values in by using the List(MemberName) approach . . . or . . .?

Q3) Do I just put the COUNT summary field on the X-axis? I'm fuzzy on how this relates to Q2 . . .

I'm maybe in over my head on this -- it seems like it should be simple, but I can't get it to work. It seems like if I have a Sub-summary layout displaying the exact information I'm wanting to chart, it should be pretty straightforward, but I'm not getting it right.

:-/

[bcooney]

ValueListItems

http://www.filemaker.com/help/html/func_ref1.31.45.html

I use Get (FileName) in the filename spot.

[Wickerman]

THanks bcooney -- that's useful for me.

After going around in circles for awhile, I've solved the charting challenge I described above:

Chart layout is on Attendance context.

Q1) Chart goes in Grand Summary Part

Q2) Y-Axis is just MemberName from the related Member Table

Q3) Yep, simple Count summary Field (in the Attendance Table) goes on X-Axis.

The main thing to get right is to set the Data Source to 'Current Found Set' to display "Summarized Groups of Records" - and of course remember to sort by MEMBER NAME.

It's pretty once you get it done!