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

[ClarkyPA365]

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

Form view

The submit button formula is 

[v-jefferni]

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.

[ClarkyPA365]

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

[v-jefferni]

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.

[ClarkyPA365]

Hi @v-jefferni 

Sorry not quiet understanding what you mean??🤔

[v-jefferni]

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.

[ClarkyPA365]

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

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

[v-jefferni]

Hi @ClarkyPA365 ,

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

[ClarkyPA365]

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 

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]

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.