Skip to main content

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.

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.

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.


This is great, thank you!

Terms & ConditionsAccessibility statement