I want to make a calculation that I need help with.  I want filemaker to tell me how many days a work order has been late.  For instance when we make a work order we give it 7 business days till due.  I would like filemaker to tell me how many days its past due if the order is still open.  What would the calculation be?

//something like

Let (

[

_status = Status Flag ;

_due = 7 ;

_overdue_days = ( date + _due ) - date

];

If ( _status = "open" ; _overdue_days ; "" )

)

Why not

Let ( due = workorderDate + 7 ;
Case ( status = "Open" ; Get ( CurrentDate ) - due )
)

HTH

calculation must be set to unstored in options also because you are using the current date so it must change constantly or at least when viewed.

Why not

Let ( due = workorderDate + 7 ;
Case ( status = "Open" ; Get ( CurrentDate ) - due )
)

Why not calculate anything only if the status is open?

Case ( 
  statusField = "open" ;
  Get ( CurrentDate ) - workorderCreation + 7 
)

For instance when we make a work order we give it 7 business days till due.

You need to tell us what counts as a "business day" in your business: weekdays, holidays, etc.

Monday thru Friday

I want filemaker to tell me how many days a work order has been late.  For instance when we make a work order we give it 7 business days till due.  I would like filemaker to tell me how many days its past due if the order is still open. 

You need to start by calculating how many business days are there between the date of the order and today - which is not trivial at all. Then compare this with the number of business days allowed for the order.

The following fields are assumed:

• OrderDate (Date)
• DueDays (Number)
• DeliveredDate (Date)

Let ( [ 
workStart = OrderDate + Choose ( Mod ( OrderDate - 1 ; 7 ) - 5 ; 2 ; 1 ) ;
n = Get ( CurrentDate ) - workStart ; 
w = Div ( n ; 7 ) ; 
r = Mod ( n ; 7 ) ; 
d = Mod ( workStart - 1 ; 7 ) ; 
a = ( d + r > 5 ) + ( d + r > 6 ) ;
workdays = 5 * w + r - a ;
overdue = workdays - DueDays
] ;
Case ( overdue > 0 and not DeliveredDate ; overdue )
)

Note:
I believe this has come up in another question of yours: you should record the closure of a project by entering a date into a date field (in this example, the DeliveryDate field). Once you have done that, the status of the project is given by the existence of the date in this field, and an additional Status field is unnecessary.

 

 

Edited July 28, 201510 yr by comment