BToTheAToTheBABA
asked on
Filemaker DB's calculated field containing relationship data ( using list function.)
Hi
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,TeamNa me3
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 ?
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,TeamNa
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
----------------------
Calculation
--------------------
Substitute ( List ( team::team_name ) ; ¶ ; "," )
Instead of TeamA,TeamB,TeamC
I need TeamA[2],TeamB[3],TeamC[1]
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER