My list just shows the first record and does not include all the items I need.

Title

My list just shows the first record and does not include all the items I need.

Post

Hi,

I am trying to make a field that lists all events that an athlete has won. Currently, I can only get the first event that an athlete won.

To explain further, I have an athlete table (athletes) that includes all the basic details such as name, club, age.

I then have a results table (results) where I fill in the results of an athlete in a competition; picking the athlete's name from a value list and then entering data such as position, event and result.

On the athlete table I want to list every event in which the athlete has a position of "1". Can anyone help me in doing this?

If you want a single field with a list of events you can create a calc field that uses List() function. In this case you would also have to create a new relationship to restrict the list to just position 1.

or

If you're familiar with SQL you could also use ExecuteSQL() in a calc field to query results table for Event where position = 1 and athlete = athlete. If you need things in a single field this is the most flexible method as your query can return multiple fields from the events table and put them into your calc field.

I have just a few months of experience, but have been working with Filemaker quite a lot. I want the result to be in a single field. I am unfortunately not familiar with SQL yet, though I might have a look into it if it is the best option for this. How would I create a new relationship to restrict the list to just position 1?

Create a new calc field in athletes, Name it One_c and the calculation is simply 1

Go to the relationship graph and select the result table occurrence

Duplicate it by clicking the button on the bottom of the screen that looks like ++

Now make the link like you did for the original result table occurrence but add One_c = position as well as whatever you had used in the original

Now make a new calc field in athletes and enter the formula List(result 2::event). (I'm assuming your new table occurrence is called "result 2" and the field you're interested in is "event". If not change the formula to whatever they are.

What is field "WinnerCounter"? I thought you were looking for "Position" = 1 "WinnerCounter" must be a number field and be set to 1 for this to work.

A suggestion for the future is give every record a unique serial number. Then make your links by serial number to serial number. What if you had two athletes named John Smith, it would mess up all your relationships.