I need help on Virtual Column

[Suresh_Subraman]

Hello,

I am new to appsheet and a non-technical guy, need some help on this query.

I have two tables one is “summary” with unique row values and “projectHistory” with duplicate row values. “CODE” is unique in both tables. I need just One column value from the latest record (Top 1) +(DATE DESC). Last “Budget”… I used many queries, LOOKUP(), ANY(SELECT()), For some reason, ORDERBY(FILTER) is not recognizing the column name to sort with… I am getting the values, but not the latest record for all columns only date column

VIRTUAL COL 1 >>>>>>> Not Accurate
(ANY(SORT(SELECT(projectHistory[budget], ([CODE] = [_THISROW], TRUE)), FALSE)))

VIRTUAL COL 2 >>>>>>> Not WORKING!
ANY(SELECT(
projectHistory[budget],
IN(
projectHistory[budget],
TOP( SORT( SELECT( projectHistory[Date],([_THISROW].[CODE] = [CODE])),TRUE), 1 )
)
)
)

TABLE 1
CODE | NAME | LATEST BUDGET
A1 | google | ???

TABLE 2
DATE | CODE | NAME | BUDGET
2020-07-14 | A1 | gle | 500
2020-12-31 | A1 | gle2 | 4500
2021-02-08 | A1 | gle2| 3220
2021-06-02 | A1 | gle3 | 1000

I want the latest budget value as “1000” in my TABLE 1

Kindly help on this query

[Lynn]

Hi @Suresh_Subramani
Maybe have a look at MAXROW()

[Suresh_Subraman]

It may not be always a number, I might need a text value as well.

[WillowMobileSys]

@Suresh_Subramani

Lynn:

Maybe have a look at MAXROW()

@Lynn has the best approach using MAXROW(). To elaborate, you will want to select the Budget value from the rows with code A1 with the maximum date.

The expression for [Latest Budget] column would be something like the below. It assumes that the [Code] value is already known. NOTE: you will need to replace column and table names to match your actual schema.

ANY(SELECT(TABLE 2[BUDGET], [Key_Column] = MAXROW("TABLE 2", "DATE", [CODE] = [_THISROW].[CODE])))

[Suresh_Subraman]

Thank you I really appreciate it. For some reason, there are no errors and the column does not show in results.

[WillowMobileSys]

Can you provide an example of where it is not showing?

[Suresh_Subraman]

The script is accepted, but it is not loading on the details page. If you really see the [Table 1], I need 1000 to be displayed in the “??” column. When I use the test data option in the expression editor, it results in a list of data.

I hope the issue with KEY COLUMN as my key columns are not the same in both tables. [CODE] in both column are the matching condition.

After removing brackets for KEY_COLUMN ie., ID, now the column is visible, but the results default Zero

ANY(SELECT(TABLE 2[BUDGET], "ID" = MAXROW("TABLE 2", "date", [CODE] = [_THISROW].[CODE])))

TABLE2
ID[KEY] | DATE | CODE | NAME | BUDGET
AD001 | 2020-07-14 | A1 | google1| 500
AD002 |2020-12-31 | A1 | google2 | 4500
AD003 |2021-02-08 | A1 | google2| 3220
AD004 |2021-06-02 | A1 | google3 | 1000

TABLE1
CODE[KEY]| NAME | LATEST BUDGET
A1 | google | ???

@WillowMobileSystems @Lynn @Steve

[WillowMobileSys]

Suresh_Subramani:

ANY(SELECT(TABLE 2[BUDGET], "ID" = MAXROW("TABLE 2", "date", [CODE] = [_THISROW].[CODE])))

In this expression “ID” should be [ID]

[Suresh_Subraman]

@WillowMobileSystems, the destination table does not have “ID” column, so I tried [Code] which is the key column, I also tried [ID] still it did not load the value. If I use [ID], the column disappears from the detailed page. Both table Key Columns do not have matching values. [Table 1] has Key column as [Code], [Table2] the key column is [ID], Table2 also has a [code] column

[WillowMobileSys]

The expression is operating ONLY on Table 2 in order to return the Budget value from the desired Table 2 row. So, all of the columns will be from Table 2. If ID is your key column in Table 2, as indicated in your post above by the header “ID[Key]” then the expression I proposed MUST use that column because MAXROW() returns the row key as a reference to the row. So the expression should be in this way:

ANY(SELECT(TABLE 2[BUDGET], 
        [ID] = MAXROW("TABLE 2", "date", [CODE] = [_THISROW].[CODE])))

If no value is returned then you will need to breakdown the expression and test it.

I do this by adding a temporary Virtual Column to the Form so that when I pull up the row I can see what values are being used. For example, I would create a Virtual Column for the MAXROW() part and then look at it in the Form to verify it is returning a value and the correct one. If it is, the rest should just work.

If you are still having issues and not sure what to do, post an actual image from the AppSheet editor of your Table 2 columns. With the posted text above, we are making certain assumptions about how you have things defined. If some of those are not as expected then they could be causing you issues that we here simply cannot know about.

[Suresh_Subraman]

I want to Sort by Date, but get any column as a return. Can someone alter my script and give me an idea please

TOP( SORT( SELECT( projectHistory[Date],([_THISROW].[CODE] = [CODE])),FALSE), 1 )

This returns just the latest date, I need another column [Name] as the return, but sort by Date.