Need to lookup multiple items from another module

I have a module with PO numbers 1 line per PO, with space for 4 product codes

The source list is from a simple PO list, with no details, i can then get the details of shipping etc using a lookup Inbound Stock Transactions.P_Forwarder[LOOKUP: PO] etc

But the source data has mutiple lines, one for each product, which i also need (and the user wishes it to be all on one line)

I want to lookup each item and place the Product code in the 4 columns (as above in blue)
How can i lookup multiple lines from the second module?

thanks


Tagged:

Answers

  • Hi @DeveloperCYT

    You can do a workaround as mentioned below:

    1. In your source data module, create one line items to calculate rank based on PO Grouping. That way you'd be able to generate 1,2,3,4 in that line item.
    2. Create 4 different line items for product codes, use formula:
    3. 1st line item: If rank=1 then Product code else blank, 2nd line item: If rank=2 then Product code else blank, etc.. till 4th line item
    4. Finally use these 4 different line items as lookup in your target module

    Cheers!!

  • Hi,
    I had tried something like that but that still doesnt work for a lookup as it will only lookup the first instance of the PO , and product 2, 3 and 4 will be still be blank.

  • Hi @DeveloperCYT

    In that case, create a PO formatted line item in source module and use finditem in that.

    Then instead of lookup use firstnonblank on these product codes.

    That should work, Cheers!!

  • Sorry i am not following that
    I already have a line item that is PO formatted, thats how i lookup the other details.
    How do i use FirstNonBlank instead of a Lookup?
    thanks or the help

  • Hi @DeveloperCYT

    In this source module, if PO line item is list formatted already then, in your target module , you need to use formula Inbound Stock Transactions.'Product code 1'[firstnonblank: PO]

    Cheers!!

  • DeveloperCYT
    edited April 2023

    Sorry that gives me an error, but do appreciate the help

    "Dimension of mapping used for aggregation doesn't match any dimension of the source


  • Hi @DeveloperCYT

    Not sure what is the issue that is happening, but one solution i can look at from here is

    You can create 4 different PO line items as well, in similar way with if condition as you did with Product codes and then use different PO line items in each lookup formula

    Thanks!!

  • Hello @DeveloperCYT ,

    Can I understand that this will be your target, or is it the other way around?

    Cheers!

    Kind regards,

    Bruno Rodriguez

  • Other way round that the source data (some of it)
    Lookups only bring in the first line.

  • Hello @Himanshu1998
    You can't do that as the Source Module is based on list and any lookup has to refer to that list surely.
    you can't do [LOOKUP:PO1] and then on another line items [LOOKUP:PO2] as thats just not possible.

  • Hi @DeveloperCYT Pardon I was not clear

    I meant create 4 line items , PO1, PO2, PO3 & PO4 , all will have same list format as PO line item has. In those 4 line items you can use formula: If rank = 1 then PO else blank, etc.

    Then in your target module you can use Source.product code 1[LOOKUP: PO1]

  • Source.product code 1[LOOKUP: PO1] cannot be done, you can't LOOKUP a line item, you can only lookup using a PO line item in the target module, and you cant create 4 PO line items in the Target module either as we cant have ranking in there. So this solution will not work.

  • Hi @DeveloperCYT pardon, please ignore my answer. Didn't see what i wrote

  • Hello @DeveloperCYT ,

    Ok, Let's try the following. I think it should work:

    • Create the following module:
    • From each of the line items in the other module, if Rank = 1 AND PO (source) = PO target then Product Code ELSE BLANK
      • It could require some mapping based on any other potential dimensions. Note that the Rank = 1 will have to be = 2, 3, and 4 for the other 3 items.

    I hope that makes sense.

    Cheers!

    Kind regards,

    Bruno Rodriguez

  • The second module only has 1 line per PO, and therefore no ranking

    "From each of the line items in the other module, if Rank = 1 AND PO (source) = PO target then Product Code ELSE BLANK"

    This cannot be done, and you cant lookup the rank from your module as it will only ever find the first instance

  • DeveloperCYT
    edited April 2023

    I solved this by exporting the PO concatenated with the rank to a list with a product field.
    then lookup PO&1 PO&2 etc from the list