Counting wrong

[punkshift]

I have Assets Table and Asset Checkin Checkout Table. I’m trying to count how many times an asset has been checked out.

In the Assets Table, I have a virtual column that counts the Check Out Date of a specific Asset Tag # with this Expression:

COUNT(SELECT(Asset Checkin Checkout[Checked Out to], [Asset Tag #]=[_THISROW].[Asset Tag #]))

However, if I try to check in the asset, it still counts it as one but I didn’t put anything in that column but instead in the Check In Date.

Same goes with the Scanners and Scanner Checkin Checkout Table but both Checkouts and Checkins Virtual Columns count the same thing.

COUNT(SELECT(Scanner Checkin Checkout[Check Out Date], [Asset Tag #]=[_THISROW].[Asset Tag #]))

COUNT(SELECT(Scanner Checkin Checkout[Check In Date], [Asset Tag #]=[_THISROW].[Asset Tag #]))

[1minManager]

You say this is a virtual column. See if the COUNT() is correct if you edit the Assets Table record

[Marc_Dillon]

1. COUNT() just counts the number of records. A blank value still “counts” as a record. You should use a ISBLANK condition in your SELECT.

2. You’re describing a pretty standard parent-child relation. I hope you have your references setup properly (link below if not). If so, you should have a [Related…] VC in your parent Table. Use that instead of re-running the equality against your parent ID.

All together:

COUNT( SELECT(
  [Related...][key-column] ,
  ISNOTBLANK( [Check Out Date]
) )

help.appsheet.com

References Between Tables

Learn about references, when to create them, and how to use them so your data makes sense.

help.appsheet.com

ISNOTBLANK()

Is value present?

[punkshift · Accepted solution]

I tried your suggested expression, however, it didn’t work for me or I just didn’t do it right. lol

Anyway, upon looking at the links you provided this is what I came up with:

COUNT(FILTER(“Asset Checkin Checkout”, AND(ISNOTBLANK([Check Out Date]), ([Asset Tag #] = [_THISROW].[Asset Tag #]))))

And it works now.

Thanks!