This is probably straightforward, but I'm struggling with it...
I have a module that includes historical department names, which are combinations of numbers and text, i.e. "5100 Engineering". Sometimes the department names change, for example "5100 Engineering" might become "5100 Core Engineering".
I want to create a list formatted line item in the module that looks up the current department name based on the 4-digit number that proceeds the name.
The "active" department list contains a property that isolates the 4 digit string ("5100"), and the module contains a line item with the same string ("5100"). Both the list property and module line item are currently formatted as text.
Any tips of how to use those two values to find the list item?
I would recommend that you use the "Numbered List".
Please try the following:
1. A Numbered List called "Department" with "Code" populated.
2. A module with historical department name, let's call this module "Historical" and the relevant line item is "Department Name" with format = Text. This module uses "Department" as a list.
3. In the "Department" numbered list, create a Text Property called "Display", insert the formula Code(Item(Department)) &" "& Historical.Department Name in the "Display" property. Then make the name of this numbered list refer to "Display".
The advantage of the numbered list method is that you need not create a line item for every module that uses "Department" to reflect the current department name.
You can use the FINDITEM function to lookup the current department name. This requires a text value that is usually equal to the CODE in the list you are looking up the value in.
Format the line item that you want to show the Current department name in, and then use FINDITEM(currentdepartmentlist, CODEasTEXT).... This assumes that you are using the CODE field. If you aren't, I recommend that you do... it makes so many things easier. You can still include it in the NAME as part of the overall description.
(I agree with LipChean, that a numbered list is preferred when you have a code embedded in the alias/name field.)
Thanks for the responses @PaulRitner and @LipChean_Soh. I was actually able to solve my issue by adjusting the Code in the Department list to just include the numerical string, not the string + name... I'll think about / look into converting the list to a numbered list when I have more time! 🙂