How to use a statement to locate similar data in another table and output value of the same row

[Sheikh_Aqil]

Hi, I just started using Appsheet so I'm not used to the expression and formula part. Right now I'm making an app where the student can request merit marks and teachers can accept their request.

The statement is a list of type of activities that determines how much merit that they would receive.

The merit table holds a list containing statements (matching in the request table) that would determine how much merit they would get for the statement task.
I'm trying to get the student to choose the statement from the enum column and then it will ouput the merit they would get from the other merit table.

ANY(
SELECT(
Merit Data[Current Merit],
([Statement] = [_THISROW].[Statement])
)
)
this is the line of code I tried to use, but it doesn't update the merit mark after their request is updated.
After that i also want to add the current merit to another table (when the status column is changed to "accepted" ) to sum the merit in the request with the merit in the other table.

I tried to refer to examples but it doesn't seem to work, is there something wrong with the type of column i used or is there a settings that allow updating data immediately that I'm still haven't found.
I've been stuck with this for 3 days so I appreciate anyone who can help me and thank you for your precious time.

[Tommy62]

Machen Sie sich mit dem Basistyp REF vertraut, Ihr Lösungensansatz wäre die Dereferenzierung siehe Verwenden Sie Enum/EnumList (Basistyp: REF), um Daten zu dereferenzieren 

[Marc_Dillon]

Where exactly are you using that ANY(SELECT( expression?

[Sheikh_Aqil]

I was using it in the formula for the Current Merit column in Request Data table, the first image.

[Marc_Dillon]

App Formula or Initial Value?

And you're not wanting it to evaluate until the [status] equals a certain value?

[Sheikh_Aqil]

App formula, and the status thing is when I want to sum the merit value with the third table, sorry for confused explanation.

Should i set the code as initial value?

[Marc_Dillon · Accepted solution]

So the expression outputs the merit value when the request record is first created, but doesn't "doesn't update the merit mark after their request is updated"? Not quite understanding what the issue or question is here.

And then there is a 2nd question about a 3rd table, a student table? Are you trying to get a sum of all merits for each particular student, where a request status is "accepted"?

You should definitely look into using Refs. Like the Request table should have 2 Ref columns, one to a Statement record, and one to a Student record. Then you can more easily pull values via dereference. Not that your current expression is wrong, this way is just easier.

https://help.appsheet.com/en/articles/961426-references-between-tables

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

[Sheikh_Aqil]

Sorry for the late responce, Thanks for the link.

After reading it and went to look it up, i stukbled upon the lookup function, which was what i was looking for.

[Marc_Dillon]

Ok, but LOOKUP() is the exact same as ANY(SELECT()), so that in itself wouldn't have solved whatever your issue was.