The instructions were to create 2 calculation fields (a start and end date) in the "months" table based on the global date field in the "interface" table.
What do you mean by "based on"? That doesn't really track here and sounds like something that will produce unstored calculation fields in your months field and that won't produce a relationship that works from the context of your interface table.
A detailed description of your relationships would also be helpful as it saves possible inaccurate guesses on the part of anyone responding to your question.
Sorry, "based on" meaning the calculation were dates using that global field, like this:
Right(Year(Dashboard::g_Dashboard Global Date);2)
And that won't work for what you want to do for the reasons that I noted earlier. I suggest describing what you are trying to accomplish with your global date field and then we can take a crack a an approach that will work for what you want.
I see, thank you. When I saw this done, I didn't think it would work, but it did and I really think it would be a great tool for the management team of the juvenile detention/treatment center where I work. So here is what I was thinking:
A screen with a date field that would show related records in portals and it would update each time a new date was entered. The portals could be incidents, restraints, etc. To start with, here is how I structured it:
3 tables: Interface, Months, Incident
A global date in "interface table", a start and end date in "months table" (which is the calculation I sent in my initial post) and the existing "incidents" table.
Relationship (please forgive my ignorance on the proper way to type this) Interface ----->>Months (Interface::globalOne-Months::globalOne) and then the other table occurrences would be Months----->>Incidents (start date < incident date AND end date> incident date)
If I have a field in the "Months Table" that is the Count(Incidents::Incident ID).
The layout could look like this:
Layout= Interface Table=Months
Date (global_interface) 1/1/14 (enter date here)
and it would show:
Mo/Yr # Incidents Current Yr # Incidents Prev Yr
1/14 10 11
2/14 9 7
3/14 5 8
So you don't want to see the actual incidents, you want to see a count for each month.
A different approach can make this happen quite easily and I suspect that your demonstration did this from a different context than you are expecting.
Here's how I would do it:
Months::MonthKey = Incidents::cMonthKey
cMonthKey is a calculation field, but a STORED one, that does this:
IncidentDate - Day ( IncidentDate ) + 1
Select Date as the result type. IncidentDate is the field where you record the date for a given record in Incidents. This calculation computes the date for the first day of the month for all dates from the same month so it gives you a common value for all incidents in one month. Months::MonthKey would be a date field with a "first day of the month" date. You can set several different auto-enter calculations to ensure that this is the date found in any given MonthKey field such as: Self - Day ( Self ) + 1 or you might use Get ( CurrentDate ) - Day ( get ( CurrentDate ) +1. Either way, you can create a series of records in Months with one record for each month.
A calculation field such as Count ( Incidents::IncidentDate ) defined in Months would give you the total count of incidents for that month and year. To get a count from incidents for the previous year, you can set up a calculation field such as:
Date ( Month ( MonthKey ) ; 1 ; Year ( MonthKey ) - 1 ) and use it to match to another occurrence of incidents so that a use of the count function can count the incidents for the preceding year.
A portal on a layout based on Inteface can then list all Months records in a portal and show the incident counts in two columns inside that portal.
Interface::anyField X Months::anyField
Months::MonthKey = Incidents::cMonthKey
Months::cMonthKeyLastYear = Incidents|LastYear::cMonthKey
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
Thank you so much, it worked perfectly!
Thank you for posting this question and thank you, Phil for your detailed answer.
One quick question. What happens if you enter a new Incident in a date that hasn't already been entered into MONTHS? For example, when you enter an incident for 7/15/2015, is a new record automatically created in MONTHS::MonthKey (7/1/2015) or do you have to enter this manually in MONTHS?
Should the relationship between MONTHS and INCIDENTS be Many to Many or should the fields in MONTHS be defined in a way to force FMPA to force a One to Many?
This is a screenshot of a test dbf based on your thread.The relationship graph on the right shows a Many to Many relationship between MONTHS::MonthKey = INCIDENTS::c_MonthKey and a similar Many to Many between MONTHS::c_PrevYr= INCIDENTS::c_MonthKey.
FileMaker will show "crowsfeet" on any field that is neither an auto-entered serial number field nor set up with the "unique values" validation option. It would be a good idea to specify that for MonthKey. You can't specify that validation for c_prevYr unless you changed it to a data field with an auto-enter calc, but there is no real need to do so.
You'll need to set something, such as a script that adds new month records for you in the Months table. This could be a server scheduled script that runs once a day, a script that runs when you first open the file or a script that adds the month if it is not present each time that you log a new incident.
If you select "allow creation of records via this relationship" for Months in the Months to Incidents relationship, a script that runs from a layout based on
MonthsIncidents with this single script step:
Set Field [ Months::MonthKey ; Incidents::c_MonthKey ]
Will create the new record if one does not exist and make no change to your data if it does exist.
Edit note: Corrected typo due to comments found below....
Didn't get this to work yet.
I added Validation "Unique Value" to MONTHS::MonthKey. Only during Data Entry and Allow User to override.
Specified "Allow creation of records in ...[ MONTHS ] via this relationship.
Created Script "Create Month If New" with the following steps.
- Freeze Window
- Got to Layout ["MONTHS"(MONTHS)]
- Set Field [MONTHS::MonthKey;INCIDENTS::c_MonthKey]
- Go to Layout [original layout]
Created Script Trigger "On Record Commit" that runs the script above when a record is committed in INCIDENTS.
I realize the Script has more steps than you described but if I wanted to create a new record in MONTHS when a new record in INCIDENTS is created.
Created new record in INCIDENTS with new date but this didn't create a new record in MONTHS.
What did I do wrong?
My typo, the morning caffeine has yet to be imbibed here. You would not do this from a layout based on Months, it would be from a layout based on incidents.
BINGO! That got it. Thank you.