Page 1 of 1

How to calculate weekly pay

PostPosted: Mon Nov 16, 2015 10:56 am
by philippe

I have read the demo about setting up a pay database, but I am not sure it answers my question - here it is:

I am a ski instructor, and I have a database of lessons given, each lesson having a bunch of elements (client name, type, pay rate etc, many of them linked to other databases.)

The relevant elements for my quandary are:
The lesson record shows date lesson was given
Has a field that contains a (calculated) pay for the lesson
Has a field that contains a total pay to date since the beginning of the season.

The objective is to calculate a pay for the last 14 days - making sure it matches my pay check for the same period....

The approach I took is to create a separate database, the records of which calculate the pay for any given 14 period. In those pay period calculation records,
- I enter the last day of the pay period;
- A db pop up looks for the total season to date pay total for the last lesson given on the last day of the pay period
- a field calculates the date of 14 days ago
- a second DB pop up retrieves the total season pay to date of 14 days ago
- a calculated field calculates the difference - voila, pay for the last 14 days.....

However, if I did happen to have a rare day off on the last day of the period, the DB pop does not retrieve anything, since there was no lesson record for that day ,and therefore no pay to date total to retrieve... (same issue with if there was no lesson record 14 days ago)

How can I
- either retrieve the last lesson record of the day prior, if there is no lesson for the day I am looking up
- use a different approach that will yield the total pay for any 14 given days, regardless of whether I worked on the bounty days of the pay period ?

Thanks for your help!

Re: How to calculate weekly pay

PostPosted: Sat Nov 21, 2015 8:47 am
by Brian_Houghton
To pull a value from a related database, you can use the "show from other DB" parameter of the relationship field. This would cause the value to be pulled across so you could manipulate it.

However, there may be a simpler option depending upon your setup. If you simply want to see pay for the last 14 days, you can:

1. Create a custom view
2. Add a filter to the custom view
3. Configure the filter to show last X days (based on the date field)
4. Set the range to 14

This will show the records from the last 14 days.

You can then either run a report on the float field that stores your pay amounts (tap on field header in list view, then tap Report) or you can add a calculated field that adds the values for you, giving you a running total. The formula would be Previous Value of Running Total Field + Current Value of Amount Field.