Recurring action

reggieneo · January 31, 2021, 10:52am

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

Steve · January 31, 2021, 2:59pm

None of this makes any sense to me.

reggieneo · January 31, 2021, 3:52pm

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.

Steve · January 31, 2021, 3:55pm

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])
    )
  )
)

Steve · January 31, 2021, 4:02pm

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.

reggieneo · January 31, 2021, 4:13pm

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

Steve · January 31, 2021, 5:14pm

Your Valif If expression includes this clause?

reggieneo · February 1, 2021, 1:45am

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.

Steve · February 1, 2021, 1:46am

Yep, that’ll affect it.

reggieneo · February 1, 2021, 2:10am

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

reggieneo · February 1, 2021, 2:27am

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

Recurring action

Company

Platform

Pricing

Solutions

Support

Account