cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Excel. Can't display name of person instead of ID.

I am using an Excel file on OneDrive.  It has two seperate tables, one for people (first name, their photo, email, etc) and the other table contains appointments for these people.

 

There's a field called "ID" which is unique to the person in both tables.

 

Under a screen, I can display the value of the "ID" field, but not their first name.

 

Help!

10 REPLIES 10
Highlighted
Helper II
Helper II

Highlighted
Community Champion
Community Champion

Hi

 

When you wish to display the First Name of the particular ID, you have to use

LookUp,

 

Please provide more info in order to clarify in more details.

 

Have a nice day.

hpkeong
Highlighted
Regular Visitor

What would the formula be?

 

I am just realizing that I dont want a text box, but a drop down that is connected to a different table and calls the ID of the patient I want.  But instead of showing the ID, I want it to display the first name, middle name and last name (all seperate fields)  of the person that has the appointment.

Highlighted
Community Champion
Community Champion

Hi

 

If you wish to use DropDown, then please always remember to use DISTINCT (DS< FieldName).

Example:

Distinct(Datasource, firstname),

Distinct(Datasource, middlename), etc.

 

Hope this helps.

 

Have a nice day and g9

hpkeong

How to display the name in the dropdown field, name comes from an excel table, and this dropdown on a card inside Form.

But when submitting, I write in the table the unique_id value of the person, not the person's name.

 

I have noticed that dropdown can only show a value. This is true?

 

Filter returns a table? And so Distinct selects only one field. that's how it works?

Highlighted

Hi Nya,

 

In order to accomplish this, we need to make a new collection based off your existing data. You can attach the following logic to the "OnVisible" property of the screen in which you want to use it:

ClearCollect(FullNames, DropColumns(AddColumns(People, "FullName", Concatenate(FirstName, " ", MiddleName, " ", LastName)),"FirstName", "LastName", "MiddleName", "email")) 

Even though we're calling "DropColumns()" and "AddColumns()", the data source will not be modified. These functions return new tables.

 

To summarize, this command:

  1. Creates a new column "FullName" that is the concatenation of a given record's "FirstName", "MiddleName" and "LastName" fields.
  2. Drops all the columns that aren't "ID" or our newly created "FullName". You could skip this step if you wanted to, but this will ensure we don't have a bunch of unneeded data in our new collection. Please note that the names of the columns need to be surrounded in quotation marks here. (Ex: "FirstName" instead of FirstName)
  3. Assigns the modified table to a collection called "FullNames".

 

At this point, we can create a drop down, let's call it "PatientSelect", and set its "Items" property to our newly created collection "FullNames". You should set its display value to "FullName".

 

We can then reference appointments for this selected patient by using something like the following:

Filter(Appointments,PatientID=PatientSelect.Selected.ID)

This is a simple filter matching the PatientID of any given appoint with the ID corresponding to the displayed "FullName" of the patient selected in the drop down. Note that the ID here will be the same as it was in the original "People" table, so it will match to your "Appointments" table.

 

Let me know if you have any other questions,

  Maxwell, PowerApps-Staff

Highlighted

Hi Maxwell

 

I'm trying to understand how to do with unique_id (primary key), reference data from another table to show in a gallery / form.

 

I posted my question at: https://powerusers.microsoft.com/t5/PowerApps-Forum/How-to-show-in-the-gallery-something-selected-in...

 

Also, how to make the field in the field show the name of the area of the house, and chosen the location of the house show the items related to this location. But when writing the data to the worksheet, write the unique_id (not the name of the house area)

 

It is possible for you or someone to make an application / tutorial and make it available.

 

In this link has the model spreadsheet I'm trying to learn how to deal with id and references and other columns.

https://1drv.ms/x/s!AijLzG3PzbarfcNfbmMVkoQdLX0

 

Because I'm thinking that this reference would only be possible using the CDS

Lookup relationships
https://powerapps.microsoft.com/en-us/tutorials/data-platform-intro/

Custom Picklists
https://powerusers.microsoft.com/t5/PowerApps-Ideas/Custom-Picklists/idi-p/10529

 

Or via CDS with the relationship tab would be the correct one to do this relationship between tables?

 

Thank you for listening.

Highlighted

Hey AvenaFernando,

 

  I've replied to your original thread with an answer. If you've got any questions about it, please respond there and I'll try to answer them.

 

Thanks,

  Maxwell, PowerApps-Staff

Highlighted
Frequent Visitor

I am having this same issue. In the below example I have two tables, Clients and Hospital. I created a relationship between them. If I set the Value 1 to HospitalID, the HospitalName field is populated with the values but only in ID format. When I set it to HospitalName (like in the below image) I get nothing back. How do I get it to show the name and not the ID in the Hospital Name dropdown?

 

errormessage2.jpg

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (9,664)