• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 709
  • Last Modified:

Filemaker DB's calculated field containing relationship data ( using list function.)

    I have a Master & a Relation table. Projects And Teams assigned.

Projects And Teams.
One project will have more than 1 team involved. (One-to-Many relation)

Now in project table i have a calculated field "TeamList" which contain a comma separated list of related data from team table.

For Ex)
ProjectA will contain related data Team1,Team2,Team3

For this im using this calculation  which uses List function.
Substitute ( List ( team::team_name ) ; ¶ ; "," )

Now upto here it is working fine.

Now I need another add another filed as well to this calculated field
(related data from 2 fields appended together)

i.e,. Instead of TeamName1,TeamName2,TeamName3
I need
Team1[NoOfPeople in Team1],Team2[NoOfPeople in Team2],Team3[NoOfPeople in Team3]

Field1[field2], Field1[field2], Field1[field2] from relationship table.

I tried
Substitute ( List ( team::team_name & "[" & team::team_people_count & "]") ; ¶ ; "," )

But we can't have a expression inside List().

So how to do this ?
Team Table
TeamName    NoOfPeople   Project   ..........
TeamA          2         Project1
TeamB          3         Project1
TeamC          1           .
TeamD          5           . 
TeamE          8           .
TeamZ          12          .
TeamY          7
TeamX          9

Project Table
ProjName   TeamList (Calculation as below)
Project1    TeamA,TeamB,TeamC
Project2    TeamX,TeamY,TeamZ

Substitute ( List ( team::team_name ) ; ¶ ; "," )

Instead of TeamA,TeamB,TeamC
I need TeamA[2],TeamB[3],TeamC[1]

Open in new window

1 Solution
Will LovingPresidentCommented:
The simplest way to do this is to create a field in  the Team table that concatenates the fields together - TeamDescription =  team_name & "[" & team_people_count & "]") -  and then just reference that calculation field in your list

List ( Team::TeamDescription )
BToTheAToTheBABAAuthor Commented:
This is what you call as, "Thinking In Filemaker"
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now