cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
seraph787
Level: Powered On

Textbox with collection field data (multiple records that match criteria in one textbox)

Maybe I need to go back to the drawing board with the way my data source is set up, but I thought I'd check with you guys first.

 

I have a collection of records that are all tied to employees in our organization.  Those records contain parts of that employee's affilliation. e.g. 

 

Employee database:

empIDNameOther data fields ->
1John Doe 
2Jane Smith 
3James Doeson 

 

Affilliation Database

affIDempIDaffilliationTypeaffilliationDescriptionprecedence
13UnitQuality Control2
22DivisionMedical Education4
33ProgramTB Program3
43TitleConsultant1
51BureauPublic Works5
61TitleDirector1
71ProgramWater & Power3
82TitleChief1

 

I have a gallery that shows the Employees in the Employee database and when the user selects one of the employees, I want a textbox to display the affilliation data for that person.

 

e.g. Selecting staff would display, as exemplified below for each:

John Doe
Director
Water & Power
Public Works
Jane Smith
Chief
Medical Education
James Doeson
Consultant
Quality Control
TB Program

 

 

4 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Textbox with collection field data (multiple records that match criteria in one textbox)

@seraph787 

I assume you want "line feeds" between each item...

If so, then this formula would work for you:

 

Set your Text property:

   Concat(SortByColumns(Filter('Affiliation Database', empID=Galleryx.Selected.empID), "precedence", Ascending), affliationDescription & chr(13))

 

Of course, adjust your fields and control names to your app, but this will give you the results you want.

 

Hope that helps.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

Dawidvh
Level 10

Re: Textbox with collection field data (multiple records that match criteria in one textbox)

Hi @seraph787 

 

From the below it seems like you datasources are setup properly and shouldn't require to be recreated.

 

There might be two options:

a) Create a label and use a filter and concat

Concat(Filter(AffiliationDb,empID=Gallery1.Selected.empID),affilliationDescription,", ")

b) Use a second gallery, use filter without concat and style it to whatever you want. This seems like the more versatile option.

 

See attached app and let me know if you don't come right.

 

twitter: @dawidvh

youTube: https://www.youtube.com/channel/UCIMe_ErQZP5chbI9UyW0xLw

>>If you found this reply helpful, please mark this as the answer to close the topic and make it easier to find for other people with similar questions.<<

 

 

View solution in original post

Community Support Team
Community Support Team

Re: Textbox with collection field data (multiple records that match criteria in one textbox)

Hi @seraph787 ,

Could you please share a bit more about your scenario?

Do you want to display the corresponding affilliationDescription column data for the selected employee in your Gallery within the Text Input box?

 

I have made a test on my side, please take a try with the following workaround:

Set the Items property of the Gallery (Gallery1) to following:

'YourEmployeeDatabase'

Set the Mode property of the Text Input box to following:

TextMode.MultiLine

Set the Default property of the Text Input box to following:

Concat(
Filter( 'AffilliationDatabase',
empID = Gallery1.Selected.empID ),
affilliationDescription & Char(13)
)

More details about the Concat function in PowerApps, please check the following article:

Concat function

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Super User
Super User

Re: Textbox with collection field data (multiple records that match criteria in one textbox)

@seraph787 

Okay...a bit of a wrench in the works!!  That is important to know if it is important to this app.

 

So, here is what you need.  

 

Concat(
SortByColumns(
AddColumns(
Filter(AffiliationDatabase, empID=Gallery1.Selected.empID && precedence<90),
"innerID", empID),
"precedence", Ascending),

If(titleFor="Unit",
LookUp(AffiliationDatabase, empID=innerID && affilliationType="Unit").affilliationDescription,
affilliationDescription) & Char(13)
)

What's going on here?

 

First (working inner to outer), we are filtering the list based on the employee ID (empID) and filtering out any precedence items over 89  (empID=Gallery1.Selected.empID && precedence<90)

 

Then we are adding a column to the results of that because we need another ID to compare to later.  This is just the empID again.  ("innerID", empID(There is a disambiguation operator, but I have the darndest time getting that to be my friend, so I opt for AddColumns most of the time.)  

 

Then we sort all of the above by precedence - ("precedence", Ascending)

 

NOW...we are actually in the Concat formula and all the above has given us a datasource to Concat.

Here we ask it to concat all these records based on the formula below - paying attention to the titleFor column - if it has a value of Unit, then we lookup (in the same datasource a record that has the innerID we defined in our formulas above, and an affiliationType = "Unit", and we use the affiliationDescription as the value to Concat...otherwise, we use the affiliationDescription in the actual record.  And of course...wrap it all up with a Char(13) to give us a line break.

   If(titleFor="Unit",

     LookUp(AffiliationDatabase, empID=innerID && affilliationType="Unit").affilliationDescription,
affilliationDescription) & Char(13)

 

I hope this is clear and helpful.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

9 REPLIES 9
Super User
Super User

Re: Textbox with collection field data (multiple records that match criteria in one textbox)

@seraph787 

I assume you want "line feeds" between each item...

If so, then this formula would work for you:

 

Set your Text property:

   Concat(SortByColumns(Filter('Affiliation Database', empID=Galleryx.Selected.empID), "precedence", Ascending), affliationDescription & chr(13))

 

Of course, adjust your fields and control names to your app, but this will give you the results you want.

 

Hope that helps.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

Dawidvh
Level 10

Re: Textbox with collection field data (multiple records that match criteria in one textbox)

Hi @seraph787 

 

From the below it seems like you datasources are setup properly and shouldn't require to be recreated.

 

There might be two options:

a) Create a label and use a filter and concat

Concat(Filter(AffiliationDb,empID=Gallery1.Selected.empID),affilliationDescription,", ")

b) Use a second gallery, use filter without concat and style it to whatever you want. This seems like the more versatile option.

 

See attached app and let me know if you don't come right.

 

twitter: @dawidvh

youTube: https://www.youtube.com/channel/UCIMe_ErQZP5chbI9UyW0xLw

>>If you found this reply helpful, please mark this as the answer to close the topic and make it easier to find for other people with similar questions.<<

 

 

View solution in original post

Community Support Team
Community Support Team

Re: Textbox with collection field data (multiple records that match criteria in one textbox)

Hi @seraph787 ,

Could you please share a bit more about your scenario?

Do you want to display the corresponding affilliationDescription column data for the selected employee in your Gallery within the Text Input box?

 

I have made a test on my side, please take a try with the following workaround:

Set the Items property of the Gallery (Gallery1) to following:

'YourEmployeeDatabase'

Set the Mode property of the Text Input box to following:

TextMode.MultiLine

Set the Default property of the Text Input box to following:

Concat(
Filter( 'AffilliationDatabase',
empID = Gallery1.Selected.empID ),
affilliationDescription & Char(13)
)

More details about the Concat function in PowerApps, please check the following article:

Concat function

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Super User
Super User

Re: Textbox with collection field data (multiple records that match criteria in one textbox)

@seraph787 

Did any of the almost identical solutions help you out?  If so, please accept one as a solution for others to gain from.

The only thing different in them is I had noticed your sample was sorted by precedence...so, you will want to use the SortByColumns in the formula. 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
seraph787
Level: Powered On

Re: Textbox with collection field data (multiple records that match criteria in one textbox)

These are all fantastic solutions.  I've been working with the Concat() solutions you guys have provided and I realized that I neglected one small but very important detail when I was removing extra unnecessary details to post in my original thread.

 

In the above Affillitation Database table there should be one more column called "titleFor".

 

affIDempIDaffilliationTypeaffilliationDescriptionprecedencetitleFor
13UnitQuality Control2 
22DivisionMedical Education4 
33ProgramTB Program3 
43TitleConsultant1 
51BureauPublic Works5 
61TitleDirector1 
71ProgramWater & Power3 
82TitleChief1 
94TitleChief1Unit

10

4UnitResearch & Epidemiology92 
114ProgramOffice of Health Assessment3 
124DivisionQuality Control4 

 

The expected result for employee 4 would be:

Chief, Research & Epidemiology

Office of Health Assessment

Quality Control

 

My thoughts for the "Precedence" column are two-fold. 

1. The order of the titles

2. Visible or not Visible (any number over 89 should be invisible but could be shown via titleFor - e.g. one one line as indicated above)

Super User
Super User

Re: Textbox with collection field data (multiple records that match criteria in one textbox)

@seraph787 

So when I look at that I don't see a consistent pattern.

Employee 4 has a title, unit, Program, Division, but not all employees have those and some are different.

Is the pattern that I see, that all employees have titles and if the Title has a titleFor value, then the title would be the value of whatever that item is (but also associated with their ID)

 

Does this seem correct?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
seraph787
Level: Powered On

Re: Textbox with collection field data (multiple records that match criteria in one textbox)

Hi @RandyHayes ,

 

Yes, that's correct on both points.

1. Not all employees have all affilliation levels entered into the system and some higher level employees just have one or two, like Bureau or Agency (in reality, my database works with way more levels because we manage up to a departmental level, so in total we go from Title (with optional titleFor), Section, Unit, Program, Division, Bureau, Department, then Agency).  Because there are so many levels, that's why we have to choose the ones that are most relevant to each employee.

2. Yes, the titleFor needs to find a match within the same collection associated with both 'empID' and 'affilliationType'.

 

Thanks,

-M

Super User
Super User

Re: Textbox with collection field data (multiple records that match criteria in one textbox)

@seraph787 

Okay...a bit of a wrench in the works!!  That is important to know if it is important to this app.

 

So, here is what you need.  

 

Concat(
SortByColumns(
AddColumns(
Filter(AffiliationDatabase, empID=Gallery1.Selected.empID && precedence<90),
"innerID", empID),
"precedence", Ascending),

If(titleFor="Unit",
LookUp(AffiliationDatabase, empID=innerID && affilliationType="Unit").affilliationDescription,
affilliationDescription) & Char(13)
)

What's going on here?

 

First (working inner to outer), we are filtering the list based on the employee ID (empID) and filtering out any precedence items over 89  (empID=Gallery1.Selected.empID && precedence<90)

 

Then we are adding a column to the results of that because we need another ID to compare to later.  This is just the empID again.  ("innerID", empID(There is a disambiguation operator, but I have the darndest time getting that to be my friend, so I opt for AddColumns most of the time.)  

 

Then we sort all of the above by precedence - ("precedence", Ascending)

 

NOW...we are actually in the Concat formula and all the above has given us a datasource to Concat.

Here we ask it to concat all these records based on the formula below - paying attention to the titleFor column - if it has a value of Unit, then we lookup (in the same datasource a record that has the innerID we defined in our formulas above, and an affiliationType = "Unit", and we use the affiliationDescription as the value to Concat...otherwise, we use the affiliationDescription in the actual record.  And of course...wrap it all up with a Char(13) to give us a line break.

   If(titleFor="Unit",

     LookUp(AffiliationDatabase, empID=innerID && affilliationType="Unit").affilliationDescription,
affilliationDescription) & Char(13)

 

I hope this is clear and helpful.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

seraph787
Level: Powered On

Re: Textbox with collection field data (multiple records that match criteria in one textbox)

Hi @RandyHayes ,

 

This is incredibly helpful!  In addition to this issue, it has also solved another issue I had since I, too, find those disambiguation operators to be quite clunky to work with at times.

 

Thank you!

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,780)