Daily check-in on resource tracking?


#1

Here’s the aim.

We have a marina we’re managing. There are numerous “Slips” that “Customers” are referenced in as such:

A1 = John Doe
A2 = Jane Doe
etc.

We have a crew that goes out a few times a day, and logs if the boat that is supposed to be in the slip is there or not.

I’m trying to set it up so the crew’s information is as up-to-date as possible, and end up with a report the coast guard could look at to see when boats were in, or not in, their proper slip.

Very open to ideas.


#2

Off the top of my head, I’d say track the transitions.

  1. Your main table is [Slips], containing info on the boat, owner, contact, payment, whatever.
  2. Each slip record also contains a one-to-many link to [Status].
  3. Each [Status] record contains a timestamp (probably generated from CREATED_TIME()) and a {Status} of OUT or IN.
  4. Each [Status] record contains {InTime} and {OutTime} that duplicates {Time} according to {Status}.
  5. Each [Slip] record contains a {Latest}, {LatestIn}, and {LatestOut} rollup fields that roll up {Status::Time}, {Status::InTime}, and {Status::OutTime}, respectively, with aggregation functions of MAX(values).
  6. The default status is that the boat is in the slip.
  7. When a slip goes empty->full or full->empty, record it. (That is, create a new [Status] record with Status} set accordingly.)
  8. IF {Latest} = {LatestOut}, {Slip::Status} = 'Out'; otherwise {Slip::Status} = 'In'.

This gives you an accurate snapshot of the marina at the granularity of the interval between surveys.

By listing all [Status] records for a slip, you can produce a history of slip transitions.

Demonstration base here.


#4

This is great, thank you!