Solved

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

Posted on 2010-11-29
2
684 Views
Last Modified: 2012-05-10
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,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

----------------------





Calculation

--------------------

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



Instead of TeamA,TeamB,TeamC

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

Open in new window

0
Comment
Question by:BToTheAToTheBABA
2 Comments
 
LVL 24

Accepted Solution

by:
Will Loving earned 500 total points
ID: 34236139
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 )
0
 

Author Closing Comment

by:BToTheAToTheBABA
ID: 34236307
This is what you call as, "Thinking In Filemaker"
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
A short film showing how OnPage and Connectwise integration works.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now