Solved

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

Posted on 2010-11-29
2
680 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
FM - Mouse Scroll 3 518
Perform Find and sort/omit results based on dates 12 265
Printing Portal records 7 162
Filtering portal results 3 69
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…
Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

747 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

14 Experts available now in Live!

Get 1:1 Help Now