related actions, second action behavior not working

[ingalsaac76]

Hello! I have this problem I hope you can help me.
I have 2 tables
SALES
        id, period, id_employee, authorized, total_sale, date, extrafields....

EMPLOYEE_BONUSES
        id, period, id_employee, total_sale

I have 2 actions related

 1st. find_bonuses_period: data: execute an action on a set of row
        for a record of SALES
        referenced table SALES
        referenced rows: filter(SALES, AND([period]="JUN-2022", authorized="S"))
        with this action I can find all the authorized sales on the JUN-2022 period, all the employees may have 0 or more sales on every period, so I can have many rows from the same employee
        referenced Action: bonuses_receipt

  2nd. bonuses_receipt: data: add a new row to another table using values from this row
           for a record of this table: SALES
           table to add to: EMPLOYEE_BONUSES
           set columns: period = [period]
                                    id_employee = [id_employee]
                                    date = today()
            here is the problem!
                              BEAHAVIOR (only if this condition is true):
ISBLANK( filter(EMPLOYEE_BONUSES, AND([period]=[_THISROW].[period],[employee]=[_THISROW].[employee]) ) )
I want insert only 1 record for each employee (I will calculate the bonus total with a VC) but this condition is not working and the 2nd action insert all the records.
I don't know if I have the problem in the way I put the actions, I'am stuck in this since last week, I have try create 2 extra fields on EMPLOYEE_BONUSES with the values from the condition, this values are generated rigth, but the condition is not working. any idea?

[ingalsaac76]

mysteriuosly when I run the process again the 2nd action don't create duplicates records, It has to be related with the "insertion time"? the 2nd action don't have the recordset refreshed? thats why it don't find records and insert the duplicated?

[Steve]

I've not tried your approach of testing or the prior existence of the row in the add action (but I like it), so I don't know whether it should work. I would expect it to work, though, UNLESS you're doing all this with a bot. If you're using Automation, it's possible or even probable that it won't work as desired (based on the experiences others have described).

One workaround is to enhance the Referenced Rows expression in the first action so that it only includes one row per employee.

WHOOP! (Train of thought crashes to a halt)

Should this be a column reference? Like [authorized]="S" (with square brackets)?

Let's resolve this question before proceeding.

[ingalsaac76]

hello @Steve!  thank you for your reply, my text had a typo, that part of the condition has the square brackets:
filter(SALES, AND([period]="JUN-2022", [authorized]="S"))

The sales table is a list where the supervisor change manually if the bonus is authorized or not, every employee has zero or more sales on a period, I need to create a unique receipt for every employee on a period with the list of the authorized sales, I want to create a master-detail report but I don't have a "master", that is why I want to create the master with a list of unique rows, 
about your comment of "Referenced Rows expression" I can't find an expression where I could get a recordset of unique sales, I try to use "select" but it only get me one field when I need more, if you have a better idea to create this master-detail receipt it's welcome, thank you for your time.

[ingalsaac76]

the best result I could get its create an column "firstRow" and it's filled with true or false every row, so when the field calculates  true the condition:

ISBLANK( filter(EMPLOYEE_BONUSES, AND([period]=[_THISROW].[period],[employee]=[_THISROW].[employee]) ) )

putting an "true" on that record and the rest with the "false" so I can get a filter by [fistRow]=true with only unique employees on that period.

the same condition don't work on the behavior section and creates duplicates.

[Steve · Accepted solution]

FILTER(
  "Sales",
  AND(
    ([period] = "JUN-2022"),
    ([authorized] = "S"),
    (
      [_ROWNUMBER]
      = MAX(
        SELECT(
          Sales[_ROWNUMBER],
          AND(
            ([period] = [_THISROW-1].[period]),
            ([authorized] = [_THISROW-1].[authorized]),
            ([id_employee] = [_THISROW-1].[id_employee])
          )
        )
      )
    )
  )
)

[ingalsaac76]

OMG!
OMG!
that was a street fighter combo to my mind, let me check and see how it works, thank you for your time again!

[ingalsaac76]

(
[_ROWNUMBER]
= MAX(
SELECT(
Sales[_ROWNUMBER],
AND(
([period] = [_THISROW-1].[period]),
([authorized] = [_THISROW-1].[authorized]),
([id_employee] = [_THISROW-1].[id_employee])
)
)
)
)

Are you checking "physically" which one is the MAX _rownumber from all the rows? It should be calculated with MIN instead? because I want a unique value, with MAX the value will be different every time because that number is growing after every insert, I think....

[Steve]

MAX() or MIN(), it doesn't matter. The point there is to see if the row being examined is one specific row. If we're examining that one specific row, it's a match otherwise it's not a match. The one specific row could be the very first row (MIN()) that matches the other criteria, or the very last row (MAX()) that does.

[ingalsaac76]

It work! that sentence only bring me unique values, Thank you for your help! now I have to understand "WHY" 🤣
why I have to add another sentence with the filters of period and authorized? you lookup for the MIN/MAX rownumber of that period, authorized and id_employee, when I have to do this extra check? only when I don't get the results I want? [_THISROW-1] is has the prior row than I'm comparing with?
where I can read more about [_THISROW-1] I have searching on the community and can't find any articule about it.

Thank you again!