Pulling and sorting value of Table-A of column-1 to Table-B Column 1

AppSheet AppSheet Q&A
1

Hello Friends,

I have Two tables as per followings:

Table 1:-

Field Name: ID, Project Name, Depth(ENUM)

Table 2:-

ID, Sl_No., Depth

I am Pulling the value of Table 2 Depth column in Table 1 Depth Column.

Select(Table2([Depth],True)

now i need to Orderby the Table2 depth column based on Sl_No column and i am applying below syntax:

Table1–> Depth–> ValidIf–> OrderBy(Select(Table2([Depth],True),[Sl_No])

It gives me error that “Unable to find column ‘Sl_No’, did you mean ‘ID’?”. I know this error is because Sl_No column is not available in Table 1, hence this error is coming. Please let me know how to solve this as sorting using Sl_No column is mandatory.

Thank You in Advance.

Rohit Gaur

2

OrderBy(Table2[Depth],[Sl_No])

1 Like
3

Thanks for your response @TeeSee1 .

when i am applying this syntax in Table 1 on Column Name Depth[type-Enum]. it says “Unable to find column ‘Sl_No’, did you mean ‘ID’?”

the tricky part is that i am pulling the value from other table and orderby that on another column.

please guide.

4

This expression is looking at table 2. As long as you have [Sl_No] in table 2, the expression should work.

https://support.google.com/appsheet/answer/10107362?hl=en

1 Like
5

Hello @TeeSee1 ,

the expression is triggering on Table 1 on column Depth. Select expression is completing the job for table2. On top of it, we are applying Orderby experssion and he is looking SL_No in table 1.

this is the whole issue that i am unable to direct orderby to table2 to sort based on Sl_No.

Orderby: By default consider the columns of the same table in which he is being applied.

Hope you would have got the issue now.

Please guide.

Thanks,

Rohit Gaur

6

Hello @TeeSee1 :

you are absolutely correct. I was searching further more on the web and found your advice on below post:

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Filter-with-orderby/td-p/508377

after reading this article i found my issue is also same and i narrow down my issue:

Syntax given in Article:

ORDERBY(SELECT(Customers[Customer ID], ([Country] = “Brazil”)), [Customer Name])

Note: Customer ID is ID column in the table.

My Syntax is :

ORDERBY(SELECT(CHART[Depth], ([Chart_HD_ID] = [_THISROW].[ID])), [ROD])

Now in my case since [Depth] is not the “ID” column in the table. It is giving me error. The ID column name is ID in the table. If, I replace [Depth] with [ID] the syntax become right and error is solved.

But my requirement is to sort the depth using ROD which is Serial Number. Please help to solve this with your knowledge set.

your advice would be highly appreciated.

Thanks,

Rohit Gaur

7

Sorry, I was completely wrong..

You should use this technique described here - Sorted by Another Column (at least this is the only way I can think of )

  • Create a VC in Table 1 with an expression

    ORDERBY(CHART[id],[ROD]) //You can modify CHART[id] by adding a filter condition as required

  • Then in the Valid-IF or your Depth in Table 1 set

    [VC created above][Depth]​

This does not look very elegant but it should work

1 Like
8

Hello @TeeSee1 , @Steve ,

Based on above suggestion i have now created the syntax like this:

CONCATENATE((SELECT(Trn_HDD_Register_DT[Depth],
IN([ID],
ORDERBY(
FILTER(“Trn_HDD_Register_DT”,([Trn_HDD_Register_HD_ID]=[_THISROW].[Trn_HDD_Register_HD_ID])),[Sl_No])))),“,”,[Depth])

As per order by logic the result output should be coming :- 1.25,1.50,1.75,2

but the result is coming :- 1.5 , 1.25 , 2 , 1.75

i further analyze and found the result is displayed based on the _RowNumber as per record saved in appsheet. below snapshot is for reference:

RohitGaur_0-1676619027268.png
RohitGaur_0-1676619027268.png1253×783 41 KB

no matter what my order by statement says to order based on SL_No.

Any suggestion, innovative Idea would be highly appreciated.

This is the last leg of the issue :-).

Thanks,

Rohit Gaur

9

I am not sure if you are trying to sort by the values of [Sl_No] or [depth].

The fact you want the list to look like “1.25,1.50,1.75,2” indicates you want it sorted by [depth] but you are also saying it should be sorted by [SI_No].

I am simply confused.

1 Like