cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ClarkyPA365
Post Patron
Post Patron

Concat 'Employee Numbers' from a SP site using 'Look up'

Hi,

I have a form that displays Concat data in a field which is selected from a Combobox.  

I know have Employee Records that are identified using the 'Look up' function.  It uses the Employees Email address.

How would I Concat the 'Employee Numbers' so they appear in the form and can be associated with the correct Employee and rest of data??

Gallery view

ClarkyPA365_0-1652706980500.png

 

Form view

ClarkyPA365_1-1652707039620.png

The submit button formula is 

ClarkyPA365_2-1652708248072.png

 



1 ACCEPTED SOLUTION

Accepted Solutions

Hi @ClarkyPA365 ,

 

It seems there are some limitations within this formula, so we need to separate it to two parts:

1. OnChange of the the Combo box we can create a collection to get the numbers:

ClearCollect(colNumbers,
            AddColumns(
                Combobox1.SelectedItems.Mail As EE,
                "EmployeeNumber",
                LookUp('Employee LookUp', Title = EE.Email, 'Employee Number')
           )
)

 

2. Default of the Text input to concatenate them together:

Concat(
       colNumbers,
       EmployeeNumber,
       "," & Char(10)
)

 

Hope this helps.

 

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

20 REPLIES 20
v-jefferni
Community Support
Community Support

Hi @ClarkyPA365 ,

 

Could you please share more details about your scenario? What is the Items property for that Combo box, is it from a Person column like Choices(List.PersonColumn) ?

 

If so, please try below formula:

Concat(
       AddColumns(
                Combobox.SelectedItems,
                "EmployeeNumber",
                LookUp(Employees, EmailAddress = ThisRecord.Email)
       ),
       EmployeeNumber,
       "," && Char(10)
)

 

Hope this helps.

 

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

Hi @v-jefferni 

The employee number is provided via a Lookup function.  Looking up a SP site so isn't part of the Combobox selection

ClarkyPA365_0-1652946258375.png

 



Hi @ClarkyPA365 ,

 

I am getting the employee number via looking up another list which is called "Employees". You can use the list name and column names from your end instead.

 

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

Hi @v-jefferni 

Sorry not quiet understanding what you mean??🤔

Hi @ClarkyPA365 ,

 

I was kind of combine the LookUp function in your screenshot with the Concat function in my post, just using the list column name I assumed.

 

I'm not sure about how you are selecting items then navigate to the form screen, and what is the Item of the form? I didn't see the Combo box but only a Gallery instead. I was assuming that you have a multiple selection Combo box where Items is a person column, and will get a employees table when several employees are selected. Using the formula in my post, you will be able to get a concatenated text which includes multiple employee's ID corresponding to the employees selected in the combo box.

 

As you provided the formula of the ID from in the Gallery, I can make the formula for IDs more specific:

Concat(
       AddColumns(
                Combobox.SelectedItems,
                "EmployeeNumber",
                LookUp('Employee LookUp', Title = ThisRecord.email, 'Employee Number')
       ),
       EmployeeNumber,
       "," && Char(10)
)

 

Hope this helps.

 

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

Hi @v-jefferni 

Used the formula provided but when adding in the part 'ThisRecord.email', email is not an option?

Screen shot from the SP list

ClarkyPA365_0-1652955491482.png

I've tried using some other options and they provide the following result

ClarkyPA365_1-1652955667047.png

 

 

Hi @ClarkyPA365 ,

 

Sorry for the typo, only one & is needed in that formula.

 

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

Hi @v-jefferni 

Th formula now works, partially??

It only display the last Employees Number.
The part of the formula 'ThisRecord' doesn't give the option of 'email'
I've tried the field names from the Gallery 

ClarkyPA365_0-1653034001798.png

Tried 'ThisItem' which does allow 'Email' but the result only displays ','.

As the field in the Gallery is not populated via the Office365User connector would 'ThisRecord' or 'ThisItem' work??

 

v-jefferni
Community Support
Community Support

Hi @ClarkyPA365 ,

 

ThisRecord would work only within functions that operate on a table, like AddColumns and ForAll. The ID in the Gallery is text format.

 

You have already get the concatenated IDs in your previous post, right? Only need to delete one of the And symbol from that formula. "&&" will be used for logical tests and will return true or false the boolean value. 

 

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

Helpful resources

Announcements
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.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

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