cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MrAutomate
Advocate I
Advocate I

Filtering help

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

Data Source:Relevant Column Name:Sharepoint Datatype:Notes
tSheets21:Teammate,
2: DATE (the date the clock in occurred)
Single line of textThis is a time sheet tool.
ACTIVETeammatesFullNameSingle line of textI 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!

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-qiaqi-msft
Community Support
Community Support

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

Best Regards,
Qi

View solution in original post

2 REPLIES 2
rahulswimmer
Helper II
Helper II

@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
Community Support
Community Support

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

Best Regards,
Qi

View solution in original post

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (1,006)