I made a small update to the sample. Also I forgot to mention that you should use a serialize idkey to link records instead of names and address. Names and address can change, which will create problem down the road.
Thanks. I checked out your sample. While I understand how you have calculated the summary in the Donations layout, I would really like to see the summary for the current school year in the DonorSample layout where the donations are being entered for each donor. Could you suggest a way in which I can achieve this?
I would also like to keep the "current school year total" as a Calculated field on the Donor table as I would need to provide additional analytic reports on this field. Forr e.g. each Donor could be associated to a School so I can then get the split of "Current Year Donations" by School, and also Donor gets assigned to a level based on how much they have donated in that school year so this would be another calculated field running off a sub-set of records from Donations table.
I updated the sample app with all the features you mention above. You select the current school year and school at the top of the layout. You can select All schools. Use script triggers to switch between two layouts : All or the school. The schools can be edit from the portal when you select the school there is a option to edit. The levels can be changed also. I setup a calculation / merge field for the donor levels. Just change the calculation. The figures are all based on different table occurance.
Thanks.. Ok so what you have done is created a copy of the donations table and setup a relationship to filter only on the current school year, where the current school year is a global value and set via a pop-up fields on the donation entry form. This seems to be a pretty lengthy setup for a simple summary calculation. And if I need to get summaries per school year and total for each school year displayed on the form, I have to create a copy of donations table for each year? Is this the only way to set this up in Filemaker?
I tried to replicate what you have done in my DB. I am using Filemaker Pro 11. But it does not seem to be working. I am using a list of values for the pop-up menu on the CurrentSchoolYear field (CurrentSchoolYear is a global field on the parent table) and expect the Total Amount to be updated to reflect the total for that year. But the total field is blank. Any idea on what I could be doing wrong?
You shouldn't need to copy the actual table, but just copy the reference to it found in Manage | Database | Relationships. Each box here is called a "table occurrence" and is a reference or pointer to a specific table. When you duplicate one of these using the button with two green plus signs, you only duplicate the reference to the table, not the actual table. This is how you use FileMaker to create multiple relationships between the same two tables.
When you consider all the different amounts being tracked it not really that bad of a setup. You will get a good understanding of relationships.
There is always more than one way to do things, with everything that you stated you wanted to track in your later post, no matter the way you go it still going to be lengthy.
I started in the first example using a global field in a global table. This lead to a problem with relationship because I didn't have a relation to the global table so I just moved to the parent table, which there was no reason for the field to be global. (Relationship in parent table)
No you don't have to create a copy of the donations table for each year and school. This is the reason for two popup at the top of the sample app.
Each Total is based on the same summary field. What changes the amounts is the table occurances.
Is your Total Amount field based on the correct table occurance. The Total Amount field should be a summary field. Total of the amount paid.
From Example: The total in the header is based on the school year and the school. (Can be individual or all schools)
The school can be changed to a school name. I used gerenic names. The table occurance is Donations By School_School year.
Under the portal is the amount paid by donor for current year. You have to track this if you want to assign the donors to different levels.
I didn't create a sample report with all the individual schools but it shouldn't be to hard to create. The sample does track individual amount based on the popup selection.
Another reason I went ahead and created a sample app because it hard to explain where someone else can understand what going on. Example always helps me to learn.
S Chamblee - Thanks. I was able to set it up the way you have. I appreciate your taking the time to create the app else it would have taken me quite some time to get this done.
So just to confirm - if I need to show a "YearbyYear donation history" on the Donor form i.e. show the Total per School Year, I create a global for each school year on the Donor table, create a relationship to join using that year and with a separate donation table occurrence for each: "Donations for 2012-2013" "Donations for 2011-2012" and so on - is that right? Thanks.
You don't have to have a donation table occurrence for each school year. Your donation table will have the school year the donations were paid. The donor table has the school year you select from your popup menu, so this field will change to whatever school year you select.
You select the current school year from the top popup. Means the current year that the totals you are viewing on screen.
donorsample::Current School Years = the select school year to view
donations::schoolyear = The school year the donation was paid.
The relationship is donorsample::Current School Year -< donations::schoolyear.
You would have one table occurance -- I named the occurance in my sample app : donations by school year
If you add other options such as by school for Current school year then you would have another table occurance
Occurance Name from sample app : Dontations By School_Current school year.
This 2nd Occurance would have the above relationship plus relationship donorsample::school -< donations::school
donorsample::school is the popup of the school that you want to view.
donation::school is the school the donation was paid too and you select from the drop down list in the sample.
Hello, I was not clear in framing my question earlier.
I have to show the totals for each year side-by-side in separate fields so user can see the history. Having them select the year from the pop-up would only display one total at a time. The school year is not something the user would explicitly select. I have built the School Year global field in my donor table as a calculation that returns a fixed school year i.e. 2012-2013 for current school year. As an example, the form would show all of these fields for each donor:
Current Year (2012-2013) Total ______ 2011-2012 Total ______ 2010-2011 Total ______
Since they would be all displayed simultaneously, the current approach would require me to create a Donations table occurrence per year.
I didn't understand that you wanted to displayed all the figures at one time. You're correct.