Jump to content
Sign in to follow this  

Populating a calculation field from cross-table data

Recommended Posts


Ok! Right foot forward and let's see what we can come up with.


I have a database tracking players/participants/supporters in an organization. The first table of the database is all information about these members. The second table in the database allows for some administrative functions, which includes a roster generation. That, at least, is the idea, yet I have run into a snag.



On the first table, a person can be active, on vacation, or retired. This is indicated in a text field (pop-up menu, value list) called “Status”. I have a field called NonRetiredNames, which is a calculation field. It has the following equation: If (Status="Retired";"";Name).


I then define a value list called “Members”. It uses values from the field “NonRetiredNames”. This very conviently skips blank names and keeps the entire list in alphabetical order.


I also have a field called Member_BBCode, a calculation field, which assembles other information from the first table (name, age, gender, position, and an HTML link to profile picture) into a single line of text for use on the message board in a BBCode format.


The second table in the file has fields called:

All_Members (text)

Member_List (calculation: = All_Members)

Assembled (calculation)

Starting_Date (Date)

Ending_Date (Date)


In the only layout in the second table, the field “All Members” is set to a checkbox set using the value list “Members”. Other fields on the layout are Member_List , Assembled (calculation), Starting_Date (Date), and Ending_Date (Date).



What this does is when you click a checkbox next to a name in the admittedly largefield “All_Members” it places that member’s name *in the order it is checked* into the field Member_List. This is actually OMG! GREAT since it means that members are not always listed in the same alphabetical order (a point of petty contention for some).


Now the problem part…


I have the in the field “Assembled” a brief header with some static text, the values from the Starting_Date, and the Ending_Date fields.


What I want to have after that easily formatted information is the corresponding Member_BBCode field values (from the first table) in the same order as name values in Member_List, and two cartridge returns in between each value (which I can get using the “P” sign).


It is not absolutely necessary for this field to be dynamically generated upon each name clicked in the large checkbox field. It would be entirely acceptable to have a sort of “Publish it!” button which is tied to a script that chews through the “Members_List” field as it exists at the time of button click and creates the final contents of the Assembled field.


I would like to maintain both the click-box field and pre-cursor listing (Member_List) if possible. However, if necessary, the Member_List could be dropped, but the check-list rather needs to stay.


This is in Filemaker Pro 7. I do not have the financial option right now of an upgrade to a newer version. However, if this solution to this problem would not be available in FMP 11/12, I would like to know.


Thank you for your consideration.

Share this post

Link to post
Share on other sites

Ideas, anyone? This is one of the last things that is holding me back on making a presentation of this database to PowersThatBe in hopes of obtaining some type of compensation.


Thank you

Share this post

Link to post
Share on other sites
This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Create New...

Important Information

Terms of Use