formula (Max)

[Yokuinin]

I would like to get the data from the child table.

Parent  ...  Order table

Child ... Detail table

Here is the process what I would like to do.

1. "ID_order" input in the Parent table.

2. "ID_detail" input based on the Parent and input the detail.

3. Return the record to the Parent table.

There are 3 kinds of data I want to have.

1st ; The biggest number among the same ID_order

2nd ; The latest date among the same ID_Order

3rd ; The biggest number of Row among the same ID_order

How should I describe the formula?

Can I get the record in the Parent?

Attached images are  my ideal target.

[Suvrutt_Gurjar · Accepted solution]

You will have a virtual column called something like [Related Details] in the parent Order table.

You could create three VCs with expressions something like 

1st ; The biggest number among the same ID_order

MAX([Related Details][Size])

2nd ; The latest date among the same ID_Order

MAX([Related Details][Delivery Date])

3rd ; The biggest number of Row among the same ID_order

MAX([Related Details][_ROWNUMBER])

https://help.appsheet.com/en/articles/2357296-max

https://help.appsheet.com/en/articles/1090811-dereference-expressions

If you wish to have these columns values stored in real columns in the parent table, please evaluate reference actions.

https://www.appsheet.com/templates/This-app-shows-how-to-use-reference-actions?appGuidString=e76d2e7...

[Yokuinin]

Thank you for your commet!

1st and 2nd is Good!

But 3rd is not work well.

(As you know) I can get the number, in this case, "4",

I would like to have "Tshirts".

How should I modify the formula?

[Suvrutt_Gurjar]

Oh okay. You had written "3rd ; The biggest number of Row among the same ID_order".

Sorry , I misunderstood.

You can do so in two steps. with two columns but expressions will be sync time expensive. Please try something like below

A) Column 1 Name [MaxItemRow]

MAXROW("Detail", "_RowNumber", [Id_Order]=[_THISROW].[Id_Order])

B) Column 2 [Item]

[MaxItemRow].[Item]

[Yokuinin]

Thank you.

I am sorry that my first post is not covered my intention well.

According your advice, I had modified the formula.

But there is still errors.

3rd-A) I can get the ID Key of the MAX Row.

But 3rd-B) does not get the text.

I attached the error massage.I had added extra examples.

[SkrOYC]

Try to not use column names that resemble expression names.

[Yokuinin]

Thank you for your help.

Unfortunately I can not understand "resemble expression names"😥

https://www.appsheet.com/Template/mobilepreview?appId=d0467978-6522-476c-8a39-4b4c9b9aae32

[SkrOYC]

You named a column "MaxRow(VC)".

MAXROW() is an expression, it confused AppSheet.

Reserved word - Wikipedia

[Yokuinin]

Oh!! I've understood!

The result is perfect!!

Thank you so much!

@Joseph_Seddik   Thank you too!

[Joseph_Seddik]

Further to @SkrOYC's advice to safely name your columns, in the Display Name field of the column you can put whatever name you like. 

[Suvrutt_Gurjar]

As @SkrOYC  suggested, please note that MAXROW() is a function in AppSheet. You should also not select table or column or slice names that resemble AppSheet function names  such as SELECT() or FILTER() or LOOKUP() etc. AppSheet is generally tolerant most of the times, but at times it can be an issue. I had very specifically suggested a different name for the column with MAXROW() expression.

[Yokuinin]

Thank you for kind suggestion.

From now, I need to pay attention to the column name.

[Yokuinin]

Here is for my understanding.

1st	The biggest number	MAX([Related details][size])
2nd	The latest date	MAX([Related Details][Delivery Date])
3rd - 1	The Item which has biggest number of Row	MAXROW("Detail", "_RowNumber", [Id_Order]=[_THISROW].[Id_Order])
3rd -2		[VC_MaxItemRow].[Item]