"This script works perfectly..."
Then perhaps you could clarify: what is your question?
I "think" to start with... The problem is that you are basing your "Formula" on Global Variables... I believe Portal Filters need to be base on the indexable data in the Portal's context. You can use Global Fields in the target related table for this.
The only thing the script is not doing right is that it is pulling in a record it should not be.
The portal output is correct - 3 puches were made on day (x) for that employee. 3 puches are output on day (x) for that employee. The summary of the punched time is incorrect because it is summarizing an additional punch for day (x) that is actually on day (y). Its keeping out all other records on day (y) unless that record happens to be the last entered record for that employee - at which point it includes it in the summary.
This last entered record also is summarized and displayed on days where no puches/records have been made yet.
I believe Portal Filters need to be base on the indexable data in the Portal's context.
No, that is not correct.
It's very difficult to understand your description. Your formula declares a variable which is not being used, and uses variables that we know nothing about.
In general, if your filtered portal is showing the correct records, you can view their summary by placing a summary field from the portal's table in another portal. Make this portal one row tall and filter it exactly the same way as the other portal.
Thanks Michael... I may have made some assumptions in this area that were innacurate. I will have to dig into this at some point.
$$dayhrs04=Punches::sumDayHrs - This variable is holding the results from the "summary field of hours clocked". This global variable gets displayed on the calendar for that particular day.
Punches::date = $$day04 - This variable holds the date(mm;dd;yyyy) of the day on the calendar for which the script is checking against to filter the portal results of time/hours clocked for that day.
Punches::employee_idf=$$employee_id contains the employeeID of the employee who has logged into the system.
Let me know if that helps clarify more.
I tried creating another portal as you suggested on the sumDayHrs field with the filter of "Punches::date = $$day04 and Punches::employee_idf=$$employee_id". The portal returned zero results
Im using FM 12v3
I am afraid I am only getting more confused. Earlier you said you want "the summary to only include records that meet the criteria of of the EMPLOYEE ID and the DATE of the entry".
If this is a self-join, you can define the relationship as:
Punches::EmployeeID = Punches 2 ::EmployeeID
Punches::Date = Punches 2 ::Date
This way you don't need to define any variables, nor to filter the portal. To view summaries, place the summary field from the Punches 2 occurrence on your layout (which should be set to show records from Punches).
Took long enough but finally figured out how to write a simple If statement to filter out that pesky last record getting into my filtered summary.
I wrote the following script to filter out that last record that did not belong on days it shouldnt but still display on the day it should.
If($$day04=Last(PunchesJoin::date);Let($$dayhrs04=Punches::sumDayHrs;Punches::date = $$day04 and Punches::employee_idf=$$employee_id);Let($$dayhrs04=Punches::sumDayHrs-Last (Punches::timeClocked);Punches::date = $$day04 and Punches::employee_idf=$$employee_id))
Whether or not thats the best solution, it worked.. WHEW!
Thanks for your input everyone, its nice to have a place to bounce these things around.