Problem with sum(select(and)))

i will two table,
table one called student contain student details
table two is a payment_log contains payment amount, which month they paid for, date and time.

i want to create a virtual column in table student which can calculate each student’s monthly pay amount, because some of them may pay two / three times.

so a select function with two filter is my thought to this. I tired to write the formula, but it doesnt work…

Sum(select(and(payment record[paymonth] = “Mar” , payment record[payment], [name]=[_thisrow].[id])))

the error display that Cannot compare List with Text in (Payment record[Paymonth] = “Mar”)

anyone can help me?Thank you!

Hi @HoeySho
Check out the expression in this thread for the correct syntax.

3 Likes

Perhaps try:

Sum(
Select(
payment_log[payment],
and(
[paymonth]=“Mar”,
in(payment_log[name],[_thisrow].[name])
)
))

Your table/column names may be different.

3 Likes

just try LOOKUP formula i thinks it easier to use

LOOKUP([ Column in this row for ref ] , “Tablename” , “Column in Tablename for REF” , “Column to Pull value”)

thank you for reply

i tried but it said

Parameter 2 of function IN is of the wrong type

is it work if i have more than one name / month??

what i want to do is to sumup the student’s payment by specfic month

thx for reply!

1 Like

in() return the value true/false, can it also help me to filter the list?? thank you!

try adding LIST()

in(payment_log[name],LIST([_thisrow].[name]))

finally i solved it!!! thank you everyone

sum(
select(
payment record[payment],
and ( [name] = [_thisrow].[id], in([paymonth], {“Mar”}))))

6 Likes

@HoeySho
Great

Yes i use this formula for check ID , password perUser and anything to pull data
like this

OR (
[User Level] <= 10 ,
[แผนก] = “HR” ,
AND( [User Level] <= 10 , LOOKUP([Member ID] , “HR.Members” , “รหัสพนักงาน” , “แผนก”) = LOOKUP([ผู้ลา] , “HR.Members” , “รหัสพนักงาน” , “แผนก”) ) ,
AND([Action] = “SIGNIN” , [ผู้ลา]=USERSETTINGS(Member ID) )

)

even more column but there secret cant show

before i understand to use REF Type

1 Like