Capturing Date on a File Once a Year

Help Center Get Answers
1

I’m working with three tables in my Knack app: ‘Participant,’ ‘Center,’ and ‘Center Attendance.’ Each participant can attend multiple centers, and I want to track their attendance dates. I need help with a formula to set a date field in the ‘Center Attendance’ table. Specifically, I want the formula to do the following:

  1. If a participant attends a center after July 1st, I want to record their attendance date.
  2. However, I only want to record the attendance date the first time they attend that specific center.

Can someone guide me on how to create a formula for this scenario? I’m using the ‘Participant’ table, the ‘Center’ table (with center names like Burlington, Norfolk, etc.), and the ‘Center Attendance’ table (with an ‘Attendance Date’ field). I appreciate any help or examples you can provide!

2

Hm, that depends if how you set it up precisely (for instance if “Center attendance” is a child of “Center”, and how many centers you have), but I would go with:

For each participant, add a field for each Center:
COUNT-MIN (date) of [Center Attendance->Date fields], where the Center is the one you want.

I don’t know easy ways to take MINs of dates, but I usually converts dates into number of days and take the min of that.

Hope it is somewhat clear