Recurring action

Hi All,
I still haven’t crack the method to get my payroll working automatically. I am able to use it in manual mode but I like it to be one-click all [name] to be payroll ready (computed).

in the [name] column, I am extracting the name from my list thru this expression:
ANY(
TOP(
(
LIST(
Select(Dipstick[Pump 1 Attendant],And([Date]>=[_Thisrow].[Date Start],[Date]<=[_Thisrow].[Date End])),
Select(Dipstick[Pump 2 Attendant],And([Date]>=[_Thisrow].[Date Start],[Date]<=[_Thisrow].[Date End])),
Select(Dipstick[Cashier],And([Date]>=[_Thisrow].[Date Start],[Date]<=[_Thisrow].[Date End])))
- LIST(Select(Salary[Name],And([Week Salary Count]>0,And([Date Start]>=[_Thisrow].[Date Start],[Date End]<=[_Thisrow].[Date End]))))),
1
)
)

I have also set the valid-if to reduce the name list if the name is already payroll ready (computed).

 (
   (Select(Dipstick[Pump 1 Attendant],And([Date]>=[_Thisrow].[Date Start],[Date]<=[_Thisrow].[Date 
    End]))
    +
   Select(Dipstick[Pump 2 Attendant],And([Date]>=[_Thisrow].[Date Start],[Date]<=[_Thisrow].[Date 
End]))
 +
 Select(Dipstick[Cashier],And([Date]>=[_Thisrow].[Date Start],[Date]<=[_Thisrow].[Date End]))
  )
  -
  Select(Salary[Name],And([Week Salary Count]>0,And([Date Start]>=[_Thisrow].[Date Start],[Date 
  End]<=[_Thisrow].[Date End])))
  )

the challenge is to ensure that the action will select the next available [name] to run the computation of the week payroll.

to add, I have also created a counter that supposed to stop the action when the counter reached 0.

thanks

None of this makes any sense to me.

1 Like

Hi Steve,
When I clicked “add” in salary context, the name column/field will be populated by the name that is not yet in the spreadsheet. I would like to have an automatic repeat process till all the names of that week are listed in the sheet. hope that makes sense.

1 Like

So each time the Add button is clicked, the Name column is pre-filled with the name of someone that does not already have a row in that table. Okay, that’s doable.

Your Initial value expression was actually really close–you were definitely on the right track. Here it is with some reformatting:

ANY(
  TOP(
    (
      LIST(
        SELECT(
          Dipstick[Pump 1 Attendant],
          AND(
            ([Date] >= [_THISROW].[Date Start]),
            ([Date] <= [_THISROW].[Date End])
          )
        ),
        SELECT(
          Dipstick[Pump 2 Attendant],
          AND(
            ([Date] >= [_THISROW].[Date Start]),
            ([Date] <= [_THISROW].[Date End])
          )
        ),
        SELECT(
          Dipstick[Cashier],
          AND(
            ([Date] >= [_THISROW].[Date Start]),
            ([Date] <= [_THISROW].[Date End])
          )
        )
      )
      - LIST(
        SELECT(
          Salary[Name],
          AND(
            ([Week Salary Count] > 0),
            AND(
              ([Date Start] >= [_THISROW].[Date Start]),
              ([Date End] <= [_THISROW].[Date End])
            )
          )
        )
      )
    ),
    1
  )
)

The use of LIST() around SELECT() is both unneeded and causes a problem. Instead, SELECT() provides a list result inherently, so we can use those results directly. To combine the results, we can use list addition, just as you’re already using list subtraction to remove the names that are already present. With these changes:

ANY(
  TOP(
    (
      SELECT(
        Dipstick[Pump 1 Attendant],
        AND(
          ([Date] >= [_THISROW].[Date Start]),
          ([Date] <= [_THISROW].[Date End])
        )
      ),
      + SELECT(
        Dipstick[Pump 2 Attendant],
        AND(
          ([Date] >= [_THISROW].[Date Start]),
          ([Date] <= [_THISROW].[Date End])
        )
      )
      + SELECT(
        Dipstick[Cashier],
        AND(
          ([Date] >= [_THISROW].[Date Start]),
          ([Date] <= [_THISROW].[Date End])
        )
      )
      - SELECT(
        Salary[Name],
        AND(
          ([Week Salary Count] > 0),
          AND(
            ([Date Start] >= [_THISROW].[Date Start]),
            ([Date End] <= [_THISROW].[Date End])
          )
        )
      )
    ),
    1
  )
)

There’s an unneeded AND() in the last SELECT(), and the use of TOP() is also unneeded:

ANY(
  SELECT(
    Dipstick[Pump 1 Attendant],
    AND(
      ([Date] >= [_THISROW].[Date Start]),
      ([Date] <= [_THISROW].[Date End])
    )
  ),
  + SELECT(
    Dipstick[Pump 2 Attendant],
    AND(
      ([Date] >= [_THISROW].[Date Start]),
      ([Date] <= [_THISROW].[Date End])
    )
  )
  + SELECT(
    Dipstick[Cashier],
    AND(
      ([Date] >= [_THISROW].[Date Start]),
      ([Date] <= [_THISROW].[Date End])
    )
  )
  - SELECT(
    Salary[Name],
    AND(
      ([Week Salary Count] > 0),
      ([Date Start] >= [_THISROW].[Date Start]),
      ([Date End] <= [_THISROW].[Date End])
    )
  )
)
1 Like

The Valid If expression is very similar to the Initial value expression:

(
  SELECT(
    Dipstick[Pump 1 Attendant],
    AND(
      ([Date] >= [_THISROW].[Date Start]),
      ([Date] <= [_THISROW].[Date End])
    )
  ),
  + SELECT(
    Dipstick[Pump 2 Attendant],
    AND(
      ([Date] >= [_THISROW].[Date Start]),
      ([Date] <= [_THISROW].[Date End])
    )
  )
  + SELECT(
    Dipstick[Cashier],
    AND(
      ([Date] >= [_THISROW].[Date Start]),
      ([Date] <= [_THISROW].[Date End])
    )
  )
  - SELECT(
    Salary[Name],
    AND(
      ([Week Salary Count] > 0),
      ([Date Start] >= [_THISROW].[Date Start]),
      ([Date End] <= [_THISROW].[Date End]),
      NOT([_ROWNUMBER] = [_THISROW].[_ROWNUMBER])
    )
  )
)

The enclosing ANY() is removed, and the final SELECT() is given an additional condition to avoid removing the Name value from this row from the list of valid values.

1 Like

Thanks!
After test, the name Erika was picked up again so it came back error.

image

Your Valif If expression includes this clause?

image

1 Like

yes, it is included. not sure if this fact will affect it but the salary table will have a repeat names number of times since the table is a list of weekly payroll.

Yep, that’ll affect it.

how to make it unique? i thought since we have a name with inclusive dates that we should be able to make each one unique

after several testing, i noticed that when i manually clear the prefilled selected name it automatically removed the name which was just recorded in sheet.

but the issue will repeat on the next recording or inputting of name