cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MrAutomate
Helper I
Helper 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

View solution in original post

2 REPLIES 2
rahulswimmer
Helper I
Helper I

@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

View solution in original post

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

PA Community Call

Power Apps Community Call

Next call is happening on April 21st at 8a PST.

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors
Users online (59,463)