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

[seraph787]

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:

empID	Name	Other data fields ->
1	John Doe
2	Jane Smith
3	James Doeson

Affilliation Database

affID	empID	affilliationType	affilliationDescription	precedence
1	3	Unit	Quality Control	2
2	2	Division	Medical Education	4
3	3	Program	TB Program	3
4	3	Title	Consultant	1
5	1	Bureau	Public Works	5
6	1	Title	Director	1
7	1	Program	Water & Power	3
8	2	Title	Chief	1

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

[RandyHayes · Accepted solution]

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

[Dawidvh · Accepted solution]

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

[v-xida-msft · Accepted solution]

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,

[RandyHayes]

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

[seraph787]

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

affID	empID	affilliationType	affilliationDescription	precedence	titleFor
1	3	Unit	Quality Control	2
2	2	Division	Medical Education	4
3	3	Program	TB Program	3
4	3	Title	Consultant	1
5	1	Bureau	Public Works	5
6	1	Title	Director	1
7	1	Program	Water & Power	3
8	2	Title	Chief	1
9	4	Title	Chief	1	Unit
10	4	Unit	Research & Epidemiology	92
11	4	Program	Office of Health Assessment	3
12	4	Division	Quality Control	4

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)

[RandyHayes]

@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?

[seraph787]

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

[RandyHayes · Accepted solution]

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

[seraph787]

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!