Copy Row Automation - Eliminating Duplicates OR keeping latest Row entry/update

Hello fellow Smartsheeters,

I am trying to capture entries from a project level RAID log and pushing them (based on specific criteria) to an Enterprise level RAID log. Specifically, any Risks or Issues that are created and updated need to be pushed to another sheet.

I am familiar and frequently use the Copy Row Automation but when a PM updates a Risk/Issue that is still open, the automation does not update the pre-existing entry; instead it adds a new entry into the sheet. Obviously, this creates multiple entries for the same item.

I have not been able to alter the automation to satisfy my request, there is no filter on the Destination Sheet for me to eliminate old entries/keep latest entry, and if I create a Report from the Destination sheet, I still run into the same issue with filters.

I could probably fiddle with some complex INDEX/MATCH formulas but in this case, it won't work. If the RAID entries were defined, then I suppose I could use a formula for this but, RAID entries fluctuate throughout the life of the project.

I could also add some manual work into the Destination Sheet and if it is reviewed monthly (for example), I could filter by Project, see the rows that are out of date, and delete them. This is the less than ideal situation.

If anyone has any experience with how to work with this issue, I am all eyes and ears!

Thanks in advance!

Find more posts tagged with

Sheets

Formulas

Reports

Automations

Accepted answers

All comments

Paul Newcome

Have you looked into creating a row report instead?

Nick Stamatakis

Thanks for the reply @Paul Newcome. To be honest, I tried that but I can't, for the life of me, figure out (with filters) how to remove duplicates and keep the latest entry.

Do you have any suggestions? Here are the fields I am pulling; essentially in a setup/example like this, I'd want to keep Rows 2 and 3 and remove Row 1 (as it is a duplicate of Row 3).

This is a Row Report, FYI. Thanks in advance!

Paul Newcome

You would reference the individual sheets. The row report replaces your master sheet.

Nick Stamatakis

I am not sure that will be a practical solution @Paul Newcome. I will have multiple (100+) projects, each with their own RAID log, that a PM would be updating open entries. This has to be dynamic enough such that:

When the PM opens a project via Control Centre, their RAID log is automatically connected to the Enterprise RAID log
The PM needs to be the lead in updating their project level RAID log so that I can report at an Enterprise level

If I did manually add new RAID logs as projects were spun up, I will still be in the same situation. I don't think your intent to suggest manually referencing cells would be a practical, long term solution.

I am not sure I see your solution working but I could also be misunderstanding what you are suggesting :-)

Paul Newcome

You can create a dynamic report in Control Center that will automatically have new raid logs added to it as they are created.

The row report will automatically update as the individual sheets are updated.

Nick Stamatakis

Do I need to have the dynamic report setup to facilitate this? I didn't realize this option.

So essentially I setup a Dynamic Report (will need to read up on this; never used before), and from that, I would create a Row Report feeding off the Dynamic Report and this would resolve my duplicate issue?

As always, I appreciate your time and knowledge on this @Paul Newcome :-)

Paul Newcome

The Dynamic Report is a row report.

https://help.smartsheet.com/articles/2482112-dynamic-report-scope

Nick Stamatakis

So first off @Paul Newcome; mind blown! Not sure why I didn't know about this, given I've been through Control Centre training, etc. I guess good things come in stages. A few questions:

SS (the link you sent me) tells me that up to 50 reports can feed into this dynamic report. My new Dynamic report shows me that 56 sheets are currently feeding into this report. I presume this article means I can have up to 50 dynamic reports per Blueprint, correct?
Is it better for me to setup the columns I want to appear in the Dynamic Report in advance or just let it flow once items start reporting to it?
I have one column that I am porting over from the project RAID to the Dynamic Report that contains hyperlinked text that allows an end user to click on and go into the project's Dashboard. It's porting over the text but not the link; anything I can do to fix that?

The reason for my second question is that the Dynamic Report decided what would be the Primary column, which is obviously not what I intended on selecting. Would you recommend setting the columns I want to appear in the Dynamic Report first and then adjusting as needed or am I stuck with the selection?

This feature has opened many doors for me so once I can get this fine tuned, you are in my SS Hall of Fame!

Paul Newcome

The primary column in a report is based on the primary column used in the source sheet(s). It cannot be changed.

Do all of your projects get provisioned into the same workspace, or does each project get its own workspace?

The hyperlink should pull over. I use this very frequently especially in control center builds to drop a link to every project on a portfolio level dashboard.

Nick Stamatakis

Hi @Paul Newcome . I think I've got it all sorted out, which is great. Thanks for sharing this feature with me.

My projects do get provisioned in the workspace but this reporting is in another workspace.

Unfortunately, the hyperlink isn't pulling over; the text is but that's it. Both columns at source and destination are Text/Number fields. When the project is created and the hyperlink is made, it gets push where I need it to go. But, when I am making this dynamic report, I am pulling that link from the project's metadata sheet. Is that additional degree of separation causing it to not pull over?

Not sure what I am missing to make this work. Thanks in advance!

Paul Newcome

If you are provisioning all projects in a workspace and need to bring in more than 50 sheets, you can skip the dynamic report altogether.

You can create a regular report that references the workspace (this adds new sheets as they are added to the workspace) and create a filter in the report based on the sheet name to only pull in sheets that contain "RAID" in the Sheet Name.

My suggestion would be to create the regular report and reference only a single RAID sheet to begin with. That will make it much easier to select your columns and get everything else set up. Then change the reference to the workspace and include the sheet name filter.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.