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

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
AvenaFernando
Level: Powered On

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

hpkeong
Level 10

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

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
Nya
Level: Powered On

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

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.

hpkeong
Level 10

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

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
AvenaFernando
Level: Powered On

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

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?

Microsoft Employee

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

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

AvenaFernando
Level: Powered On

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

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.

Microsoft Employee

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

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

rdevine
Level: Powered On

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

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
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,854)