3 Replies Latest reply on Oct 4, 2016 6:32 AM by scheip22

# Update values according to the status at installation and current date

Dear all,

I’m struggling with two formulas, wherefore I could not find any function.

1. To update the utilization (workhours) of a machine, the daily workhours (a, known) have to  be imported. This value have to be added to the total workhours (X) of the machine. The next day, a new record will be added, where the daily workhours should be added again to the total workhours.

So the output of day 1, should be an input for day 2, which outcome should replace the output of day 1 at the general page.

X1 = X0 + a1

X2 = X1 + a2

Xn = Xn-1 + a3

2. In my calculation I have to refer to data (total workhours) in another lay-out (A) that changes every day. There have to be a link between the creating date of the imported value (workhours of a specific component) in lay-out B and the value of the data in lay-out A at that date.

For example: (X – Y) + Z = Q

X is known, the value at lay-out A, TODAY

Y is the value at lay-out A, at 01/01/2016

Z is the imported value at 01/01/2016 in lay-out B

X and Y were calculated in the same field. Before X, the value was Y. Y went X, due to calculations in function of time. X-Y is the difference in value between 01/01/2016 till now.

I’m struggling with it for 2 days now. Could somebody help me?

Thanks a lot!

• ###### 1. Re: Update values according to the status at installation and current date

So if your imported data looks like this:

WorkstationDateTime In Time Out
Station 109/01/201608:00 AM08:30 AM
Station 109/01/201609:00 AM09:30 AM
Station 109/01/201610:00 AM10:30 AM
Station 209/01/201608:00 AM08:30 AM
Station 309/01/201608:00 AM08:30 AM
Station 309/01/201609:00 AM09:30 AM
Station 409/01/201608:00 AM08:30 AM

You are looking for TWO summary values, the total time spent (lifetime) of any one station, and also the daily time spent per workstation.

You can do this simply with a subsummary report based on the data you already have.

-First subsummary would be by the Workstation field

-Second subsummary would be by date.

-Remove the body part, you don't need it unless you wanted to see every record of usage.

-An auto-enter calculation of TimeOut - TimeIn would give you a new numeric field of "timespent" (adjust this to hours if needed)

-A summary-type field would total the "timespent" field

-Place the summary field in both subsummary parts on your layout.

-Sort your data by Workstation AND date ascending.

Boom, you have a report that now looks like this:

Workstation 1                         Total Hours:     1.5

09/01/2016                   Daily Hours:     1.5

Workstation 2                         Total Hours:     0.5

09/01/2016                   Daily Hours:     0.5

Workstation 3                         Total Hours:     1.0

09/01/2016                   Daily Hours:     1.0

Workstation 4                         Total Hours:     0.5

09/01/2016                   Daily Hours:     0.5

If you want to fool around with getting "subsummarized" values outside of subsummary part reports, then the GetSummary() function is what you need to look into. This will allow you to get a summary field, and divide it based on a break point (EG workstation name or date, or combination of both).

FileMaker Pro 15 Help - GetSummary()

If you have a table of Machines that is related to the imported table of Usage, you can easily get the lifetime summary with a calculation of:

Sum(Usage::TimeSpent)

1 of 1 people found this helpful
• ###### 2. Re: Update values according to the status at installation and current date

Hi Mike,

Many thanks!

Laurens

• ###### 3. Re: Update values according to the status at installation and current date

Hi Mike,

My question is now, how could I determine the subtotal of workhours at 29/09, for example.

29/09 will be the layout 2::Install.Date of a new component of the machine. So for that component,

there will be another amount of total workhours at this moment (05 oct. 2016).

In this case:

Subtotal at 29/09 will be: 7 Workcycles

So the component will have now (21 - 14) 7 work cycles; layout 1::Current FC - ( layout1::SubTotal FC, where layout 1::Entry Date is the first Date that occurs before layout 2::Installation Date).

Or in another way; I should have a summary (GetSummary) of all the layout 1::Entry.FC where layout 1::Entry.Date > layout 2::Install.Date.

Thx!