Find document number from multiple numbers where a measure is 0

I connected 2 universes and need data combined.
But as usual I run into the inevitable #multiple value issues…

I have 1 universe that gives me open PO numbers and also holds the quantity received measure.

Now the other universe is my main universe and there I want to add the PO field for Material.
So the main universe holds material number and now I want to add the PO number that is still open for that material (that would normally be only 1 PO).

Both Universes are connected thru Material.
So I brought in PO as a detail on material with :
“PO_D” =[Purchasing Document Number Display]
But this gives multivalue which is logical because there are multiple PO’s for that 1 material.

However in the second universe there is also a field called Open STO which shows Y or N when a PO is still open. But it is not always correct because it will also show N when still no quantity is received but the system delivery date has passed.
That is where the received quantity comes in.

The logic for “Open Order” =If([Open STO]=“N” And [Quantity of Goods Received]<>0) Then “N” Else “Y”

Now I have a material that has 7 PO’s and for all those lines the Open STO is N.
But 1 lines has a received quantity of 0.
So that is the line that I need. Better said I need the PO number of that line to come into the aggregated report.

So the big question is how can I get the po number of that 1 line that has Open STO and received quantity 0 (both field from query 2) into the main report which is mostly based on query 1?

I take it nobody knows?
Maybe I did not explain correct? Or just can’t be done?

tl;dr :wink:

give us data-examples or screens instead of writing a novel

Why not filter out the data you don’t need at the query level?

Well I have no access to the query level if that is what you mean @dtolley .

Or do you mean in the prompts of that query exclude things?
Like filter out all PO’s who’s Quantity of goods received is <> 0?
Not sure if that would fix my problem complete.

The PO number is in Query 2 and the rest in Query 1.
So still need some sort of VLOOKUP that pulls in the PO number from Query 2 into the line of the Material Number in Query 1.