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

[lynchk21]

For some clarification - you want an expression on the “Roast Recipes” table get the [shipment id] with the most recent [date received] from the “shipment received” table. This [shipment id] must have a      [Shipping Cost/Unit]>0 and in its [origin] column a value that is found in [_thisRow] in the [origin 1]column on the “Roast Recipies” table. You then want to use this [shipment id] to look up the [shipping cost/unit]

Am I understanding correctly? 

[Ryan_Hagler]

The Lookup formula is actually returning the [Shipping Cost/Unit] from the Shipment Received table which is what I want. The Maxrow formula is telling the Lookup formula to return the row key of the latest [Date Received].

In addition there are two filters setup within the maxrow formula. The second, the easy one that you mentioned, is [Shipping Cost/Unit]>0. The first seems to be where the issue is. "in([_THISROW].[origin 1],[Related Coffee Inventory Purchaseds][Origin])". It's intention is to look for origin 1 in the Related Coffee Inventory data. Which it finds but not correctly.

[TeeSee1]

I did a quick test with very inconsistent results. I believe your expression is correct and should give you the right result.

However what happened to me was that it behaved so erratically that without changing anything it seemed to work one minute and the next minute it did not work. I also get inconsistent results among the test console, desktop view and mobile device.

Not sure what you should do except to contact Google support and see what they say. I myself am very worried about this inconsistent behavior. It makes it difficult for me to place any kind of confidence in the platform..

[TeeSee1]

Since the previous test was done inside an old App with a lot of "garbage" I wanted to make sure that I was testing it properly and created a new APP. The result is the same and IN("val", [Related ***][col]) syntax is unstable.

Here is (at least) one way to accomplish the result.

Three tables - MASTER, PARENT, CHILD

Table Definition - MASTER

//TARGET_PARENT
MAXROW("PARENT","date_col",IN([_THISROW],[Related CHILDs][ref_master]))

// TARGET_PARENT2
MAXROW("PARENT","date_col",IN([id_p],SELECT(CHILD[ref_p],[ref_master]=[_THISROW], TRUE)))

//T_VAL
LOOKUP(
 MAXROW("PARENT","date_col",IN([id_p],SELECT(CHILD[ref_p],[ref_master]=[_THISROW], TRUE))), 
 "PARENT",
 "id_p",
 "val_to_retrieve"
)

Resulting View of MASTER

I hope Google looks at this and "fixes" it so the use of List Dereference is safe or at least makes it clear where it can be used safely. Perhaps I may be using it in the wrong way but if so, it is not obvious from any of the documentation I have read.

I would also appreciate it if any of the Community members submit any comments on this. I would like to continue to use this platform and to do so, making it 'right' is critical.