How to pull data out of a combobox using the clearcollect function

(0) Share

Report

Posted on by Aurora

364

Hi all,

As the title states, I am looking for a way to grab a line of text out of a Combo box. In an app I have developed, a collection is used to gather data like an employee's name, email, the number of vacation hours they have available, and the number of sick hours they have available. Below is the formula I am currently using:

ClearCollect(ManagerTimeAdjustment,
{MEmployeeEmail: DataCardValue2.Text,
MEmployeeName: DataCardValue1.Text,
MYear: Text(Year(Now())),
MVacationUsed2: Sum(Filter(Vacation_Request,vacationRequester = DataCardValue2.Text,vacationStatus.Value = "Approved", Vacation_x0020_Type.Value = "Vacation"), totalRequestedTime),
MVacationAvailable1: DataCardValue8.Text,
MSickLeaveUsed2: Sum(Filter(Vacation_Request,vacationRequester = DataCardValue2.Text,vacationStatus.Value = "Approved", Vacation_x0020_Type.Value = "Sick Leave"), totalRequestedTime),
MSickLeaveAvailable1: DataCardValue13.Text});

I would like to replace "DataCardValue2" with the value stored in ComboBox1, the combobox contains the emails of every employee at my company, so a manager can make view data for any employee they select. I haven't been able to figure out how to do this yet, I know that the combobox contains "values" and not text, but so far, I can't seem to figure out how to convert that value to text.

I can provide more info if needed.

I appreciate any help!

Categories:

General Questions

All responses (6)

Answers (0)

Aurora 364 on at

Like (0)

Report

Re: How to pull data out of a combobox using the clearcollect function

@RandyHayes
ComboBox1: Filter(_USER, IsEmployed.Value = "Active")

Gallery8: LookUp('AUNA Time Off', Email= User().Email)

Gallery8 displays the hours for vacation and sick leave that employees have, but it isn't a calculated value. For example, I have 80 hours of vacation and 27 of sick leave, so when the app starts, the numbers "80" and "27" are stored in my gallery. I then load the data into a different gallery with this in the text field of a label:
Round(Sum(ThisItem.SickLeaveAvailable1 - ThisItem.SickLeaveUsed1), 1)& If(Abs(ThisItem.SickLeaveAvailable1 - ThisItem.SickLeaveUsed1)=1, " Hour", " Hours")

With the way the gallery8 works, I don't think it would be able to help with what I am currently trying to do, because the value in it is always associated with User().Email. I need a manager to be able to view any employees hours, not just their own.

I can change the first collection to a variable if you think that's more efficient, but I still need to get this figured out as well.

Thanks for all your help
RandyHayes 76,287 Super User 2024 Season 1 on at

Like (0)

Report

Re: How to pull data out of a combobox using the clearcollect function

@Aurora

So again, with that particular formula for the TimeAdjustment collection - you are using a collection again to store static information for only one record. This is not effective for a collection!

Two things I need more info on:

1) What is the Items property of ComboBox1?

2) What is the Items property of Gallery8 (and what is Gallery8 for?)
Aurora 364 on at

Like (0)

Report

Re: How to pull data out of a combobox using the clearcollect function

@RandyHayes
The data that goes into vacationRequestor is a single line of text from a SharePoint site called _USER that we use to store data for all employees. The combobox is not multi-select, only one person's email can be selected at a time. When a user opens the app, I have a gallery that displays how much available time they have off that uses the function:

ClearCollect(
TimeAdjustment,
{ EmployeeEmail: User().Email,
EmployeeName: User().FullName,
Year: Text(Year(Now())),
VacationUsed1: Sum(
Filter(
Vacation_Request,
vacationRequester = User().Email,
vacationStatus.Value = "Approved",
'Vacation Type'.Value = "Vacation"),
totalRequestedTime),
VacationAvailable1: Gallery8.Selected.VacationHours,
SickLeaveUsed1: Sum(
Filter(
Vacation_Request,
vacationRequester = User().Email,
vacationStatus.Value = "Approved",
'Vacation Type'.Value = "Sick Leave"),
totalRequestedTime), SickLeaveAvailable1: Gallery8.Selected.SickHours});

And this shows people the proper number of hours they have available. What I need is a way for a manager to switch between users in the app, so they can see how much time is available to each employee if that manager needs to submit a request for them for whatever reason (Like if they are sick and can't submit a request themselves). I got the formula you shared a bit father, but now I can't compare the text record in my SharePoint list to the table value generated by the variable. The column of emails is just called "Email"

Do you know how I can compare these?
RandyHayes 76,287 Super User 2024 Season 1 on at

Like (0)

Report

Re: How to pull data out of a combobox using the clearcollect function

@Aurora

What kind of column is vactaionRequester in your datasource?

You are also trying to compare to .SelectedItems - that is a Table...that will never compare.

Is the combobox multi-select? And if so, how does that fit into your design?
Aurora 364 on at

Like (0)

Report

Re: How to pull data out of a combobox using the clearcollect function

@RandyHayes
I can't compare vacationRequester to my list of employee emails in the combobox. If I use combobox1.selected.mail, it tells me that .mail isn't a valid identifier. This was my original issue; I don't know how to compare that combobox value to text.

Any ideas?
RandyHayes 76,287 Super User 2024 Season 1 on at

Like (0)

Report
Re: How to pull data out of a combobox using the clearcollect function

@Aurora

The use of a collection in your scenario is a bit of an overkill. A collection is an in-memory table that has capabilities to add/edit and delete records.

You are only setting ONE record in the table with static information.

You can place all of this in a variable, but even with that, it might be overkill as the controls you have referenced in your formula are all available to you any place that you are actually going to use that value.

So, if in a variable:

Set(varManagerTimeAdjustment, {MEmployeeEmail: ComboBox1.Selected.Mail, MEmployeeName: DataCardValue1.Text, MYear: Text(Year(Now())), MVacationUsed2: Sum( Filter(Vacation_Request, vacationRequester = ComboBox1.Selected.Mail, vacationStatus.Value = "Approved", Vacation_x0020_Type.Value = "Vacation" ), totalRequestedTime), MVacationAvailable1: DataCardValue8.Text, MSickLeaveUsed2: Sum( Filter(Vacation_Request, vacationRequester = ComboBox1.Selected.Mail, vacationStatus.Value = "Approved", Vacation_x0020_Type.Value = "Sick Leave" ), totalRequestedTime), MSickLeaveAvailable1: DataCardValue13.Text } )

Since you are making 2 additional data actions in the formula, you can refine to the following for better performance:

With({_empInf: Filter(Vacation_Request, vacationRequester = ComboBox1.Selected.Mail, vacationStatus.Value = "Approved" ) }, Set(varManagerTimeAdjustment, {MEmployeeEmail: ComboBox1.Selected.Mail, MEmployeeName: DataCardValue1.Text, MYear: Text(Year(Now())), MVacationUsed2: Sum(Filter(_empInf, Vacation_x0020_Type.Value = "Vacation"), totalRequestedTime), MVacationAvailable1: DataCardValue8.Text, MSickLeaveUsed2: Sum(Filter(_empInf, Vacation_x0020_Type.Value = "Sick Leave"), totalRequestedTime), MSickLeaveAvailable1: DataCardValue13.Text } ) )

This can then be referenced in your app as, for example the employee name: varManagerTimeAdjustment.MEmployeeName

However, also note that ANY place in your app you can reference DataCardValue1.Text to get the same.

I hope this is helpful for you.