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?
You can do a workaround as mentioned below:
- 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.
- Create 4 different line items for product codes, use formula:
- 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
- Finally use these 4 different line items as lookup in your target module
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.0
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!!0
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 help0
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]
Sorry that gives me an error, but do appreciate the help
"Dimension of mapping used for aggregation doesn't match any dimension of the source0
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
Hello @DeveloperCYT ,
Can I understand that this will be your target, or is it the other way around?
Other way round that the source data (some of it)
Lookups only bring in the first line.0
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.0
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]0
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.0
Hi @DeveloperCYT pardon, please ignore my answer. Didn't see what i wrote0
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.
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 instance0
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 list0