Re: Help with a FILTER() expression

jamijr · 12-13-2020 03:54 AM

Hello!

I’m trying to use a FILTER expression, but I don’t understand why is it giving unexpected results.

I have two tables: Sessão (with “IDSessão” and other columns) and Agendamento (with “IDAgendamento”, “IDSessão” as a reference, and other columns).

I’m setting up a workflow that will run in every update to the Agendamento table with the following condition:

COUNT(
  FILTER(Agendamento, ([IDSessão] = [_THISROW_BEFORE].[IDSessão]))
) = 0

I was expecting the filter to return 0 results, but it keeps returning a list with all the Agendamento in my sheet, without filtering anything.

Could anyone help me with this? Thanks in advance.

Suvrutt_Gurjar

Maybe if you wish to have no condition in the condition section of the workflow, if you wish the workflow to run on any update of the said table’s row.

If you wish the workflow to run on update to a specific column in the record , you could use the method described in the article below under the title

Sending Email When a Row is Updated to Have a Specific Column Value

Please post back if you are looking for something else.

jamijr

Hi!

I think I really need the condition in the workflow. I want to change the “Status” of the Sessão only if there are no other Agendamento referencing it.

My pain point here is the FILTER expression. I just can’t understand why it keeps returning all the values in my table, ignoring my condition ([IDSessão] = [_THISROW_BEFORE].[IDSessão])

I also tried it with a SELECT expression SELECT(Agendamento[IDAgendamento], ([IDSessão]=[_THISROW_BEFORE].[IDSessão]), true), but got the same wrong result: all the values, ignoring the condition.

Suvrutt_Gurjar

Thank you.

I believe you may wish to share a bit more on your requirement. Is this Sessão column where you wish to change status in parent table or the child table? Do you wish to change the status when a child record is added or changed etc.? Also is your workflow essentially a data change workflow?

jamijr

Hi!

The Sessão table has a Status column. The Status can be either “Open” or “Busy”.
The Agendamento table has a SessãoID column that references to the Sessão table

I already have set up a workflow that fires every time an Agendamento is created or updated. It verifies which Sessão is it referencing and changes the Sessão Status from “Open” to “Busy”.

What I’m trying to do now is the opposite. When an Agendamento is edited and it’s SessãoID is changed, the app needs to change the previous Sessão Status from “Busy” to “Open”. But this should only happen if there are no other Agendamento associated with that Sessão.

Ideally, one Sessão could have only one Agendamento, but it can happen that two or more users simultaneosly create two or more Agendamento and select the same Sessão.

Suvrutt_Gurjar

Thank you for more clarifications.

From the description, it appears that Agendamento is child table of Sessão table. So could you please add if you are trying to change the reference column in the Agendamento table?

And if so , could you please mention any specific reason why you wish to change the reference column itself?

jamijr

Hello!

Yes, the user changes the reference column in the Agendamento table. But the Status is changed at the Sessão table.

It’s a scheduling app. Think about the Sessão as a time slot. It has a date, time and and a staff member. The Agendamento is the appointment, it contains name, phone number, and other information about the person scheduling an appointment. Sometimes, the person needs to reschedule their appointment, that’s why we need to edit the Agendamento and change it’s referenced Sessão.

The app also needs to change the previous Sessão Status, from Busy to Open, to make the Sessão available for another person.

Suvrutt_Gurjar

As per my current testing and understanding , I believe what you are looking for does not seem to be freasible. I am unsure if the previous status of ref ID can be available to update the previous parent through workflow and action combination.

Someone else may have a better insight and suggestion.

jamijr

The workflow doesn’t need to know what is the current Status of the previous Sessão. Only if there is any Agendamento that also referes to the same Sessão

I created a test workflow that sends me an e-mail on every update in Agendamento

Previous session: <<[_THISROW_BEFORE].[IDSessão]>>
New session: <<[_THISROW_AFTER].[IDSessão]>>

SELECT's result: <<SELECT(Agendamento[IDAgendamento], ([IDSessão] = [_THISROW_BEFORE].[IDSessão]))>>

This is what’s stored in Agendamento sheet:

And this is the e-mail that the workflow sent:

Previous session: 6Ky8a5ds
New session: Nge6qOFK

SELECT's result: 19bcd910 , 2ecedf29 , ea416d4b , 0d9481b6 , 3ef8198b , 86fe2f24

As you can see on the sheet, after the update there is no Agendamento that has the previous IDSessão. I expected that the SELECT’s result would be empty, but it keeps returning all the IDAgendamento values.

Suvrutt_Gurjar

Yes, your observation is correct that the expression returns a list. As I mentioned earlier, I am unsure about the use of [_THISROW_BEFORE] and [_THISROW_AFTER] in multirow expressions. Maybe someone else will have more insights.

I believe you need to set the previous Sessão to open if the related Agendamento count is zero. And I believe that is where the challenge is of tracking the previous Sessão

Alternative approach:
Anyway, I have tested the requirement with an alternative approach of reference actions and it works. However, the option is a bit long wielding. It is as follows

Create two “Set The values of some columns in this row” type actions on your [OpenBusy] column in the Sessão table.

A) One action called say SetSessioOpen to set value to “Open”,
B) the other called say SetSessionBusy to “Busy”

Add a ChangeDateTime type column in Agendamento table called say [DateTimeStamp] and set the change column as “IDSessão”
Create two reference actions on the Agendamento table. Action type: “Execute an action on a set of rows” Reference table in both cases in this action to be set to Sessão

A)One action called say SetSessioParentOpen with expression

SELECT( Sessão[IDSessão], AND(ISBLANK([Reverse Reference column of Agendamento table in Sessão table]), (NOW()-[DateTimeStamp]<“000:00:25”)))
Reference action: SetSessioOpen

B) The second action called say SetSessioParentBusy with expression

SELECT( Sessão[ IDSessão**], AND(ISNOTBLANK([Reverse Reference column of Agendamento table in Sessão table ]), (NOW()-[DateTimeStamp]<“000:00:25”)))
Reference action: SetSessioBusy

Create a Group action on Agendamento table and have SetSessioParentOpen and SetSessioParentBusy as two actions within this group action.
Set this group action as event action on Agendamento form save.

jamijr

Hi! Thanks for your reply!

Before you sent it, I started to rethink my usage of the Status column and decided to go with a different approach (which now I realized it’s way simpler and easier than what I was trying to do before).

I was using the Status column to just filter out all the “Busy” sessions (Sessão) when the user is scheduling an appointment (Agendamento).

Now, I got rid of the Status column and in the appointment form, I’m using a Valid_IF expression that only selects the sessions that are not being already in use.

NOT(IN([IDSessão], Agendamento[IDSessão]))

This allowed me to get rid of the workflow mess that I was making. I just wish I thought about this before. Sometimes I just make things more complicated then it should be

Thank you for your patience and helping me out!

Suvrutt_Gurjar

Thank you for the update. Nice to know you solved it.

Steve

Please post a screenshot of the workflow configuration that includes the condition expression you referenced in your original post.

LeventK

@Steve
Why don’t we look into the palantir instead of asking what the expression is, what’s the table schema etc.?