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!
TOP( SORT( SELECT( projectHistory[Date],([_THISROW].[CODE] = [CODE])),TRUE), 1 )
CODE | NAME | LATEST BUDGET
A1 | google | ???
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