Can you post a little more information about the setup.
From what you are saying and is the correct orperator, but are you returning the text "expenses" or a field called expenses?
Wild cards don't work in an if statement so you'll need to change this part: Date = 1/*/*
Depending on your system settings that 1 could represent the month of january or the first day of the month. Assuming that it is the day, you can use: Day ( Date ) = 1 instead.
Rev MK: that is a field called expenses
PhilModJunk: How do I setup my settings so that 1= January, 2=feb, etc.
Month ( Date ) will return the number of the month. Just as Day ( Date ) returns the day number of the date.
That worked perfectly, thanks! I only have one more hurdle to clear and then i'm all set.
I currently have:
Category A_January Amount: If (Category = A and Month (Date) = 1; Expenses ; "")
Category A_January Total: = Total of Category A_January Amount
The Category A_January Total field is correctly only adding the expenses from the month of January, however it is not updating automatically. I've noticed that it only updates the total when i go to the next portal row and insert my date field and then tab to the expense amount field, its THEN, that the total field is updated with the figure from the prior portal row.
What settings do i need to change in order for this to update automatically as the number is entered into the expense amount field in the portal? The 'amount' field above is currently a calculation field. Should i change it to a number field and enter in the calculation that way?
This is a known refresh issue with summary fields from a related table. You can set up script triggers that refresh the window when you exit a field or the portal row.
You may also want to greatly simplify this approach if you are using Filemaker 11, a single summary field can produce monthly totals for each month if you set it up in 12 one row filtered portals instead of defining 12 calculation fields and 12 matching summary fields...
Another option is to define calculation fields in the parent record that use Sum in place of the summary fields defined in the portal's table. This avoids the need for a refresh script, but cannot be set up with portal filtering as it will evaluate at the data level rather than the presentation level.
How do i set up the script trigger? I found the 'refresh window' option for the script but i'm not sure how to set it up from there.
On any field that you can edit on your layout and which then might alter the total computed by the summary field, use the OnObjectSave trigger to perform one these two scripts:
Refresh WIndow [Flush cached join results]
I think the first two steps are sufficient, but if not, try the 'flush' option and see if works where the other doesn't.
Which field should i be putting the script trigger on? The summary field or one of the fields in my portal?
Not the summary field. any field you have in the portal where changing the value in that field might change the value in the summary field.
Ok, I tried that (both scripts) and it didn't work. Any other ideas? Could you further explain how to set up the portal summary you mentioned earlier? Thank you so much.
Not really and they've worked for me in the past. Some detail is missing here. You might try enabling the script debugger if you have advanced or adding a show custom dialog step to the beggining of the script and then working with this layout to confirm that the script is actually be triggered at the times it needs to be triggered.
If we can't figure out the refresh issue with one instance of the summary field, the filtered portals with summary field method will have the same issue 12 times over.
How it works for January and then extending this to 11 more portals just requires a slightly different portal filter expression for each:
1) Define a summary field that computes the total of Expenses instead of the calculation field with the If function.
2) Set up the portal filter expression to apply the same logic as the original If function:
Portaltable::Category = A and Month (PortalTable::Date) = 1
3) Make this a one row portal and make the summary field the only field in the portal row.
4) Once this works for January, make copies of this portal and update the Portal filter expression to specify different months and/or different categories.
So i'm not really sure why this didn't work the first time, but i saved the file and returned to it this evening and i assigned the refresh script to my 'expenses' field in the portal (with the refresh script using commit records then refresh window w/ flush cached join results) and set it to trigger upon object exit and it worked! The summary field updated immediately. Also, i figured out that if i assign the refresh script to the date field, category field, expenses field and credit field, the running balance does not disappear and it updates immediately...both problems solved w/ one script!
Thank you as always for your time and patience.