Ref_Rows() not work as expected

AppSheet AppSheet Q&A
,
1

polohi_2-1686558548987.png
polohi_2-1686558548987.png1892×1958 349 KB

I have three tables “Leave History”, “Leave”, “Employee detail”.Where Leave Histroy ref to Leave, Leave ref to Employee detail all using EMP_Email.

But I want to ref back from Leave to History with Ref_Rows(), instead of giving me the result of History. It gives me employee detail which does not makes sense, or can anyone point out what I was doing wrong?

Here is want I want to achieve, caculate the leave of current month from history table and insert into current leave table.

SUM(
SELECT(
Leave History[time_in_hour],
AND(
[EMP_Email] = [Related Leave Historys].[EMP_Email],
MONTH(TODAY()) = MONTH([Related Leave Historys].[date])
)
)
)

2

Dereference expressions - AppSheet Help

3

Thanks for this. I understand the concepts it explain clearly but how to do it if I already have the ref one way and I want to refer backward.

4

I don’t understand your data structure or goal well enough to draft an example. Nonetheless, in what you have so far the basic mistake is that [Related Leave Historys] is not a Ref type column, but rather a list of key values from another table that references its table. You need to distinguish between the following. Note that one uses a dot (.) delimiter and the other does not.

  • Dereference one reference value: [Ref column].[Column from referenced table]
  • Dereference a list of referenced values: [Related... column][Column from referencing table]
2 Likes