A little vague as we don't know how you've set up your data nor what version of Filemaker you are using.
You can certainly set up a relationship that matches to a range of dates such as the first and last day of your fiscal year--which is just one of several possibilities:
ReportTable::FiscalYearDay1 < DataTable::Date AND
ReportTable::FiscalYearDayLast > Datatable::Date
FiscalYearDay1 and FiscalYearDayLast can be date fields that are set by either script or calculation to be the date of the beginning and end dates of a Fiscal Year.
Sorry for being vague, I am using Filemaker Pro 10.0v3. I have a value list with all of the possible values to fill in ("FY01", "FY02", etc.) and have worked out how to compare those to actual dates. What I would possibly be interested in would be either a drop-down menu that the user could select from that would change that field in all of the records. How would I go about setting something like that up?
Why would you need to "change that field in all of the records"? Except for one time data repair type batch operations, this isn't something you'd normally want to do and I don't see why it's necessary.
Can you describe your tables and the way you have them dated or otherwise marked so that you can determine which records belong to a given fiscal year?
Do you want to display a set of records for this fiscal year, compute an aggregate values such as a total, average, standard deviation, count or other such value?
It's not something that I really want to do, but I'm not sure how else to accomplish this. Basically each record has a "membership level" each of which are associated with a particular donation and we want a to be able to see how many of each we have per fiscal year. So we record the date on which they donated and record that. I understand that this could be taken care of just by sorting, however, we have a couple other fields (six to be exact) which are in a second table that is accessible via a portal, which I don't see any way of sorting, as they have their own date fields, etc. The main information we want out of this is the overall donations we received, and how many people we had donte and a breakdown of how many people donated in which incriments. Oh and the outcome should sortable by fiscal year.
You'll need to describe this part in more detail:
"however, we have a couple other fields (six to be exact) which are in a second table that is accessible via a portal, which I don't see any way of sorting, as they have their own date fields, etc"
You don't have two tables that both record dondations do you? How are these two tables related? What type of data do you record in each?
If you have a single table of donations where each record has a date recorded in a date field, a fairly straight forward summary report can be constructed to provide a break down based on membership level. This report can often be set up to include related data from another table, but I'd need to understand the relationship before I can know if this is possible here.
A calculation field can use the donation date to compute the fiscal year.
Here's one possibility that assumes June 1 as the first day of your fiscal year. You can just replace that date with the date of the first day of your fiscal year and then you can sort on it and/or perform finds on it to produce your report.
Year ( DonationDate ) - ( Month ( Donationdate ) < 6 )
This produces 2011 for all donation dates from 6/1/2011 to 5/31/2012
Ok, to get right down to the nitty-gritty, there are seven tables in question. The main table has all of the records for each of the people/parties in our database which includes name, date of registration, membership level and much more. This table is related (via a filemaker generated id number from the main table) to six other tables all with the same architecture that includes a modifiable date field, a field that contains how much was donated on that date, another field that contains the calculated fiscal year, and several others that are less important for this purpose. All of these six tables have records that coincide with a specific donation that could happen at any time (table one has records coinciding with each record in the main table for type one donations, table two has records coinciding with each record in the main table for type two donations, etc.)
There is currently a report tool that sums up all the different membership levels and how much money they each have brought in since the beginning of the current fiscal year. What I would like to be able to do is to add the functionality to change the fiscal year they are compared to (the membership level field has a date associated with it that is compared to the current date and determines if the membership is current, and labels it current or lapsed)(each of the related tables has a creation date associated with each record in that table which is associated to each record in the main table). So I need to find some way to compare these two date fields to something the user could choose from/define.
Hmmm, don't think I'd use separate tables for each type of donation. There are other ways to group your donation records by type and leaving them all in the same table makes for much easier reporting.
You want to modify the "reporting tool" to make the fiscal year selectable by the user? And what kind of reporting tool do you have?
The reporting "tool" is just a layout in Filemaker that is selectable for the main table. And yes, I would like to be able to modify the report output.
First to figure out how that layout is set up, then to figure out how to modify it to work.
In layout Setup... there's a drop down labeled "show records from". You'll need to look at that and note the name, then go to Manage | database | relationships and note how your donation tables are linked to that table occurrence. We may also need to examine how any calculation fields that present the report data are defined, but let's start with the relationships first.
All of the report tool fields are in the main table (which probably wasn't the best choice, but I created it before I knew how to relate tables). Each membership level (which is storred in the main table as well) has a count (how many records have that standing in a given year) and a payment/donation total. To count these records, I created a field that calculates whether or not a given record is within the current fiscal year and then marks it as either "1" or "0". Then I have summery field which sums up the amount of records in that level and is displayed. That field is then multiplied by the specific price that must be paid to join that membership level which is also displayed.
The six other tables are denoted by the word "Fund" and a non-specific dollar amount. These are currently counted by a summery field that counts the number of records in the given table, and the payment/donation total is a sum of all of the donations in that table in the current fiscal year (starting july 1st). I already have a field set up where the user could choose the fiscal year from a list, however it only sets it for that one record, and if there is information on another record that should appear on the report (which is more than likely), it will not be effected.
That doesn't answer the questions I asked in the previous post. Without that info, I cannot suggest a way that works for you as I don't know how your current tools do what they do and thus cannot suggest a way to modify them. Somehow, your "report tool" links to records by fiscal year. If we can identify how it does that, we can introduce a data field where you can select a fiscal year from a value list of fiscal year values to make this layout something where you can pull up data for any fiscal year you choose.
But I need that information before I can tell you how to do this...
The way I have it set up, the report layout is a part of the main table, and that main table is related to the other tables only through their id numbers. The reason my report tool returns the current fiscal year's information is because I have a field in that main table that checks to see if the renewal/membership date is after that most recent June 31st. And the other tables' dates are also compared to the most recent June 31st. What I can't figure out is how to compare all of these to a date that is changible from any record.
"I have a field that checks..."
How does it "check" like that? (I can see how to implement a calculation that returns a result of 0 for all dates earlier than the most recent June 31st if defined in each of the other tables, but am having trouble seeing how a calculation field defined in your main table does this.) If this is a calculation field, please post the calculation expression here so that I can see how it works.
If ( Membership Level="y/individual" ; If (Membership_FY=Date_FY; "1" ; ""); "")
Where Membership_FY = If ( Month(Membership_Last) ≥ 7 ; "FY" & If (Right(Year(Membership_Last);2)<10 ; "0" & Right(Year(Membership_Last);2) ;Right(Year(Membership_Last);2) )& ", "; "FY" & If (Right(Year(Membership_Last);2) - 1 <10; "0" & Right(Year(Membership_Last);2) - 1 ; Right(Year(Membership_Last);2) - 1) & ", ")
and where Date_FY = If ( Month(Date) ≥ 7 ; "FY" & If (Right(Year(Date);2)<10 ; "0" & Right(Year(Date);2) ;Right(Year(Date);2) )& ", ¶"; "FY" & If (Right(Year(Date);2) - 1 <10; "0" & Right(Year(Date);2) - 1 ; Right(Year(Date);2) - 1) & ", ¶")
The ", ¶" is a remnant of a checkbox set that is auto-updated by fiscal year field in each of the relating tables.
Date_FY is an autofill field, but has a list of acceptable options associated.
Date is simply the current date.