3 Replies Latest reply on Feb 20, 2015 4:08 PM by philmodjunk

# Calculations

### Title

Calculations

### Post

Just new FM, if someone could assist.

I have a portal on a layout, i would like to do a sub total by data entered in to that category

I have setup a
Table - Wages, -ID, Date From, Date To, Total Hrs, Total Time, Gross Wage, Tax, Net Wage.
Table - Wages Details - Date, Job, Pay Code, Start Time, Finish Time & Total

There are other fields also but just need one to start, there is to many to write here

Portal on the wages layout- Wages Details

In that portal with the headings noted below

Date,  AU        Job Name, Pay Code, Start Time,     Finish Time,   Total
D=11/02/15, JN=Myer, PC=MTR, ST=06:00 am, FT=06:45 am, T=00:45
D=11/02/15, JN=Myer, PC=WRK, ST=06:45 am, FT=12:45pm, T=6:00
D=11/02/15, JN=Myer, PC=MTR, ST=12:45 pm, FT=13:45 am, T=01:00
D=11/02/15, JN=Myer, PC=WRK, ST=13:45 am, FT=15:30 pm, T=01:45
D=11/02/15, JN=Myer, PC=MTR, ST=15:30 am, FT=17:30 pm, T=02:00

So the above for MTR would = 00:45+01:00+02:00 = 3:45hrs, for WRK = 06:45+1:45 = 07:45

I would like to find out how to Subtotal by the Pay Codes column , e.g.: MTR & WRK as i have Travel Hrs, Work Hrs, and others.

I have calculated the total portal column, by total time,  but not the above

I know its a calculation, but not how to do it.

Also, if i have a Pay Rate table and my rate is, say \$18 per hr, and i get a pay rise to \$20 per hr, if i add to that table, how do i auto select the new pay rate for new entries but keep the old for old entries, would i have to have a Pay Level field to lock it to that field.

It would be kind of you to help, thanking you in advance

hoping i have explained it ok

thanks

Geoff

• ###### 1. Re: Calculations

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.

• ###### 2. Re: Calculations

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.

• ###### 3. Re: Calculations

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.