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!

 

Answers

  • 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

     

  •  

    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?

     

     

     

  • @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.

  • 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!

  • @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 )