FINDITEM of first 3 character in List

CHSJ
Occasional Contributor

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?

8 REPLIES 8
TonyViGrand
Contributor

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

CHSJ
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
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
Contributor

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

Hi Tony, will try this out. Thank you.
MarkWarren
Expert

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.