Issues Connecting Dates in Blueprint derived Workspace and Summary Sheet

jdswift

I'm having some difficulty in associating a date cell created by a blueprint program with the project it creates. Is there a way to change this that I'm unaware of? 

 

Setup Summary: 

I have a blueprint that pulls from an intake sheet to create a new project workspace. The intake sheet contains a Start Date and a Go Live date. The blueprint templates include a Metadata/Metrics sheet where I have house all of the metrics/data used to populate reports, dashboards, and some cells on the timeline of the resultant workspace. I'm seeing that the blueprint is taking the Start Date and Go Live date from the intake sheet and placing them in the metrics sheet in the *Value column; the column immediately to the right of the Primary, as expected. 

The same dates (at the intersection of the Item and *Value column in the metrics sheet) are also added to the timeline at the default intersection of Task Name and Status.  

In the Timeline image there is a Start Date and End Date Column. The intersections highlighted yellow feed back the Metadata/Metrics Sheet at the yellow highlighted intersections (Item and *Date Column) in the Metadata/Metrics sheet image above. 

 

The Problem:  

The dates in the Metadata/Metrics sheet at the intersection of Item and *Value also feed to the Portfolio Summary Sheet. I want the dates in the Portfolio Summary sheet to be connected to/update with the manually input dates found in the yellow highlighted cells of the timeline so that my Portfolio Summary sheet is always displaying the correct dates. Is there a way to do this?  

AravindGP

Hi @jdswift

The connection from Metadata to Portfolio Summary is based on the name of the field. If you would like to keep both dates (from intake and the project), I suggest that you rename the fields in your intake to something like Baseline Start Date, Baseline Go Live Date and add two additional rows in your metadata sheet that picks up the start date and go live date from the project. In your blueprint summary configuration, you can only have the start date and go live data configured to be pushed through for the portfolio summary sheet.

AravindGP

Hi @jdswift

The connection from Metadata to Portfolio Summary is based on the name of the field. If you would like to keep both dates (from intake and the project), I suggest that you rename the fields in your intake to something like Baseline Start Date, Baseline Go Live Date and add two additional rows in your metadata sheet that picks up the start date and go live date from the project. In your blueprint summary configuration, you can only have the start date and go live data configured to be pushed through for the portfolio summary sheet.

jdswift

Thank you @AravindGP!

I have a few other questions:

I added a "Baseline Start Date" and "Baseline Go Live Date" to my intake sheet and metrics sheet, then changed the blueprint summary configuration so that these dates are pushed to the summary sheet and metrics sheet. This opened the fields for Start Date and Go Live Date in the summary sheet. Thank you for your insight.

My new questions are:

1: In my metrics sheet there are some fields that were created by someone else that I am unsure of their purpose. In the image below you'll see several instances of <<TEXT>> in the *Value column. I believe these are just placeholders but does surrounding the text with double arrows tell Smartsheet to do something or indicate something? Such as how surrounding something in curly brackets { } indicates another sheet.

2: I've linked Start Date, Go Live Date, and End Date from the timeline to the metrics sheet at the respective row intersection with the *Date column (highlighted yellow in the image below). These are updating as I update the dates in the timeline - as they should. The issue is, I want those dates to update the summary sheet but, it appears the summary sheet is looking at the intersection with the *Value column and displays the text seen in that intersection (image below).

I could just change the cell link to insert the dates at the intersection of the Item and *Value column but, is there a better way? Or, is there a way to direct the summary sheet to look at particular intersections that I'm unaware of?

I have this same issue with a symbol set. In the image below the *Symbol (RAG) column has a symbol at the intersection of Current Project Health and Current Project Trend. These are linked to the timeline and update accordingly. I want these to update on my summary sheet too. Again, my summary sheet is looking at the intersection of the Item and *Value columns so I entered the formula: =[*Symbol (RAG)]@row at the intersection of Current Project Health/Trend and *Value in order to pull data into the cell the summary is looking at. It turns the symbol into text and seems to work just fine but again, is there a better way?

AravindGP

Hi @jdswift

Here are the answers

1. Smartsheet doesn't do anything for these brackets <<>>. It might have been added while building the project dashboard to show some value to begin with. These are usually added from Intake to the metadata sheet as part of your profile data selection in the blueprint configuration.
2. There are two ways in which you can send the dates to Portfolio Summary. One is to just do =[*Date]@row in these rows under the *Value column and it will get auto pushed. The better way is to add Start Date, Go Live Date, and End Date in your Profile data selection and select the Target column as the *Date column and add the source as Date as shown in this image and ensure to have the required field unchecked
3. The same logic as date applies to your symbol set. You can either do =[*Symbol (RAG)]@row in the *Value column or add it to your profile data and keep it unchecked with *Symbol column selected as the Target column. Smartsheet will know where to pick up the value to push to your Portfolio Summary this way.

AravindGP

Here's the screenshot as an example

jdswift

@AravindGP

That worked perfectly for the dates. Thank you again!

For the symbol set, I'm unsure which value option to select, it seems dropdown requires that I enter further values as seen in the images below. This isn't a roadblock but I'm curious.

Lastly, for my own understanding, can you explain why it is better to add Start Date, Go Live Date, and End Date in your Profile data selection and select the Target column as the *Date column and add the source as Date instead of just using a formula to pull the information over to the *Value column?

Also, how does Smartsheet know to associate the selection of "Date" with where to get the information? In other words, we selected Date as the source, but there isn't a column or cell specifically called date so, how does it know? Is it looking at the item row and then looking for an intersection with a Date column type?

Thank you for your assistance. You have been a great help!

AravindGP

Hi @jdswift

You can select Dropdown as the field type but don't edit the values. Just select the Target column.

The reason to select the Target column instead of the formula is, if you are using DD/MM/YY as your date format, when you pull the value from the column to a text/number column, Smartsheet will change it to MM/DD/YY format. Smartsheet will by default pick the value from the column immediately to the right of the Primary column in your metadata sheet to push to the Portfolio Summary sheet you select. We let Smartsheet know where to pick the value from when we select the Target Column. It uses that as the basis to pick the value.