cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MrAutomate
Helper III
Helper III

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

Helpful resources

Announcements
Super User 2 - 2022 Congratulations 768x460.png

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

<
Users online (3,980)