Recurring action

reggieneo
Participant V

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

0 10 166
10 REPLIES 10

Steve
Participant V

None of this makes any sense to me.

reggieneo
Participant V

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.

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

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
Participant V

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

3X_5_6_56e2c3bc586b0c1f5588670710c9516eaecf4a2b.png

Your Valif If expression includes this clause?

3X_5_5_55eba2350dfd21b42644f38af2d68563a93cbb5c.png

reggieneo
Participant V

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.

reggieneo
Participant V

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
Participant V

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

Top Labels in this Space