FINDITEM of first 3 character in List

Options
CHSJ
CHSJ Member, ALL USERS Posts: 5 Occasional Contributor
in Planning & Modeling

Hi,

I have a module with say a Product dimension consisting of 3 Letter product codes, e.g. APP, ORG, PEA. I would like to find these items in a list where the Names in the List are 5 Letters e.g. APPUS, ORGUS, PEAUS. How am I able to lookup APP against the list of APPUS (first 3 letter) and return ÀPPUS?

Tagged:

Comments

  • CHSJ
    CHSJ Member, ALL USERS Posts: 5 Occasional Contributor

    Hi Tony,

     

    Thanks for the quick response. How about if it is the other way around? I.e. List 1 having 5 characters?

     

    Thanks much!

  • TonyViGrand
    TonyViGrand Member, ALL USERS, Partner, Certified Model Builder Posts: 5 Contributor
    If that is the case, then its easy. You can put the formula as
    finditem('List 2', left(name(item('List 1')),3))

    Hope this helps.
  • TonyViGrand
    TonyViGrand Member, ALL USERS, Partner, Certified Model Builder Posts: 5 Contributor
    You can put the above formula in the target module line item itself. You don't need the mapping module I created.
  • CHSJ
    CHSJ Member, ALL USERS Posts: 5 Occasional Contributor

    Hi Tony,

     

    I am probably doing it wrong as I got an error. Below is a mock up of List 1, List 2 and the Module.

    What is the formula to get the desired result below? Thank you.

     

    CHSJ_0-1644691740622.png

     

  • TonyViGrand
    TonyViGrand Member, ALL USERS, Partner, Certified Model Builder Posts: 5 Contributor

    Hi @CHSJ 

     

    I will give below my solution once again in steps based on the mock up you have shared.

     

    1) First create another module and lets name it as "StagingModule" dimensioned by List 1.

    2) Create two line items and lets name it as "LineItem1 & "LineItem2".

    3) The format of LineItem1 will be List 1 formatted line item.

    4) The format of lineItem2 will be List 2 formatted line item.

    5) In LineItem1, the formula should be ITEM('List 1')

    6) In LineItem2, the formula should be FINDITEM('List 2',left(name(item('List 1')),3)).

    7) Now back to your original module showed in the mock where you want the result. You can put the formula in the product line item as 'StagingModule'.'LineItem1'[FIRSTNONBLANK:'StagingModule'.'LineItem2'].

     

    See to that when you input the formula you put the brackets correctly otherwise you might get the formula invalid error. If any error, please do share the error screenshots and blueprint view of the module and formula you have put.

     

    Hope this helps.

     

    Regards,

    Tony

  • CHSJ
    CHSJ Member, ALL USERS Posts: 5 Occasional Contributor
    Hi Tony, will try this out. Thank you.

Categories