I'm trying to create a function whereby I can return the parent value of a list item without using SELECT. I am trying to use LOOKUP & it is not working
I have a source module with a list (Machines - with a top level of Total Machines), a Mapping module with the Machines list and a line item returning the PARENT of that list item (Total Machines) & a target module with Machines as a list and a formula referencing the source module with a LOOKUP to the mapping module but is returning 0
It works if I use SELECT to the parent list item but I know this is far from best practice
If you change the set up with your Machines list then Lookup should work. Instead of having Total Machines as a top level item in your Machines list create Machines L1 with Total Machines as a list item and Machines L2 that has Machines L1 as a parent hierarchy. Now when you use [Lookup] with the same parent mapping module it should pull the total value:
The reason for it not being "best practice" is that the items in a list are likely to change over time. You do not want your formulas to stop working because a list was updated. However your Top Level item of a list is not likely to change and thus it is an exception to the best practice rule that Select is not smart to use.
I would in your case advise to use the SELECT method.