Filtering help

[MrAutomate]

I need some help...
I have one PowerApps with 2 Data sources:

Data Source:	Relevant Column Name:	Sharepoint Datatype:	Notes
tSheets2	1:Teammate, 2: DATE (the date the clock in occurred)	Single line of text	This is a time sheet tool.
ACTIVETeammates	FullName	Single line of text	I use this as the basis for all teammate related searching and filtering, etc

I have a dropdown using this :SortByColumns(ActiveTeammates, "FullName").FullName - This dropdown presents a list of active teammates to the user to select their name and clock in...

I want to add a filter that says if a user is ALREADY clocked in for the day, do not present that user in the list from the activeteammates values.   Why introduce the possibility of someone accidentally clocking someone else in again?  I have tried different methods and cannot seem to get the correct formula.

so, only  give me a list of activeteammates.fullname:

No [Date] = today in the tsheets table

Activeteamates.Fullname <> tsheets.Teammate

Fullname and teammate name are equivalent, as the teammate name column in tsheets is initially fed from ActiveTeamates.fullname field

Thanks all!

[rahulswimmer]

@MrAutomate 

So you basically want to update the ActiveTeamMate list based on whether column in the other table is Today() or not, correct ?

[v-qiaqi-msft · Accepted solution]

Hi@MrAutomate,

Could you please tell me that if you want to remove a certain user who has already clocked in today from the activeteammates Dropdown?

I assume that you use a Dropdown and a DatePicker to select when a certain user will clock in.

Actually, you can use a simple determination to check if the user has already clocked in for the day.

IsBlank(LookUp(tSheets2,Teammate=Dropdown1.Selected.FullName && DATE=Text(DatePicker1.SelectedDate,"[$-en-US]mm/dd/yyyy")))

If this condition returns true, you could update the selected team mate, otherwise could not.

You could put the following formula into the OnSelect property of the submit button:

If(
    IsBlank(
        LookUp(
            tSheets2,
            Teammate = Dropdown1.Selected.FullName && DATE = Text(
                DatePicker1.SelectedDate,
                "[$-en-US]mm/dd/yyyy"
            )
        )
    ),
    SubmitForm(Form1),
    Notify(
        "This user has already clocked in today!",
        NotificationType.Error
    )
)

On the other hand, if you insist on filter users in the Dropdown to make only the users that have not clocked in today appear, please try the following solution.

Set the OnStart property of the App as below:

ClearCollect(Test,ACTIVETeammates.FullName)

Set the OnSelect property of the Dropdown:

If(
    !IsBlank(
        LookUp(
            tSheets2,
            Teammate = Dropdown1.Selected.FullName && DATE = Text(
                DatePicker1.SelectedDate,
                "[$-en-US]mm/dd/yyyy"
            )
        )
    ),
    Remove(
        Test,
        Filter(
            Test,
            FullName = LookUp(
                tSheets2,
                Teammate = Dropdown1.Selected.FullName && DATE = Text(
                    DatePicker1.SelectedDate,
                    "[$-en-US]mm/dd/yyyy"
                )
            ).Teammate
        )
    )
)

Every time you select a user, the Dropdown will check if this user has already clocked in today.

Set Dropdown Items property:

SortByColumns(Test,"FullName").FullName

Hope it could help.

Regards,

Qi