iDEViate Posted March 4, 2012 Share Posted March 4, 2012 Hey guys, I usually just troll around on here but am having some trouble with a calculation thats needed to complete an attendance tracking database i'm building for the place I work for. First off I've only been using FileMaker for about 4 months now so I'm by no means a developer and I truly hope one of you all can help me with this. Heres the way it works. Each day missed is 1 point. For each 30 days of perfect attendance the employee gets -1 point. After 90 days the employee will get -4 points. And after 180 days every point expires. I really hope this is easier to do than I've been thinking it is. But I've just been spinning my wheels trying to get it to work. Thanks ahead of time for the help. Link to comment Share on other sites More sharing options...

iDEViate Posted March 4, 2012 Author Share Posted March 4, 2012 Well boys I think I got it.. If ( Sum ( Points::Sorted_Point_Value ) > 0 ; Case ( Get ( CurrentDate ) > Max ( Points::Date ) + 150 ; Sum ( Points::Sorted_Point_Value ) - 6 ; Get ( CurrentDate ) > Max ( Points::Date ) + 120 ; Sum ( Points::Sorted_Point_Value ) - 5 ; Get ( CurrentDate ) > Max ( Points::Date ) + 90 ; Sum ( Points::Sorted_Point_Value ) - 4 ; Get ( CurrentDate ) > Max ( Points::Date ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 2 ; Get ( CurrentDate ) > Max ( Points::Date ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) ) Link to comment Share on other sites More sharing options...

AHunter3 Posted March 4, 2012 Share Posted March 4, 2012 If you're using that as a field definition, be sure to set the calculation field to "unstored" or it won't work due to not noticing when Get(CurrentDate) changes from one day to the next. Link to comment Share on other sites More sharing options...

iDEViate Posted March 4, 2012 Author Share Posted March 4, 2012 Yeah its unstored.. Heres what I ended up with but it breaks. If ( Sum ( Points::Sorted_Point_Value ) ≥ 6 ; Case ( Get ( CurrentDate ) > Max ( Points: :ate ) + 150 ; Sum ( Points::Sorted_Point_Value ) - 6 ; Get ( CurrentDate ) > Max ( Points: :ate ) + 120 ; Sum ( Points::Sorted_Point_Value ) - 5 ; Get ( CurrentDate ) > Max ( Points:ate ) + 90 ; Sum ( Points::Sorted_Point_Value ) - 4 ; Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 2 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) ) & If ( Sum ( Points::Sorted_Point_Value ) = 5.75 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 150 ; Sum ( Points::Sorted_Point_Value ) - 5.75 ; Get ( CurrentDate ) > Max ( Points:ate ) + 120 ; Sum ( Points::Sorted_Point_Value ) - 5 ; Get ( CurrentDate ) > Max ( Points:ate ) + 90 ; Sum ( Points::Sorted_Point_Value ) - 4 ; Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 2 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) ) & If ( Sum ( Points::Sorted_Point_Value ) = 5.5 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 150 ; Sum ( Points::Sorted_Point_Value ) - 5.5 ; Get ( CurrentDate ) > Max ( Points:ate ) + 120 ; Sum ( Points::Sorted_Point_Value ) - 5 ; Get ( CurrentDate ) > Max ( Points:ate ) + 90 ; Sum ( Points::Sorted_Point_Value ) - 4 ; Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 2 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) ) & If ( Sum ( Points::Sorted_Point_Value ) = 5.25 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 150 ; Sum ( Points::Sorted_Point_Value ) - 5.25 ; Get ( CurrentDate ) > Max ( Points:ate ) + 120 ; Sum ( Points::Sorted_Point_Value ) - 5 ; Get ( CurrentDate ) > Max ( Points:ate ) + 90 ; Sum ( Points::Sorted_Point_Value ) - 4 ; Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 2 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) ) & If ( Sum ( Points::Sorted_Point_Value ) = 5 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 120 ; Sum ( Points::Sorted_Point_Value ) - 5 ; Get ( CurrentDate ) > Max ( Points:ate ) + 90 ; Sum ( Points::Sorted_Point_Value ) - 4 ; Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 2 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) ) & If ( Sum ( Points::Sorted_Point_Value ) = 4.75 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 120 ; Sum ( Points::Sorted_Point_Value ) - 4.75 ; Get ( CurrentDate ) > Max ( Points:ate ) + 90 ; Sum ( Points::Sorted_Point_Value ) - 4 ; Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 2 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) ) & If ( Sum ( Points::Sorted_Point_Value ) = 4.5 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 120 ; Sum ( Points::Sorted_Point_Value ) - 4.5 ; Get ( CurrentDate ) > Max ( Points:ate ) + 90 ; Sum ( Points::Sorted_Point_Value ) - 4 ; Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 2 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) ) & If ( Sum ( Points::Sorted_Point_Value ) = 4.25 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 120 ; Sum ( Points::Sorted_Point_Value ) - 4.25 ; Get ( CurrentDate ) > Max ( Points:ate ) + 90 ; Sum ( Points::Sorted_Point_Value ) - 4 ; Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 2 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) ) & If ( Sum ( Points::Sorted_Point_Value ) = 4 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 90 ; Sum ( Points::Sorted_Point_Value ) - 4 ; Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 2 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) ) & If ( Sum ( Points::Sorted_Point_Value ) = 3.75 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 90 ; Sum ( Points::Sorted_Point_Value ) - 3.75 ; Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 2 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) ) & If ( Sum ( Points::Sorted_Point_Value ) = 3.5 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 90 ; Sum ( Points::Sorted_Point_Value ) - 3.5 ; Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 2 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) ) & If ( Sum ( Points::Sorted_Point_Value ) = 3.25 ; Case ( Get ( CurrentDate ) > Max ( Points:]ate ) + 90 ; Sum ( Points::Sorted_Point_Value ) - 3.25 ; Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 2 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) ) & If ( Sum ( Points::Sorted_Point_Value ) = 3 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 90 ; Sum ( Points::Sorted_Point_Value ) - 3 ; Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 2 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) ) & If ( Sum ( Points::Sorted_Point_Value ) = 2.75 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 90 ; Sum ( Points::Sorted_Point_Value ) - 2.75 ; Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 2 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) ) & If ( Sum ( Points::Sorted_Point_Value ) = 2.5 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 90 ; Sum ( Points::Sorted_Point_Value ) - 2.5 ; Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 2 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) ) & If ( Sum ( Points::Sorted_Point_Value ) = 2.25 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 90 ; Sum ( Points::Sorted_Point_Value ) - 2.25 ; Get ( CurrentDate ) > Max ( Points:\ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 2 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) ) & If ( Sum ( Points::Sorted_Point_Value ) = 2 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 2 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) ) & If ( Sum ( Points::Sorted_Point_Value ) = 1.75 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 1.75 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) ) & If ( Sum ( Points::Sorted_Point_Value ) = 1.5 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 1.5 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) ) & If ( Sum ( Points::Sorted_Point_Value ) = 1.25 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 1.25 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) ) & If ( Sum ( Points::Sorted_Point_Value ) = 1 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) ) & If ( Sum ( Points::Sorted_Point_Value ) = .75 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - .75 ; Sum ( Points::Sorted_Point_Value ) ) ) & If ( Sum ( Points::Sorted_Point_Value ) = .5 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - .5 ; Sum ( Points::Sorted_Point_Value ) ) ) & If ( Sum ( Points::Sorted_Point_Value ) = .25 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - .25 ; Sum ( Points::Sorted_Point_Value ) ) ) The issue i have now is. When someone has a point on say 1/20 then another on 2/21 then another on 3/23 they only get -1 credit when they should get -2 credit. So i've got part of it but i need to keep going with it. Any thoughts guys? Link to comment Share on other sites More sharing options...

AHunter3 Posted March 5, 2012 Share Posted March 5, 2012 Can you clarify, without me having to parse all of your code and all of your informal commentary on it? Your formula is difficult to read and unnecessarily convoluted — not the cause of your problem but makes it difficult to read. You should use Let () to summarize all the chunks of code that keep getting repeated over and over in your existing code. Link to comment Share on other sites More sharing options...

iDEViate Posted March 5, 2012 Author Share Posted March 5, 2012 oh Awesome, thanks for that bit of insight. I'll clean my code up and repost it. Heres the core of my code cleaned up. Let ( [ PV = Sum ( Points::Sorted_Point_Value ) ; MX = Max ( Points::Date ) ; CD = Get ( CurrentDate ) ] ; If PV ≥ 6 ; Case ( CD > MX + 150 ; PV - 6 ; CD > MX + 120 ; PV - 5 ; CD > MX + 90 ; PV - 4 ; CD > MX + 60 ; PV - 2 ; CD > MX + 30 ; PV - 1 ; PV ) ) ) This rolls the points off fine from the last point. The issue i'm having is say Occurrence #1 - 1 point on 12/20/2011 Occurrence #2 - 1 point on 2/02/2012 The employee will then have 1 point with this calculation. Even though before the 2nd point was created the calculation had rolled that point off. I need to devise a way to store the previous amount that had been rolled off. Hope that makes sense. Link to comment Share on other sites More sharing options...

Ender Posted March 5, 2012 Share Posted March 5, 2012 Yikes! that Let() statement didn't help. Too condensed! What's not clear to me is how you're recording attendance in order to know when the absences are. Link to comment Share on other sites More sharing options...

Ender Posted March 5, 2012 Share Posted March 5, 2012 Also important, what specific version of FileMaker? Link to comment Share on other sites More sharing options...

iDEViate Posted March 6, 2012 Author Share Posted March 6, 2012 i'm using FMSA11v4 and FMPA11v4. Hopefully this will help you understand how the data flows. I'm calculating the points in a field on the advisors table at this time but i'm open to all suggestions. http://www.mazdas247.com/members/KySpeed/ss.png If you need any other info just let me know. Link to comment Share on other sites More sharing options...

Ender Posted March 7, 2012 Share Posted March 7, 2012 I still don't see how you're recording attendance. Link to comment Share on other sites More sharing options...

iDEViate Posted March 21, 2012 Author Share Posted March 21, 2012 I've uploaded a very simple sample file for you to give you a idea of how i'm doing it. Thanks for the help guys I'm still struggling with this. http://www.msprotege.com/members/kyspeed/attendancerolloff.fp7 Link to comment Share on other sites More sharing options...

Ender Posted March 21, 2012 Share Posted March 21, 2012 You seem to be mixing "points" with attendance. They really need to be separate. Points are earned or lost as a result of attendance. Your "points" table seems to be absences, yes? I've never heard of "points" for attendance like this, so you have to explain what the rules are. Are points reset at the new fiscal year, or do people keep going into the hole for perfect attendance. Is 160+ the last increment? If someone has perfect attendance for 400 days, is that still -6? Do the negatives accumulate? example, if someone has perfect attendance for 95 days, do they get -4, or do they get the -1 for the first 30, plus the -2 for the first 60, plus the -4 for the first 90 (total of -7)? What does it mean to have positive or negative points? You say "After 30 days of perfect attendance...", it that calendar days or work days? What about when someone goes home sick after a few hours of work, is that an absence? I ask these questions because it's the exceptions that make the script or calculation difficult. 95% of the time, things fall into the typical case, but it's what to do about the outliers that you have to think about. Link to comment Share on other sites More sharing options...

iDEViate Posted March 21, 2012 Author Share Posted March 21, 2012 You seem to be mixing "points" with attendance. They really need to be separate. Points are earned or lost as a result of attendance. Your "points" table seems to be absences, yes? I've never heard of "points" for attendance like this, so you have to explain what the rules are. Are points reset at the new fiscal year, or do people keep going into the hole for perfect attendance. Is 160+ the last increment? If someone has perfect attendance for 400 days, is that still -6? Do the negatives accumulate? example, if someone has perfect attendance for 95 days, do they get -4, or do they get the -1 for the first 30, plus the -2 for the first 60, plus the -4 for the first 90 (total of -7)? What does it mean to have positive or negative points? You say "After 30 days of perfect attendance...", it that calendar days or work days? What about when someone goes home sick after a few hours of work, is that an absence? I ask these questions because it's the exceptions that make the script or calculation difficult. 95% of the time, things fall into the typical case, but it's what to do about the outliers that you have to think about. Anytime someone misses they are going to get a point so its one and the same thing. Negatives do not add up. I have this working but I really feel it could be done simpler. And my solution breaks if you aren't able to capture the value previously rolled off. Heres an example of a advisors attendance. 12/18 1 point 12/19 1 point 12/20 1 point. 12/25 1 point. Roll off date for this one point will be + 30 days if no other points have been accumulated. + 60 days will roll off 2 points. and 90 will roll off 4.. 120 will be 5 and 150 will be 6. 7 Is termination and points expire after 180 days. What i'm doing now is using the calc outlined earlier but modified a tad to include a field that holds the previously rolled off value. A server side script changes the points to 0 after 180 days. Runs once a day. I know this is really weird. Its for a call center which i work at. I just took this on as a project to learn FileMaker. Once again i have this working i just know there has to be a better way of doing it. As i've learned with my experience with FM I usually end up way over thinking things. Link to comment Share on other sites More sharing options...

Ender Posted March 22, 2012 Share Posted March 22, 2012 I would approach this by having each portal row within the last 180 days calculate the number of days since the previous absence. A recursive CF could probably do the same thing. Any period 30 days or longer gets a negative 1 for each 30 days. Link to comment Share on other sites More sharing options...

iDEViate Posted March 22, 2012 Author Share Posted March 22, 2012 I would approach this by having each portal row within the last 180 days calculate the number of days since the previous absence. A recursive CF could probably do the same thing. Any period 30 days or longer gets a negative 1 for each 30 days. I think your on the right track with that. The way i'm doing it really isn't ideal. The boat i was missing was how to get the value from the previous portal row. What function would I be using for that? Sorry i'm still rather new to FileMaker. Link to comment Share on other sites More sharing options...

iDEViate Posted March 22, 2012 Author Share Posted March 22, 2012 GetNthRecord ( Points::Date ; ( Get ( RecordID ) - 1 ) ) That the best way to d it? Link to comment Share on other sites More sharing options...

iDEViate Posted March 22, 2012 Author Share Posted March 22, 2012 http://www.msprotege.com/members/kyspeed/samplefile.fp7 The link above is a sample file showing what you described but my issue is.. The calculation isn't correct across all records? One is perfectly fine and correct. What am I over looking? ***EDIT Looks like if i use RecordNumber instead of RecordID it works just file. But can we confirm if the above sample file exhibits expected behavior ? Link to comment Share on other sites More sharing options...

Ender Posted March 22, 2012 Share Posted March 22, 2012 Here's how I'd do it. I left some intermediate calcs in there so you could see what's going on. Note the additional table occurrence on the graph and the relationship (including the sort option.) samplefile.fp7 Link to comment Share on other sites More sharing options...

iDEViate Posted March 22, 2012 Author Share Posted March 22, 2012 Shame i'm not a Gold member. Considering joining. really it would be worth it so i could get this calc finally completely worked out. Link to comment Share on other sites More sharing options...

iDEViate Posted March 22, 2012 Author Share Posted March 22, 2012 The file you posted breaks many ways. Overview of the four records in the advisors table Record 1. John Smith He should have 5 points. 1/16 should have been removed. Record 2.James should have 1 point not 3 The 104 day gap should've caused - 4 points to be removed which would've taken him down to 0 then the point on 5/1 bringing him to 1. Record 3. Susan has -1.. She should have 0 Record 4. Will is correct Thanks for your help. As the system i'm using now is copying the "current rolled off point value" into a field and subtracting that from the long case statements i listed earlier. It works but i really don't like to use it because if data isn't entered at the time of the absence it breaks. IE i put data in thats 3 months old and the system credits them with 3 months of perfect attendance when in reality they may not have had perfect attendance for 3 months the data just may have not been entered yet. EDIT.. Adding the evolution of the sample file. I made some changes It does calc correctly now on the current record and hopefully won't break once i write all the other scenarios into the calculation. Still think it could be simpler SampleFile Old.fp7 Link to comment Share on other sites More sharing options...

Ender Posted March 23, 2012 Share Posted March 23, 2012 Here's my version fixed. I changed the increment levels. Doesn't match what you said, but seems right to me. samplefile2.fp7 Link to comment Share on other sites More sharing options...

iDEViate Posted March 24, 2012 Author Share Posted March 24, 2012 Well it works now and doesn't break. The filtered relationship and unstored calc's are really taxing on the solution. Running really slow now when switching records but I guess my users will just have to deal with that. Link to comment Share on other sites More sharing options...

Ender Posted March 26, 2012 Share Posted March 26, 2012 That's going to be a drawback of calculating the date differences on the fly. You can work around this by taking storing the calculated Prev Absence in a regular Date field. This could be done with a script run once a day, like with a server-side script. Link to comment Share on other sites More sharing options...

## Recommended Posts