Not getting the correct score

[IamGroot]

Hello Everyone,

Basically, I have a DataPage that let the users evaluate themselves by choosing a statement that best describes how they performed the task. Each statement have different point values. These values will be received in the fields. One value in each field. The score will be the sum of those values.
As the formula I use now in my tabular DataPage, If a user is performing the task 5 times, and the scores are 30, 40, 50, 60, 70 - the displayed score will be 250.

How should the formula look like to display only the last score, in this case 70? Depending on the user who just logged-in.

This is the formula I currently use:

IsNull((SELECT (SUM(EvalA)+SUM(EvalB)+SUM(EvalC)+SUM(EvalD)) FROM TABLE_EVAL WHERE USERS = '[@authfield:User_ID]'),0)

Thank you in advance  

[kpcollier]

Hey @IamGroot, are you looking to only display the last score (EvalD) or the highest score? You could just select EvalD in the sql statement. If not, you could do something like this
 

SELECT MAX(id) FROM table

or 

SELECT TOP 1 * FROM table ORDER BY id DESC;

 

[Aether]

Hello @IamGroot,

For you to be able to get the last score from your user's self evaluation, you can  just use this SQL formula in your calculated field instead:

(SELECT TOP 1 EVALA FROM TABLE_EVAL WHERE USERS = '[@authfield:User_ID]' ORDER BY Feedback_ID DESC) + 

(SELECT TOP 1 EVALB FROM TABLE_EVAL WHERE USERS = '[@authfield:User_ID]' ORDER BY Feedback_ID DESC) +

(SELECT TOP 1 EVALC FROM TABLE_EVAL WHERE USERS = '[@authfield:User_ID]' ORDER BY Feedback_ID DESC) +

(SELECT TOP 1 EVALD FROM TABLE_EVAL WHERE USERS = '[@authfield:User_ID]' ORDER BY Feedback_ID DESC)

In this formula, it will just get the last value of (EVALA, EVALB, EVALC, & EVALD) in the TABLE_EVAL depending on the user logged in your DataPage.

I hope this helps  

~WatashiwaJin~

[Aether]

You can also add a formula field in your table that will get the overall TOTAL of the user evaluation.

For Example (Formula field: TOTAL): 

[@field:EVALA] + [@field:EVALB] +  [@field:EVALC] + [@field:EVALD] 

Then you can just call that one field in your calculated field instead:

SELECT TOP 1 (TOTAL) FROM TABLE_EVAL WHERE USERS = '[@authfield:User_ID]' ORDER BY Feedback_ID DESC

I hope this process also helps you  

~WatashiwaJin~

[Aether]

You can also refer to this article for more information:

 

[NailDyanC]

Hi, just want to add. You may also consider checking this post: 

 

[ianGPT]

Hello,

Just want to share this howto article that you can use for referencing available syntax in calculated fields/value.

https://howto.caspio.com/function-reference/