Dataverse table relationships and using it to help sum columns

(0) Share

Report

Posted on by lmcc

I am officially stumped.

I have learned how table relationships helps with performance.

I have two tables. One called Users with 'Year Group' as a choice column ranging from 1 - 6. I have a second table called points which contains a lookup column for student ID from the Users table.

Each user can have multiple records in the points table.

I would like to sum up the points for the users that are in Year 1 for example, How might the formula look?

I have tried this, but this is not working.

Sum( Filter( 'Points', LookUp(Users, ID = ID).['Year Group'] = 1), 'Earned Points' )

Could this be the solution:

ClearCollect( UserHousepointTotals, ForAll( BSKL_Users, { UserID: ID, TotalHousepoints: Sum( Filter( 'Housepoint Transactions', 'Student ID' = ID ), 'Earned Housepoints' ) } ) )

Any help would be greatly appreciated.

@WarrenBelz, @SpongYe @FLMike Can you offer any advice for me, please?

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (2)

Verified answer

NPPlatform 606 Moderator on at

Like (1)

Report
Copy link

Link copied!
@lmcc What is your lookup column in the table 'Points' called? For now I'll assume it's studentID. If so, the following formula should you point you in the right direction:

Sum(Filter('Points', 'studentID'.'Year Group' = 1), 'Earned Points')

Was this reply helpful? Yes No
lmcc 77 on at

Like (1)

Report
Copy link

Link copied!

@NPPlatform I can't believe it was so simple in the end. This has really helped me - thank you very much!

Was this reply helpful? Yes No
Verified answer

NPPlatform 606 Moderator on at

Like (1)

Report
Copy link

Link copied!

You're welcome! Glad I could help you. Just so you understand why it's working: a lookup column is a reference to another record. This referenced record has its own values. In your example the record is a row in the table Users with a value for Student ID, a value for Year Group and probably also values like 'Name', 'Mail', 'Age, and so on. You can access all this information through the lookup-column in your table Points, with the formula [name of lookup-column].[name of desired value]. Best of luck with your app!

Was this reply helpful? Yes No
lmcc 77 on at

Like (1)

Report
Copy link

Link copied!

Thank you for taking the time to explain the code. Beginners like me really thrive off helpful people such as yourself - take care!

Was this reply helpful? Yes No