Custom Field ID to Name in Project Report

[StephenSc1]

I am attempting to pull in the name of the person who requested an issue into a project report. Current Steps Taken: Request/Issue custom form has calculated field with calculation as Entered By ID Project custom form has calculated field with calculation as the name of the issue field (to pull in the same ID) When I reference the project custom field in my project report it is returning the user string (like: 596f7db901922d6fa8077e409a5044bb) How can I convert the ID string back into the name of a user? Can I accomplish this by modifying the initial calculated field? I've tried changing it to Entered By ID.Name with no luck. I have looked into some of the options in text mode, but the material I found doesn't seem to address converting ID to Name for custom fields. My field in text mode looks like: displayname= linkedname=direct namekey=Request Entered By querysort=DE:Request Entered By textmode=true valuefield=Request Entered By valueformat=customDataLabelsAsString Thanks in advance for any and all suggestions! Stephen Schmidt The Home Depot Creative Services

[NRYNR]

Stephen, I use the same calculated field on the issue and the project form, but slightly adjust the calculation. Here's how I handle it. Keep in mind, this will only update on the project during the convert activity.

[StephenSc1]

Thank you Narayan, exactly what I was looking for. Appreciate the help! Stephen Schmidt The Home Depot Creative Services

[JasonSe]

Hey Stephen and Narayan, This is very helpful, as I have a similar issue I have not been able to find any help with. I would like to pass the name of a creative change/issue requestor through to the WF DAM, however there is not a system field that is mappable that contains the name, only the ID. I have tried to pass this information by mapping the Primary Contact and project Sponsor fields (both of which have the requestor's name), but there are only ID fields for each, no names. I am thinking I will have to create a calculated field on a custom form to capture that information, and it seems like your solution above would work. However, I am very inexperienced at text mode, so I was wondering if you would be able to share a couple more screengrabs of how exactly you have this setup (and what goes in each text mode for both the request/issue custom form and the project custom form). If I can get the name to appear as a custom form field on the project, I can then map it to a corresponding field within DAM. Thank you very much for any help you can offer! Jason Semall Sr Creative Ops Mgr Conversant

[JasonSe]

Screengrabs of the fields I referenced. Jason Semall Sr Creative Ops Mgr Conversant

[NRYNR]

Hi Jason, I'm unfamiliar with the DAM and what you are trying to do there. What you've shown me there is new to me. Wish I could better help!

[JasonSe]

Okay, no problem. Thank you for taking the time to read it! Jason Semall Sr Creative Ops Mgr Conversant

[pco88]

@Jason Semall , I'm assuming that the reason you're pushing this info to the DAM is that there are documents/assets attached with the issue/request. So whoever attached the document to the issue is also the owner of the issue. Wouldn't owner:name be the map to a custom field on the DAM? Polly Co

[JasonSe]

Thanks Polly! The document(s) that I am passing to DAM from WF are posted to the project that the issue/request is converted to, by a designer. So, if they are the ones pushing their document from the Project to the DAM, I think they would pass through as the "owner" of the document, no? I am trying to find a way to capture and pass through the name of the person who created the issue/request that is then converted to the project that the final documents are then attached and passed to DAM from. That person is always the Primary Contact on the issue/request, but that system field doesn't seem to pass through when converted to a project, and only appears to be an ID when I try to map to DAM. It is also part of our process that when issues/requests are converted to projects, the person who requested it is always made the Project Sponsor. This also only returns an ID option rather than name when trying to map to DAM. I was hoping there was a way to create a calculated field that converts an ID to a name within a project, like Narayan and Stephen were describing for a project report, that I could use to pass to the DAM. Thank you again for your help! Jason Semall Sr Creative Ops Mgr Conversant

[pco88]

I'm confused on the workflow. Are the designers submitting the request? If so, their names are going to be attached to the document and so can be mapped to send to the DAM. But that's not what it seems to be on your last message. So whoever has attached the asset on the request, their names would still show up as the document owners. Converted to a project, the asset is still owned by the person who attached it to the request and that's something mappable to the DAM. Otherwise, and this might not be something that is available to you - we have multiple fields we want mapped from the issue itself that we want to get sent to the DAM. So we made a custom form that can be attached to the document. The custom form itself is automatically attached to the document/asset and copies over all issue data fields to the custom form (done thru coding). All document fields are mapped to the fields on the DAM. When we push the document to the DAM, it carries all the issue/document data. Polly Co

[JasonSe]

Hey Polly! Thanks again for your input! It is greatly appreciated! Let me step out our process so it is a bit clearer: Marketing Manager submits a request/issue via our queue Each request/issue has a custom form attached that contains the information captured Project Manager receives request and converts the request/issue to a project The request/issue custom form is then transferred to the project Designer is assigned tasks and completes the work. When complete, they post the final deliverables to the project under Documents tab. They then send the asset to the DAM When sending to the DAM, any of those fields from the custom form attached to the project can be passed to DAM attached to that asset I have been able to map all of the fields from the custom form that I need, except for the name of the Marketing Manager who made the initial request/issue - the system fields that would give me that information are both only ID's (Primary Contact ID and Project Sponsor ID), not names. I am trying to avoid having an open text custom field on my form where they have to enter their name, but that may be the only way. I am very interested to learn more about the solution you described. When you say "done thru coding," what exactly do you mean? What was your process for implementing? Jason Semall Sr Creative Ops Mgr Conversant

[pco88]

@Jason Semall , I see what you mean now. I think the best solution I can suggest is to put a calculated field on your issue/request and project form. On the issue form, put in the calculated field Label: Original Requester Calculation: Entered By.Name On the project form, put in the calculated field Label or retrieve the field: Original Requestor Calculation: IF(ISBLANK(Original Requester),"",Original Requester) Both are calculated fields so nothing for the PM or the user to enter. Then you can map the original requestor to the DAM. --- I've used Workfront to have designers (non contributor role in DAM) submit their images to be placed on the DAM by just one person (DAM admin). Requestors/Designers submit their requests with their assets to Workfront. The request form contains the info about the images/assets. I needed the same info attached to each image so that whoever is uploading to the DAM, the data is already mapped about the image and just needs to actually push the button to send to the DAM ( future state: automating this as well) but currently there's no way of auto attaching custom form to the image/asset. When I say code, I meant I wrote a program to tell the system to check all new requests for the DAM and attach a custom form to each asset and then push the contents of the request fields into the document form. The program also gives the DAM admin person manage rights to the asset so they can upload the asset to the DAM. Polly Co

[JasonSe]

Amazing, thanks so much, Polly! I have added those fields and will be testing it over then ext couple of weeks. I am also going to keep the solution you described in my back pocket in case the way I have designed our workflow doesn't end up working the way I'd like. Our designers will have contributor licenses and will be asked to push all their assets to DAM themselves, as we do not have a full time DAM admin. One more issue that I have just discovered that I would love to hear your thoughts on is the known limitation within mapping of only being able to send one value from WF to DAM for multiselect custom fields. Said another way, I have several custom fields on my forms that allow a user to select multiple values while filling out a request/issue. When I map those fields to DAM, only the last value selected is passed through (all others are ignored). Have you run into this same issue? Jason Semall Sr Creative Ops Mgr Conversant

[YannickSe]

Hello, Can we use calculated fields to pull the planned completion date of a task (or any other information from a task) in a project custom form? Thanks, Yannick Yannick Seifert WebMD

[pco88]

@Jason Semall , I haven't used checkboxes to map to the DAM but I've tested and saw what you meant by just one checkbox item is being sent forward. So... the best option I could think of is adding another calculated field for each checkbox field. For example, you have test3 as a checkbox field with dam1,dam2 and dam3 as the contents of the checkbox field. You then add a test4 calculated field. The calculated field contains this: CONCAT(IF(CONTAINS('dam1',test3),'dam1',''),IF(CONTAINS('dam2',test3),'dam2',''),IF(CONTAINS('dam3',test3),'dam3','')) Continue the pattern to as many contents of the checkbox field. If user has checked dam1 and dam2 on test3, test4 contents will be dam1dam2 (you can prettify this content later - I'm just giving you an example how you could possibly set up your calculated field). You can then map test4 to the DAM. Hope that helps. Polly Co

[pco88]

@Yannick Seifert , I haven't seen it done and I took a look at my calculated fields, I don't think it's possible but maybe somebody else in the forum can answer. Logically, the reason why I think it's not possible in the custom form is that usually the project custom form is set up upon project creation, which means tasks weren't set up yet which also meant that there should be a way to update the custom form (like Recalculate timeline or recalculate finance) but currently we don't have that. So no to the calculated field. However, another option that I have for things like this is thru API and some programming, don't know if that's an option available to you. But basically, it's like calculating fields you want outside of WF and then exporting the data back to an open field on your project custom form. Regards. Polly Co

[SarahNau]

Hi Yannick, You can pull task information on a project report by using collections in reports. There was a great presentation done at LEAP on this and I have attached the deck. Good luck! Sarah Nau Dominium

[JasonSe]

@Polly Co thank you so very much for all your help on this! This is seriously so much more helpful than going through actual support. They simply told me they knew that was an issue and that there was no fix scheduled. End of story. I greatly appreciate all the time you have taken to walk through this with me, as well as the help with text mode and calculated fields. That is definitely a weak spot of mine (for now anyway). You solution looks like it would solve my problem ! However, I am bit confused on exactly how the "pattern" would work beyond 3 options. I have fields with 15+ values, so I am having trouble visualizing how that would work in the formula you provided. Would it simply be CONCAT(IF(CONTAINS('damX',test3),'damX','') for as many options as I have? Jason Semall Sr Creative Ops Mgr Conversant

[pco88]

@Jason Semall , the pattern would be IF(CONTAINS('damx',test3),'damx','') So CONCAT(Pattern1,Pattern2,Pattern3,Pattern4,Pattern5) where pattern is IF(CONTAINS('checkbox option',checkboxfield),'checkbox option','') CONCAT( IF(CONTAINS('dam1',test3),'dam1','') , IF(CONTAINS('dam2',test3),'dam2','') , IF(CONTAINS('dam3',test3),'dam3','') , IF(CONTAINS('dam4',test3),'dam4',''), IF(CONTAINS('dam5',test3),'dam5','') ) Polly Co

[JasonSe]

Alright, thanks @Polly Co ! I'll start building those out! If there are spaces between the words of the values from the custom for field, should those be removed? What about "", &, and () in the values - should those be removed? Or should you enter your values into the calculation exactly as they are in the fields? Also, just wanted to confirm these calculated fields should be added on the Project custom form, not the creative change custom form. Jason Semall Sr Creative Ops Mgr Conversant

[YannickSe]

Thank you Sarah, I know how to pull a specific task info in a project report using the collection but I wonder if there is a way to use this type of calculation in a calculated field in a project custom form. This is the text mode I used to pull the planned completion date from a task where the milestone is called "production" in a project report: displayname=Prod. listdelimiter= listmethod=nested(tasks).lists textmode=true type=iterate valueexpression=IF(IN("Production",{milestone}.{name})=true,IF(ISBLANK({actualCompletionDate})=false,'‚òë',{plannedCompletionDate})) valueformat=HTML The main reason is that with the above you cannot use the data pulled for further manipulation like sorting your report/compare this column to another column/filer etc. For ex. in the report I used the code above, I would like to sort by the production planned completion date and filter "out" any project where the task production is completed and cannot do that. I kind of address that by inserting a tick box so it is easily identifiable but not ideal (especially the fact that projects are sorted randomly). Thanks, Yannick Yannick Seifert WebMD