You may need that added table occurrence or you may not. It depends on what you might do with that total count of activities. Here's a "display only" way to get what you describe, but keep in mind that it's not the only way and other methods might be a better option:
Put a portal to activities on a volunteer layout. In portal setup specify a portal filter that filters the activities to limit those displayed to just those for a specified year. You can set up this expression to refer to a number field where you specify the year you want to see or the calculation can refer to the current date's year.
Now duplicate this portal and reduce this second portal to a single portal row. Put a summary field defined in activities that "counts" a field that is never empty in this single portal row and it will display the activity count.
If you want to select the year manually, add that field to your above relationship to get this:
Volunteers::SelectedYear X Activities::anyField And
Volunteers::pkVolunteerID = Activities::fkVolunteerID
Including the year field with the X operator will force the portal to update and show the new set of related records each time the value in SelectedYear changes. If you specify SelectedYear to have global storage, you can select the year once and then move from Volunteer to volunteer and see Activities filtered for the same year on each volunteer record.
Is there a built in function that can get the current year? I would really rather not have the user type in a year? I could also I guess enter default year in the field, but that would mean I need to change that every year.
Year ( Get ( CurrentDate ) ) will return the four digit year as a number, so in your portal filter you could use the syntax:
Activities::date > Date (1; 1; ( Year ( Get ( CurrentDate ))))
That year formula works great, thanks. Still having a little issue wrapping my head around these table occurences. I added a field to VolunteerActivities called Position. To track what a Volunteer acts as a Team Lead, group member, runner etc. I also added table occurences duplicates of the three above, but when I add them to the layout, I get an error "Unrelated Table" when I use the 3 original table appearences in the LO, and try to edit position in VolunteerActivities, all records in that table are changed instead of the one I am editing.
I would like to do some calcs, on number of miles a volunteer travels during any given year, number of times they served in different positions etc, thats why I think I need the table occurences, I'm guessing I just don't have the relationships correct. I am thinking I need a direct relationship between Volunteers and Activities. But worried this may create a loop, or circular reference
The relationships shown are correct. It's the additional occurrences that you are trying to set up that are producing the "unrelated table" errors. You shouldn't get that error if you set up layouts that are based on one of these three table occurrences and add portals or fields from one of the other two. You can add as many fields as you need to Volunteer Activities in order to collect data about one volunteer's involvement in a specific activity. Summary reports can then be based on Volunteer Activities to produce the totals that your are describing.
Table occurrence "basics":
- Table Occurrences are what we call the "boxes" found in Manage | Database Relationships.
- Each time you define a new table, FileMaker automatically creates a new table occurrence and a new layout with exactly the same name. While these items have the same name, they are very different objects with different "jobs" to play in your database.
- A Table occurrence is a way to refer to a specific table (called a data source table) by a name you can specify. You can define as many table occurrences in Manage | database | relationships to refer to the same data source table as you need--each with a unique name.
- Unless you are on the Tables or Fields tabs in Manage | Database, nearly any part of FileMaker that refers to "table" is actually referring to a table occurrence.
- When you select a table occurrence for a layout (Show Records From) or a portal (Show Related Records From), you are establishing a "context" for that layout or portal. What data in other tables is or is not accessible will be determined by the relationships that you define linking the specified table occurrence to other table occurrences. If you add a field to a portal row or a layout from a table occurrence that is not linked to the portal or layout in a direct or indirect relationship, you'll get the "unrelated table" error as you haven't defined any way for FileMaker to access data in specific records of the data source table specified for that table occurrence.
For a tutorial on table occurrences: Tutorial: What are Table Occurrences?
For a tutorial on summary reports such as you might set up for a Volunteer Activities layout: Creating Filemaker Pro summary reports--Tutorial
Thank you for the reference material, I'll look through them as soon as I can. Seems like a table occurrence, or group of them is what I understand to be a query. If thats the case why does the Position in VolunteerActivities update every filtered record when I edit one position record?
Maybe I;m doing the LO incorrect and really need 2 portals, not one. One to VolunteerActivities, and one for Activities making sure the VolunteerActivities fk =pkactivityID. Could this be equivalent of a sub-query.
All I can say is it's a good thing I have a year to convert this to a DB!
Seems like a table occurrence, or group of them is what I understand to be a query
Yes, the relationship linking one table occurrence to another does indeed function as a simple type of query. If you are familiar with SQL, it's like setting up a query with Select * and some join statements, but no WHERE or ORDERBY clauses. The perform find and sort records steps/menu options serve that function.
The resulting "found set" is much like a record set in VB or MS Access, but is much more easily manipulated by the general user than is typically possible in those other systems with which you might be familiar.
why does the Position in VolunteerActivities update every filtered record when I edit one position record?
That would suggest an error in how you have designed your layout or in how you have defined this field. You'd have to describe what you have done with this field in more detail before I can suggest why you are getting that apparent result.
In most basic level layout designs, you shouldn't need a portal to both occurrences as you can include fields from Activities inside the portal row of your portal to Volunteer Activities. But there can be uses for a portal to Activities. It depends on how you plan to use this layout. A portal to Activities, FYI, will list each activity that has been linked to the current volunteer at least once, but no activity will be listed twice as can be the case in the portal to volunteer activities where you might assign the same volunteer to a given activity on more than one occasion.
Thanks very much, finally got that portal working and adding records also works. Time to try a little Sub-summary reporting. I will give this a go, but looking for a text function that counts if a word is present. I would like to count how many times a volunteer is counted as a Leader, Team Member etc. In other words I want to count the number of times they served in what Positions in the VolunteerActivities table.
Can't seem to find a text function that will countIF Value = "Leader" " or any other value, and return a number of times in that found set that value occurs.
If you sort your records by that position field to group the records for a given volunteer by Position, a sub summary layout part can use a "count of" summary field to show the count of records for each specified value in the position field. This is covered in the summary report tutorial.
But it's not the only way to count how many times a volunteer has had a particular position. ExecuteSQL can also be used to produce such totals and a table with one record for each position can also be linked in a relationship to volunteer Activities to provide a count for each volunteer for each position in which they have served.
I created a sub-summary section that is a sub-summary when sorted by "I tried both VolunteerID and Position" and it seems to count, not group and summarize the different values. I placed a summary field in the VolunteerActivities tbl that is a count of VolunteerActivities. Then I sorted the records as the when sorted by seems to be a sort is needed to trigger the summary.
If the found set is already sorted by the needed field, is a manual sort still needed?
Not sure if more info is needed to speculate why the count total rather then the different values in the field.
by seems to be a sort is needed to trigger the summary.
To get sub totals in a sub summary layout part, a sort that groups the records into groups that sorts on the same "when sorted by" field is required. If the records aren't sorted in a sort order that include that field, the sub summary part will not be visible nor the summary field placed in the sub summary part that shows the sub total.
It does not require a manual sort so long as the found set's sort order includes the "sorted by" field, you'll get sub summary layout parts and sub totals.
You set up a sub summary layout part "when sorted by position" and place your summary field inside this part.
If you show all records and sort by Position, you get the total number of times someone volunteered to serve in that position. If you sort by volunteer and also by position, you can get subtotals that show how many times each individual volunteered at each position. If you perform a find for just one volunteer ID and then sort by position, you get the same results but for just one volunteer.
So it's all controlled by the sort order and the records that make up your found set.
The key in your answer I think is grouping. I don't remember seeing a grouping option. Will go back and look for that.
Sorting is what produces the grouping. When you sort on the Position field, the sort groups the records by that value and this is necessary for this report method to be able to produce a sub total for each group.
But thats not what I am trying to accomplish. I'm trying to find out haw many times a Volunteer has served in each position. So I'm thinking I need to sort by VolunteerID and group by Position.
This prevents Leaders from assigning volunteers to the crappy positions all the time. If they can quickly look and see this Volunteer is always assigned as a Leader, they may want to assign a different position on this event.