Check if a set of start date and end date are consistent

[globdug]

Hello,
I’m doing an Asset Management doc. I have a table to track each asset when it has been checkin to a person/location/other and when it has been checkout.

I embed an example:

I wanted to add some check to know the status of an asset and, just for me, to know if I inserted some wrong date.

I would like to share an example of what I’m doing also to get some suggestions about my approach.

A little explanation of tables:

• Single assets: just a list of the assets.
• Asset tracking: the table to track each asset.
  • Asset: is the primary field and is a relation to Single assets table.
  • Checkin is to track when the asset has been assigned to someone/somewhere.
  • Checkout is to track when the asset has been returned.
  • Checkout Count is the number of times an asset is returned.
  • Asset Count is the number of times an asset is in the table.
  • Status it’s where I’m stuggling.

This is the formula in Status.

SwitchIf(
  thisRow.[Checkout Count]=thisRow.[Asset Count], "Free",
   And(
    thisTable.Filter(thisRow.Asset=Asset).Checkout.Sort(False).First()
    >
    thisTable.Filter(thisRow.Asset=Asset).Checkin.Sort(False).First(),
    thisRow.[Checkout Count]<thisRow.[Asset Count]
   ), "Check tracking",
    thisRow.[Checkout Count]<thisRow.[Asset Count], "Assigned"
)

Explanation:

1. If Checkout Count and Asset Count are equals it means the the asset is returned so it’s free to be deployed again.
2. If the most recent Checkout date is greater than the most recent Checkin date and Checkout Count is less than the Asset Count it means that I have done some mistake in Checkin/Checkout fields of the asset (See in the example PC03) and the Status is “Check tracking” to alert me.
3. If Checkout Count is less then Asset Count it means that the asset is not returned so it’s Assigned.

Now the question: I would like to add another check, I would like to check that the checkin/checkout dates of an asset are correct, so a checkout date can not be later of the next checkin date, this is not possible.
So in a scenario like this:

I should get an error cause the asset cannot be checkin (01/09/2024) before it has been checkedout (05/09/2024).

How could I achieve that and what could be other checks and suggestions?

I’m new to Coda and I maybe I could reach same result in a simpler way.

[globdug]

After a “little” help of Chat GPT I achieved what I wanted, I’m sharing the Doc, maybe will be helpful to someone else.

The doc is still work in progress and I’m making changes while adding items and facing new issues and needs.

But I’m at a good point, I think.

I used Snipe-IT for sometime but I need something faster and more readable also on smartphone, so I’m doing this dock.

By the way, for the specific problem that I have asked help for I used at the end this formula in a SwitchIf statement.

  thisRow.Checkin < thisTable
    .Filter(Asset = thisRow.Asset)
    .Sort(true, thisTable.Checkin)
    .Filter(CurrentValue.Checkin < thisRow.Checkin)
    .Last()
    .Checkout,
  "Error #2: Sequence of checkin/checkout are not chronologically correct.",
  

The entire formula of Status column is:

SwitchIf(  
  // Check 1
  // Check if checkin > checkout to generate a warning
  thisRow.Checkin > thisRow.Checkout,
  "Error #1: Checkin greater than Checkout.",
  
  // Check 2
  // Check if each checkin is greater than the previous checkout
  thisRow.Checkin < thisTable
    .Filter(Asset = thisRow.Asset)
    .Sort(true, thisTable.Checkin)
    .Filter(CurrentValue.Checkin < thisRow.Checkin)
    .Last()
    .Checkout,
  "Error #2: Sequence of checkin/checkout are not chronologically correct.",
  
  // Check 3
  // If checkout and assets are not the same count, check if the latest checkout is greater than the latest checking to check if I maybe misfilled the dates
  And(
    thisTable.Filter(thisRow.Asset=Asset).Checkout.Sort(False).First()
    >
    thisTable.Filter(thisRow.Asset=Asset).Checkin.Sort(False).First(),
    thisRow.[Checkout Count]<thisRow.[Asset Count]
  ),
  "Error #3: missing some checkout before the latest",
  
  // Check 4
  // Check if checkin is empty and checkout is filled
  And(thisRow.Checkin.IsBlank(), thisRow.Checkout.IsNotBlank()),
  "Error #4: missing checkin.",
  
  // Check 5
  // Difference between asset occurences and checkout must be max of 1,
  // if it's greater I must have forgot to enter some checkout
  thisRow.[Asset Count] - thisRow.[Checkout Count] >= 2,
  "Error #5: missing some checkout.",
  
  // Check if free
  thisRow.[Checkout Count]=thisRow.[Asset Count],
  "Free",
  
  // Check if assigned
  thisRow.[Checkout Count]<thisRow.[Asset Count],
  "Assigned",
  
  "SwitchIf DEFAULT, CHECK THE CHECKS"
)

In this way, I do all the checks I want before simply checking if the asset is free or assigned. And if the value returned start with “Error” the rows of the asset are formatted yellow with codnitional format options so I can check manually what I have typed wrong.