Use the List() function. Calculation (result is text) in your Trails table:
Substitute ( List ( Obstacles::Description ) ; ¶ ; ", " )
or just " " if you want only a space and no comma between them.
Unfortunately this didn't work. Its just displaying each obstacle_ID description. Not combing multiple fields descriptions with the same obstacle_ID into one field.
I might have misunderstood. Here are some things to check:
* Am I correct that one trail can have many Obstacles?
* You want the result per trail to display in your Trails table?
* In your first example, you show the same ObstacleID twice (01) and the Obstacles table should only have uniqueIDs.
* Are they are related as: Trails::ObstacleID = Obstacles::ObstacleID?
* Did you create a calculation (and not a regular text field with auto-enter calculation)?
* You do not need a TrailsID in Obstacles because Obstacles is the 'one' side ... or is this a many-to-many table? If so, you are missing a table.
If there is one trail with many obstacles and if you have it joined as indicated and it is type calculation (result is text) then when viewing Trails ID 0001,there will be two related Obstacles records (01 and 02) and it will work as expected. Let us know how we can adjust it for you. :-)
I believe I'm missing something regarding the relationships of tables so I've probably set it up wrong. But here goes. I have a layout called Trails. Within Trails I have a portal called Obstacles. Each obstacle has a separate ID which I access with a drop down menu. I have two table (Trails & Obstacles) with a relationship between trails::trail_ID to obstacles::Trail_ID & trails::obstacle_ID to obstacle::obstacle_ID. Maybe this is the "many to many" your'e talking about. Thru hit and miss this is the only way I could find to view the different obstacles_ID. Change to obstacle 2 with a drop down menu and the rest of the record refreshes with the new information. Keep in mind I am not a database programmer so I"m thinking I need to go pick up FMP11 for Dummies :-)
Edit: I also don't know how many obstacles there are. There could 3 or 5 or 10. Number is unknown
Everyone is ignorant of many things and everyone is new at everything sometimes.
Here is a demo showing how you should be structured. Note that Obstacles does not hold the TrailsID and that I have added a join table where each combination of trail/obstacle is logged.
The number of obstacles does not matter. The List() function takes all of that Trail's Obstacles and puts them in a list as:
... and the Substitute() changes the carriage return to a space for the file result of: steep help me nice
Thanks LaRetta!! I must have tried every combo except the one you've suggessted. And it works. Its a classic example, on my part, of making something more complicated than it is. Again thanks and I'm going to use your example.