Could you be more clear as to the calc you are trying to do?
I don't understand "subtract the number field between two dates...
My imagination guesses you want the number subtracted from another value only if a third date field is between your global dates.
Sorry, not too clear. Every day there will be a new record/row in the portal. Each record/row has a Date and a Number field. As an example, I want to subtract the value in the Number field on the 30th May by the value in the Number field on the 1st May. Clearer?
But how will that work with a date range? When you specify a range of dates, you may get multiple values in the portal. How will FileMaker select from that list of values a single value to subtract?
Ok sorry, again not clear enough. When I say date range I mean 2 dates. If the value in the Number field on the 30th May is 100 and the value in the Number field on the 1st May is 25, I want the calculation to do 100-25=75
Will the two pertinent dates be the dates in your global fields?
If there are a number of dates, how will the dates of interest be selected?
If these are the dates in your global fields, you would make two table occurences, each liinked to a different global field (Global1 = TO1), (Global 2 = TO2) then use the calc in the form of
TO2::number - TO1::number
How exactly do I link that global field to the relevant table?
Create a "Table Occurrence" (or T.O.) for each Global Link (if this is a new term, please say so). ....Relationshp Graph...double + sign at the bottom left.
Link each T.O. separately, one to each of your global fields.
MainTable::GlobalDate1 = Gloabl1TO::Date
MainTable::GlobalDate2 = Global2TO::Date
These table occurrences can be referenced separately in the caluclation definition using the Table Dropdown selector in the top left corner.
Thanks Ninja, works well. I have another question now. How do I sum another field on that portal, between those two dates that I have already selected. The difference here, is that there are more records between those two dates that need to be included in the sum.
You may want to start a new thread...folks tend to look past threads marked as solved...
You can use yet another TO whose relationship is double defined.
"Records are related when"... Child::Date > Global1 AND Child::Date < Global2
Then set a calculated field in your parent table which is Sum(ChlidTable::Number) *Result is Number*
Thanks for that Ninja, again works well. This brings me to another question, again.
The calculated field "Sum(ChildTable::Number)" you mentioned in your last post is located in the Parent table. Can you please tell me how I can create another calculation, that is still based on the dates between Global 1 and Global 2 but located in the Child table?
Referencing my May22 post regarding a filtered Child TO using max date and Min date (Globals 1&2) as filter criteria
Your calc field would simply be
Sum ( FilteredChildTO::Number).
If you sum referencing the old TO, you'll get the results through THAT filter...the Sum() function will give different results based on what filter it is looking through to the referenced Child TO. Reference the TO that is using the filter (relationship definition) that you want the Sum() function to use.