Asset register report/dashboard showing latest child row data only

knightyridebikey

Hi there, I'm trying to build an asset register in smartsheet with parent rows for the initial addition of the asset record and then child rows to record any future tickets logged against that asset and the corresponding asset status (so the asset history can easily be tracked). The objective is to provide customers with a live view of asset status by make/model.

I have a column to input the date the row was added to the asset register so, in theory, I was hoping you could generate a report or dashboard that either shows the parent row data if there are no child rows and if there are child rows, pull in only the row with the most recent date.

Report table would ideally look something like this:

Summary:

• In-stock - XX qty
• Delivered - XX qty
• Collected by retirement provider - XX qty
• Awaiting assessment - XX qty
• etc

Make/Model breakdown

In-stock:

• Asset type 1 qty
• Asset type 2 qty
• etc

Awaiting Assessment

• Asset type 1 qty
• Asset type 2 qty
• etc

Is this possible?

SSFeatures

Hi @knightyridebikey,

This is how I would recommend doing it.

Create a new checkbox column named Is Most Recent Entry. This column is going to detect if the row is the most recent row for each asset by looking at the Date for the row.

Notice how the checkbox is selected for the last 3 rows. For example Asset 1 most recent entry is 10/04/24, so row 4 is checked for asset 1.

The formula to do this is:

=IF(MAX(COLLECT(Date:Date, Asset:Asset, @cell = Asset@row)) = Date@row, true, false)

This formula says:

• For the current row, get all of the dates that match the asset in this row.
• For all of these dates, find me the maximum one for this asset.
• If this max date matches my current row's date, check the box, otherwise don't check the box.

This formula assumes that asset 1 won't be added twice in the same day.

This formula also works for child and parents. So if you want to group your assets where you have one Asset 1 parent and multiple Asset 1 children, it will work correctly.

I recommend right clicking the cell and click Convert to Column Formula so that the formula applies to the entire column.

Then in your report you can filter to only include rows where the checkbox is checked.

You can also Group by Status and Summarize → Sum by Qty to summarize and breakdown the data however you want.

I hope this helps!

knightyridebikey

Hi Nathan, sorry for the very delayed response, this is brilliant, thanks you so much, it has worked a treat!