Jump to content
Aleesha

Too many calculations!

Recommended Posts

Aleesha    0

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 :)

 

Share this post


Link to post
Share on other sites
comment    1,372
Posted (edited)
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 by comment
  • Thanks 1

Share this post


Link to post
Share on other sites
Aleesha    0

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

Share this post


Link to post
Share on other sites
Aleesha    0

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

Share this post


Link to post
Share on other sites
LaRetta    478
Posted (edited)

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 by LaRetta
  • Like 1

Share this post


Link to post
Share on other sites
Aleesha    0

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

Share this post


Link to post
Share on other sites
LaRetta    478

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. :)

Share this post


Link to post
Share on other sites
LaRetta    478

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.

 

Identifiers.png

  • Like 3

Share this post


Link to post
Share on other sites
Aleesha    0

Great tip, LaRetta, thank you. 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


×

Important Information

By using this site, you agree to our Terms of Use.