Display link to Resource Management in Smartsheet report

dpm1028

Hello,

I have Resource Management attached to a number of Smartsheet projects. But there are some that are not. Is there a way, in a report from Smartsheet, to see the projects that I have linked to RM? Some indication that the link has been established or not?

Thank you

Dan

Brian_Richardson

There's not a built-in report that you could just pull to do this. You could accomplish it, though, in a few different ways depending on what capabilities you have on your Smartsheet account and what permissions you have in RM.

Option 1: Manually download a list of projects from RM by going to Settings…Account Settings…Project Import…Export Project List. Import that into Smartsheet by copy/pasting it to a standing "RM Projects" sheet. Have another sheet where you've listed all your projects and have a formula using a reference to lookup the project names against the RM Projects sheet to see if anything matches.

Option 2: Setup an Analytics report in RM for TIme & Fees and group by Project, then convert to an Advanced report. That will sync the data to Smartsheet, and includes project custom fields and user custom fields. You can then perform lookups from a list of projects on another sheet to see if that project exists in the data. If you want to do that by some ID, you can add the ID as a custom field on the project in RM, and then look for it in the Advanced Report.

Option 3: If you have Bridge, you can pull a list of projects from the RM API and include the RM project ID and potentially custom fields. You can then match to the RM ID and/or a custom field like your own internal project IDs.

dpm1028

Thank you Brian, I will try option 2 then 1.

dpm1028

Not sure I have - or know what Bridge is. But I'll look.

dpm1028

Hi Brian,

I'm still working on this. I am pursuing option 2. Can you elaborate on:

"You can then perform lookups from a list of projects on another sheet to see if that project exists in the data."

I'm wondering if I can add a field in the smartsheet that is linked to RM that simply indicates the RM project exists. Like a "y" if there is a RM project or a "n" if not?

Brian_Richardson

If you use the Advanced Report option, then what you’ll see in Smartsheet is a sheet with all the assignments listed. The information on that sheet includes the project name and any custom fields.

So, from the other sheet that you have projects listed in, that you want to setup a flag for whether a project is in RM or not, you can add a column and use Index/Match to do a lookup from that sheet to the advanced report and look for the project name. If it’s found then you know that the project exists in RM.

=If(Index({Project Name},Match([Project Name]@row,{Project Name},0))<>””,”Yes”,”No”)

{Project Name} is a reference to the advanced report that you insert by clicking Reference Another Sheet in the formula pop box as you type out the formula. It should point to the Project Name column in the Advanced Report.

dpm1028

Hi,

=If(Index({Project Name},Match([Project Name]@row,{Project Name},0))<>””,”Yes”,”No”)

If(Index({Project Name}, - references the sheet I just created by setting up the report in RM correct? I point to the column that has the Project Name?

Match([Project Name]@row,{Project Name},0)) - refences the sheet I am in (original). But the name I would be comparing to is the name of the sheet. Not a field.

Sorry if I am missing the obvious.

dpm1028

Thank you again Brian for helping me, I am close, but not quite there. I may have not made sense, I think I am trying to match the data in a column to the name of the sheet.

Thank you again,

Dan

dpm1028