Too many calculations!

[Aleesha]

Hello everyone,

This is my first time asking for help on a board, so please be patient! I am self taught in Filemaker so far, creating field data collection solutions for use in Filemaker Go on ios. 

I am currently creating a new database and data collection form. Both are still in the early stages of development, and I am open to going about this in an entirely different way. Let's get started. 

I have two tables: One is the "People Management (PM)" and one is the Activity Management (AM)".

In PM, each row is an individual who is tied to the organization, either as a volunteer, a member, donor, etc. These individuals can attend events, and we would like to know an individual's history of events attended. 

In AM, each row is an activity. Details of the activity, such as the location, start and end times, staff in attendance, total number of participants, etc, are stored for reporting to the funders. The names of the individuals in attendance is not necessary here.

In AM data collection form, each activity will autogenerate an ActivityID# (01, 02, 03...). I would like to show up at an event and fill out this form. I imagine selecting people in attendance from a drop down list, generated by the PM table. I am capable of creating all of this just fine, up to this next point: the ActivityID from this form is, in turn, automatically placed in that participant's list of attended events. 

My question is: How do I achieve this last step? I imagine needing multiple columns for each individual in PM

First Name | Last Name | ActivityID_1 | ActivityID_2 | ActivityID_3 ....

How would I tell Filemaker to find the next empty column in the ActivityID series and fill in the ActivityID from that form I am working in? Is this possible via relationships and scripts? Is there a better way to go about this?

I really appreciate your expertise. Please let me know if you need more information. 

Thank you 

 

[comment]

1 hour ago, Aleesha said:

Is there a better way to go about this?

Yes, there is.

You have a many-to-many relationship between People and Activities (one person can participate in many activities, one activity can have many participants). This requires a third table to join the two - a table where each record will represent the participation of one specific person in one specific event.

You can see a basic demo of a join table here:
https://fmforums.com/topic/50942-portal-grouping-problem/?tab=comments#comment-239210

 

Note that you can add records to the join table from either parent table, and have them show up in the portal from the other parent. You can also have any number of join records and there is no need to predefine numbered fields in any table for this purpose.

 

 

Edited July 10, 2017 by comment

[Aleesha]

Ah, you have sent me down the right path, thank you. 

[Aleesha]

I have created an affiliation table as suggested above. I have attached a draft of the fmp here. 

In the Activities layout I would like to select more than one attendee at an event, creating multiple entries in the Affiliations table. 

Does anyone have a suggestion for how to get this to work?

Thank you!

 

People and Activities.fmp12

[LaRetta]

Hi Aleesha, welcome to FMForums!  I've re-attached your file.  Since Affiliations can be multiple (the many side) to one Activity, they need to be displayed in a portal when on Activity layouts.  Also note, you already had 'Allow Creation' active on the Affiliations side of the relationship which was perfect so you should be good to go.

Let me know if I missed anything. :-)

 

People and ActivitiesMOD.fmp12

Edited July 11, 2017 by LaRetta

[Aleesha]

This is exactly what I have been missing, thank you! 

[LaRetta]

Aleesha, you are a quick study!  You'll be up to speed in no time flat!  It is really nice to see someone begin their files with a relationally-sound base structure.  Way to go!

Again, welcome. 

[LaRetta]

Aleesha, another thing which fits for you right now is making it easy on yourself in future to know specifically which table occurrences have Allow Creation turned on.  It looks obvious here but as your solution grows, it won't always be obvious and we can forget as well.  

What many developers do is use the text tool to create reminders such as the A or a - if 'deletion' or S for sort.

 

[Aleesha]

Great tip, LaRetta, thank you.