Lookup off of code

KevinMcQuillen
Contributor

Lookup off of code

I need to be able to lookup off a unique opportunity code for line items in a module.  

Below is the code I need to look up off of, the list is titled MEC Opportunities

KevinMcQuillen_0-1618953085451.png

In this module, I need the line item Opportunity Code (list) to return the above codes while also being list formatted in order to use in lookup functions.

KevinMcQuillen_1-1618953427885.png

My train of thought is FINDITEM(Code(Item('MEC Opportunities')), Opportunity Name) however this has not been working.

Any help/corrections would be greatly appreciated, thanks!

 

5 REPLIES 5
ArunManickam
Master Anaplanner/Community Boss

Re: Lookup off of code

Hello @KevinMcQuillen ,

 

You are not following the syntax for finditem. The first parameter is the name of the list, second parameter should be code/name that you are trying to match.

 

ArunManickam_0-1618968663373.png

 

Let me know if it helps.

 

Thanks

Arun

 

KevinMcQuillen
Contributor

Re: Lookup off of code

 

This still does not return the code, however - it returns the list item names per below

KevinMcQuillen_0-1619011328860.png

KevinMcQuillen_1-1619011610934.png

I need the line item to be returning the unique codes from the list - while also keeping the line item list-formatted so that may utilize LOOKUP formulas off of the Code.

KevinMcQuillen_2-1619011731701.png

Is this functionality possible?

 

 

 

ChrisHeathcote
Community Boss

Re: Lookup off of code

@KevinMcQuillen 

It is not possible to return the code in a list formatted line item as a code as a text format.

Create a new line item, format it as TEXT and use CODE() on the list formatted line item to return the list code.

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA
KevinMcQuillen
Contributor

Re: Lookup off of code

Sorry I don't think I'm explaining this well.

The core of the issue is this - Management has requested a dashboard view of a list - 'MEC Opportunities' - which is  a specific set of opportunities with very specific naming conventions, therefore in the list I must use a display name.  This used to be a subset of a larger list titled - 'All Opportunities' - however given managements request on how they want to view the Grid, I need a new list with the correct Display Name.  There is a filter in a properties module which can be checked if an opportunity qualifies as MEC.  I used a saved view with the MEC filter applied to create the 'MEC Opportunities' list.

Then in the module, I have various line items which require a LOOKUP formula.  The module is dimensionalized by the 'MEC Opportunities' list.  In various other modules, I create an 'All Opportunities' list formatted line item which I then use for the LOOKUP functions, per below...

KevinMcQuillen_0-1619014327402.png

This example above is a module in which the functionality is working.  Below is the module I am seeking help on, b/c the same process is not working.

KevinMcQuillen_1-1619014449460.png

For the "SalesReq" and "Opportunity URL" line items, when I try to LOOKUP on the "Opportunity (list)"  - 'Properties: All Opportunities'.SalesReq[LOOKUP: 'Opportunity (list)'] - I get a mapping error.  My thought was that this is because of the different display name, the MEC Opportunities List and the All Opportunities list are un-able to match.  Therefore I was hoping to be able to LOOKUP on the code, however as you mention that is impossible.

So yes, creating a text-formatted line-item with the code is simple to do, but it does not exactly accomplish what I am needing.

Do you have any other work-arounds you can think of for this situation?

Thanks for all the help, it is much appreciated!

ChrisHeathcote
Community Boss

Re: Lookup off of code

@KevinMcQuillen 

You can use the list code to inform your FINDITEM to retrieve the list item.

You can also use ITEM if the module contains the list as a dimension.

ChrisHeathcote_0-1619018916416.png

ChrisHeathcote_2-1619018961868.png

 

What is not possible is using the text formatted display name in the FINDITEM function.

 

I you need to show the display name and then use this module to inform a mapping you will need to create two line items - one to show the display name and another to use in a mapping ( this can take the code and use FINDITEM to return the list item which can then be used in a mapping )

 

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA