Lookup Maxrow with related list not functioning correctly

[Ryan_Hagler]

Aloha everyone!

I am having issues with getting the correct result from this formula. It is returning the wrong max "Date Received" therefore the wrong "Shipment ID" etc. The interesting thing that I've noticed is that when I place the [_thisrow].[Origin 1] that I am looking for at the top of the [Related Coffee Inventory Purchaseds] list in its data table it then returns the correct max "Date Received" row. It makes me wonder if the formula is not looking past the first entry on the Related list.

Another issue is that the "Shipping Cost/Unit" that I am really after is being returned incorrectly regardless. The number it is returning is found nowhere on the Shipment Received Table. 

What am I missing here? Mahalo!

Ryan

Table 1 = Roast Recipes

Table 2 = Shipment Received

Table 3 = Coffee Inventory Purchased

Formula:

lookup(

  maxrow("Shipment Received","Date Received",

    and(in([_THISROW].[origin 1],[Related Coffee Inventory Purchaseds][Origin]),
      [Shipping Cost/Unit]>0)
  ),"Shipment Received","Shipment ID","Shipping Cost/Unit"
)

Roast Recipes TableShipment Received TableCoffee Inventory Purchased TableShipment Received Data 1Shipment Received Data 2Formula ResultRelated Coffee Origin Data