FINDITEM of first 3 character in List

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:

Answers

  • Hi @CHSJ 

     

    Please refer the below screenshots, the formulas are available in screenshots.

     

    Note: The list used in target module is "List 1" and the list used in Mapping module is "List 2".

     

    TonyViGrand_0-1644666473432.png

    TonyViGrand_1-1644666514809.png

     

    Hope this helps.

     

    Regards,

    Tony

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

  • 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.
  • You can put the above formula in the target module line item itself. You don't need the mapping module I created.
  • 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

     

  • 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

  • Hi Tony, will try this out. Thank you.
  • If you can, put the 3 letter part as the code of the list, that way the FINDITEM would work with either the 5 or 3 letter variants.