Forum Discussion
Creating a column based on the string value from another table
Hello!
I'm having problems... among many, I am trying to create a column in a table that will collect the name from another table and add it into this column...
Table 1 (Acc/Act/Comm/Web)
| Purchase item text | Amount | WBS |
| ABC.1 item1 | 230 | 100001 |
| XYZ | 120 | 100003 |
| random item | 450 | 100003 |
| ABC.1 item2 | 40000 | 200001 |
| DEF item 1 | 200 | 100002 |
| other random item | 100 | 100002 |
I have table 2
(Activity List)
| Work Package | Activity | WBS | Capital WBS |
| ABC | ABC.1 | 100001 | 200001 |
| ABC | ABC.2 | 100001 | 200001 |
| ABC | UNKNOWN1 | 100001 | 200001 |
| DEF | DEF.1 | 100002 | 200002 |
| DEF | UNKNOWN2 | 100002 | 200002 |
| XYZ | XYZ.1 | 100003 | 200003 |
| XYZ | UNKNOWN3 | 100003 | 200003 |
I would like to use Activity column in the Activity List table to to search through the string in the Purchase Item Text column to determine which Activity the item relates to, if the Purchase Item Text doesn't have the Activity in the string then the result should be the Unknown# that corresponds with the WBS. I'm hoping for a result such as this:
| Purchase item text | Amount | WBS | Activity |
| ABC.1 item1 | 230 | 100001 | ABC.1 |
| XYZ.1 | 120 | 100003 | XYZ.1 |
| random item | 450 | 100003 | Unknown3 |
| ABC.1 item2 | 40000 | 200001 | ABC.1 |
| DEF.1 item 1 | 200 | 100002 | DEF.1 |
| other random item | 100 | 100002 | Unknown2 |
Please help!
