Hours Tracking

[Beuwolf]

Hi Guys,
I have been searching for a solution to this for awhile now and would appreciate any insights you can offer up, here goes

 

I have database to track and invoice my hours as a truck driver, I have two tables set up,
1. Work (Pic 1)
2. Customers (Pic 2)

In the work table i have a calculation field called 'Hours Paid' with this calculation
If (Hours Worked<10; "10.0"; Hours Worked)
straight forward enough so far...

I have recently added a new customer who pays a different min hours to my others so what i need to be able to do is check Hours worked against a field called 'customer::Min Hours' and if less than the 'customer::Min Hours' it will use the number in 'customer::Min Hours' but if more than the 'customer::Min Hours' field it will use the value in field 'Hours worked'.

to make it more interesting the field 'Hours Worked' is a repeating field and when i have tried just linking in the forumla it gives me the correct data in the first repeat but text in the others.....

Any Help would be greatly received

[comment]

You need to use the Extend() function when referencing a non-repeating field in a repeating calculation field.

Notes:

1. It would be better to use records in a related table instead of  repeating fields;
2. You should lookup the MinHours value into a local field, so that the calculation can be stored. Otherwise future changes in customer's minimum hours will also affect your past records.

[Beuwolf]

Thanks for that and I'm getting closer to a solution it seems, I'm trying the extend option but to not real sucess,

I have amended the calculation thus (Also changed the Min Hours to MinTIme)
If (Hours Worked<customer::MinTime; (Extend (customer::MinTime)); Hours Worked)
But even though the First argument and the last argument work the extend doesn't... Am i being completely primary school here and doing something so obvious I cant see it?

Thanks for the help its been driving me nuts :-)

[comment]

If (Hours Worked<customer::MinTime; (Extend (customer::MinTime)); Hours Worked)

That should be:

If ( Hours Worked < Extend ( customer::MinTime ) ; Extend ( customer::MinTime ) ; Hours Worked )

Or more simply:

Max ( Hours Worked ; Extend ( customer::MinTime ) )

I repeat my two notes above.

[Beuwolf]

Hi Comment,

Thanks for that I have tried both options and find that all unused repeats are auto filling withe the first value. I'm guessing I am doing something wrong here but have no clue as to what. I have also tried bringing the data into a lookup field with similar results. Dunno what is happening and going slightly cross eyed.

Thanks

 

[comment]

See if this works for you:

 PaidTime.fp7

Note: the condition Case ( cDuration ; ... ) is necessary only because repeating fields are being used. With related records, there would be no empty "slots" (and you also wouldn't be limited to 7 slots per job).

[Beuwolf]

Thanks Comment

I'll have a look
Thanks again for your help on this

Marc

[Beuwolf]

Hi Comment,

Ive checked it out I think the problem lies in that i do work for different clients but only create one final invoice for the week that goes to my agent, what i need to do is be able to use different clients on a daily basis in the same record. each client has differing hourly minimums so if client A has a 8 hour min then Client B has a 10 hour min etc. I'm beginning to understand why no one has done this before it seems to be a pain.

Cheers

Marc

[comment]

Ive checked it out I think the problem lies in that i do work for different clients but only create one final invoice for the week that goes to my agent

Why do you think this is a problem? Perhaps I am missing something, but it seems quite simple to me:

 PaidTime2.fp7

 

PaidTime2.fp7

[Beuwolf]

I have to format the days worked into their accepted format, I am trying to get them to accept a table format with a ll the data in but at the moment they want it in mondays work in mondays slot etc etc. I think I have been to tied up in the formatting of the file rather than the mechanics behind the file which is why i have been running around in circles. Ive added a copy of the current file i use so you can see what i'm trying to do

Thanks for all your help on this its appreciated

27-7 v4 Clone.fmp12

Edited September 2, 2015 by Beuwolf

[comment]

I have to format the days worked into their accepted format

Can you elaborate? Is this a paper report? If yes, post a screenshot.

[Beuwolf]

This is the format that i have to post it to them in, on this week only one client but usually two or three each on different days

Edited September 2, 2015 by Beuwolf

[comment]

I see. You need to make a decision here: if your file is used only for producing this report and nothing else, there's no reason why you couldn't use repeating fields for the daily data. OTOH, if you want your data to be usable for other purposes, then you cannot put it in repeating fields - you need to use the structure outlined in my second file. Then the problem becomes one of display.

BTW, I see different vehicles being used on different days, for different clients. Do you never use two different vehicles and/or work for two different clients on the same day?

[Beuwolf]

This report is the only one i use, i want to end up with a hosted database so i can use phone, ipad or computer to input data. I only work for one client on any given day due to the laws regarding driving.

I think probably the easiest rout would be to use the repeating fields and manually change the hours as and when required

[comment]

I think probably the easiest rout would be to use the repeating fields and manually change the hours as and when required

Sorry, I am not entirely with you. What do you mean by "manually change the hours as and when required"?

[Beuwolf]

If i do less than the customers min hours i need to change that in the file before i print the report.

[comment]

Okay, let's try that again, shall we?

 PaidTime3.fp7

PaidTime3.fp7

[Beuwolf]

Comment you are a legend