REF_ROWS() formula I canțt figure it out

[sorin_mihai]

Hello all

I’m having some trouble creating a formula.

So in my Task Management App, Makers are the Employees and they complete their Working or ExtraWorking Times (working outside the daily scheduale).

So I have a Makers Table, a Tasks Table and a Times Table.

When selecting a Task, there is an action button to add Times to that Task (a Maker completes the for to say that he spent 2 hours working on that Task). In this form there is an Enum option to choose whether that time was worked during the daily working hours - Working Time or if it was done on top of daily working hours - Extra Time.

What I would like is to have in the Makers Table, a virtual list column of all the Extra Times completed by each Maker.
So in my Makers View, when I go to the Detail View of one Maker, I can see the list of only the Extra Times he has completed in the app.

Any Ideas how this formula should look for the Virtual Column?
I guess it should be done with REF_ROWS() but I can’t figure it out.

Thank you,
Sorin

[Suvrutt_Gurjar]

Hi @sorin_mihai,

I believe some additional inputs on table and column configuration will be useful to the community to help you better.

Could you confirm if it is correct to assume that the Tasks table (child table) references Makers table (parent table).

Is the enum (working and extra working )in the Task table. Is the Times(duration) column in the third table, that is Times table? And does the Times ( child table) references Tasks (parent) table?

[sorin_mihai]

Hy,

So the Tasks Table references the Makers Table yes.
And also the Times Table references both the Tasks and the Makers Table. Each time is completed by a Maker for a certain Task.
The Enum is in the Times Table, as the Time spent is either Working or Extra.

So a Maker, hits the Add Times action button of a Task, and that takes him to a Form to complete a Time (in the Times Table). In the form he completes the Time spent - 2 hours and the type of time from the Enum - Working or Extra.

[Steve · Accepted solution]

Used from Makers Table, this:

REF_ROWS("Times", "Maker ID")

means, in English, “list the rows in the Times table with a Maker ID column value that references this row” of the Makers Table table. It is equivalent to:

FILTER(
  "Times",
  ([_THISROW] = [Maker ID])
)

which isn’t specific enough for your purposes. Instead, it sounds like you want:

FILTER(
  "Times",
  AND(
    ([_THISROW] = [Maker ID]),
    ("Extra Time" = [type of time])
  )
)

[sorin_mihai]

The Filter you wrote works perfectly,
Thank you so much