Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
mskusace
Creator
Creator

Comma Delimiting Multiple Fields into One Field based on Association and Status

I have 2 tables that are associated with the ID column. One table has project data and the other table has the users associated with the project. Each project has a status which indicates who has current ownership of the project. I would like a way to show in the table everyone in a certain role associated with the project based on the status. There can be multiple people in the same role, so I would need multiple people to show in one field delimited by a comma.

Table 1

IDProjectDate StartedStatus
1Project12019-03-15Initiated
2Project22018-08-23Reviewed
3Project32019-02-01Final

 

Table 2

IDUser RoleUserE-mail
1AdminUser1user1@user1.com
1Project ManagerUser2user2@user2.com
1AnalystUser3user3@user3.com
1AnalystUser5user5@user5.com
2Project ManagerUser2user2@user2.com
2AnalystUser4user4@user4.com
3Project ManagerUser6user6@user6.com
3AnalystUser3user3@user3.com
3AdminUser7user7@user7.com

 

Desired Result

IDProjectDate StartedStatusUserE-mail
1Project12019-03-15InitiatedUser3, User5user3@user3.com, user5@user5.com
2Project22018-08-23Reviewed(Missing)(Missing)
3Project32019-02-01FinalUser6user6@user6.com

 

Logic

If Status = Initiated, show Analyst
If Status = Reviewed, show Admin
If Status = Final, show Project Manager

 

If Status = Initiated and there is no Analyst associated with the project, fill in the field with "(Missing)". This is an error catch for me.

Any guidance would be greatly appreciated.

Labels (1)
1 Solution

Accepted Solutions
shubham_singh
Partner - Creator II
Partner - Creator II

Try this:

Pick(

Match(Status,'Initiated','Reviewed','Final'),

Concat({<[User Role]={'Analyst'}>}User,','),

Concat({<[User Role]={'Admin'}>}User,','),

Concat({<[User Role]={'Project Manager'}>}User,',')

)

View solution in original post

4 Replies
shubham_singh
Partner - Creator II
Partner - Creator II

Try this:

Pick(

Match(Status,'Initiated','Reviewed','Final'),

Concat({<[User Role]={'Analyst'}>}User,','),

Concat({<[User Role]={'Admin'}>}User,','),

Concat({<[User Role]={'Project Manager'}>}User,',')

)

mskusace
Creator
Creator
Author

I can't seem to get it to work. I double checked all spelling, capitalization, and field names also. It says "Invalid Dimension".

UPDATE

I got it to work as a measure. Is there a way to make that a searchable field? It doesn't appear to be at the moment.

Thanks for the reply and help!

shubham_singh
Partner - Creator II
Partner - Creator II

You will have to create this field in script to make it searchable.

mskusace
Creator
Creator
Author

Got it. Would I just use the same code you provided for the load script and put an "As [Name_of_field]" there? Do I need to change any of the syntax? I've only done basic editing in the data load editor. I appreciate your help!