Multi-Line key, need calc to 'extract' related values.

[Grundly]

This could be one of those easy ones..?

Two tables.. I have a text field in the Product table, called mlfk_DepartmentIDs, which contains paragraph-delimited numerical Department IDs. That field is then used as the left side predicate of a relationship to the Department table, into the Department table's pk_DepartmentID field (its primary key field). Department table also has a text field called DepartmentName.

From the perspective of Product, I want to show a comma separated list of all DepartmentNames, for each department ID in that multi-line key field, mlfk_DepartmentIDs.

I am familiar with using a Join table, but I want to see if this can be solved with just a calculation of some sort. -I don't want to add more clutter to the graph just to benefit this one necessity. -There won't be more than a maximum 10-12 possible IDs, well below that 125-limit I read about (which probably doesn't matter here anyway).

 

Thanks!

[LaRetta]

Hi!

In Product table, create calculation (result is text) with:

Substitute ( List ( departments::departmentName ) ; ¶ ; ", " )

 

Edited October 30, 2015 by LaRetta

[Grundly]

Oh how it comes so easily for you. It's perfect. Thank you!