And if you were able to calculate such sub totals, where would you display them? In another column in the portal? In a separate portal with one row for each PayCode?
Both the ExecuteSQL() function and a self join linking Wages Details to a second occurrence of itself are possible methods for calculating the subtotal that you want.
When it comes to changing an employee's hourly rate, the typical method used is to record the rate in one table and use an auto-enter field option to copy the rate from the related table to wages details or wages for use in calculating the amount earned. Because the auto-enter option copies over data, the rate change affects only the next new record to be created, not existing records.
Where you put that wage rate for purposes of managing such changes depends on whether employees are paid at the same rate for every paycode or paid at different rates for different paycodes.
Thank you for you reply, I really have a lot to learn, working with layouts I'm fine with, but when it come to calculations, some I get, but other are way over my head. I will have to do some study/research on you answers to understand the functions a little better.
The sub totals would be displayed on the Wages layout under the Wages Detail portal, similar to an invoice totals, each portal window will only hold a fortnight of entries. e.g.: The Wages record is one fortnight, then the portal holds the entries for that fortnight.
Our hours are allocated to a Job, e.g. 8hrs Allocated, if we complete this in 7hrs, this is Actual hrs calc by start & finish time, but some jobs we still get the fixed 8hrs good for us, if we go over 1hr plus bad luck, to keep track of whether you are in front or behind would be beneficial. to have it calculated on the fly e.g. Allocated Hrs, Actual Hrs & Paid hrs. this would be great .
The above would be set up as these, all will have Alloc, Act & Pd hrs, So at a glance you can see if your up, even or down
Pay codes are Local Travel, Regional travel, Hire Car Travel, Plane Travel, Wait Plane, Job, Training, Paperwork, Consulting, Allowances and the are more and all with different pay rates
I have been working on this off & on for weeks, a came up with a solution. all data will be entered by a iPhone, small area of Realestate to work with, this was a challenge. see pic
This works ok, but not to sure if its done right, i added tables for each pay code, and totalled them to achieve this, i use slide buttons and popups to better use the little space. All the data is entered via a popup with a portal.
I'm now thinking the phone screen may be to small for what i need to do, but its what we all carry with us, the portal is tight to enter Date, Start & Finish times, Job Name, Pay Code, Alloc & Pd Hs, there are three lines which makes it hard once you have entered a few records.
Again thank you for your advice and assistance
For this post I am only focusing on one small part of your post:
The sub totals would be displayed on the Wages layout under the Wages Detail portal, similar to an invoice totals,
But an invoice total just adds up all of the items shown and provides a total. You show two different paycodes in your example and want a total for each. that's a much different proposition here--especially if you have multiple paycodes possible, not just two.
This can be done, but the best method depends on exactly what kind of total you want here and also whether there is a fixed, small number of pay codes or a constantly changing number of pay codes that vary from employee to employee and week to week.
Best guess here is to use a second portal with one row for each pay code that lists the relevant paycodes for the current record and computes a subtotal for each.