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

Autofill a sharepoint column based on data from another sharepoint list

I have a sharepoint list "Timesheet" that contains timetracking information like "Created By", "Client" and time entries for the week.

 

I have a second sharepoint list "Resource Clients" that needs to contain a Resources Name(Person type) and the Client that they are currently assigned.

 

I have a PowerApps app that takes in the information through a form and updates the first list with time entries.

 

What I am trying to do is make the Timesheet lists Client column to autopopulate with information from the Resource Clients list. When the user uses the PowerApps form they should not have the option to select a Client and this should be a display only option in PowerApps that will then autopopulate the Timesheet after submitting a new entry.

 

If anyone could advise how I might approach this it would be great thanks.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Autofill a sharepoint column based on data from another sharepoint list

Hi @JFexp ,

Do you want to auto-populate the Client column in your Edit form (which connects to your "Timesheet" list) with values from the Client column in your "Resource Clients" list?

 

Based on the needs that you mentioned, I have made a test on my side, please take a try with the following workaround:

Firstly, you need to add your 'Resource Clients' list as data source into your app. More details about adding a data source into an app, please check the following article:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/add-data-connection

 

Then within your Edit form (EditForm1), unlock the "Client" Data card, set the DisplayMode property to following:

DisplayMode.Disabled

Set the Default property of the Text Input box within the Client Data card to following:

If(
    EditForm1.Mode = FormMode.New,   /* <-- EditForm1 represents the Edit form in your app */
    LookUp('Resource Clients', 'Resources Name'.Email = User().Email, Client),
Parent.Default )

Or

If(
    EditForm1.Mode = FormMode.New,   /* <-- EditForm1 represents the Edit form in your app */
    LookUp('Resource Clients', 'Resources Name'.Email = User().Email).Client,
ThisItem.Client )

Please consider take a try with above solution, then check if the issue is solved.

 

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

5 REPLIES 5
Super User
Super User

Re: Autofill a sharepoint column based on data from another sharepoint list

@JFexp 

Will your "Resource Clients" list only have one record for that person?  I have a feeling that there might be many records in that list that would meet the criteria of the Resource Name.

 

If not, then it's pretty straight forward - you can have in your EditForm the client DataCard (which you most likely already have).  You didn't mention what type of column the Client column is in either list, so I will assume it is text.

With that in mind, you can set the DataCard's DisplayMode to View to keep it from being edited and then set the Default property to the following:

If(Text(yourEditFormName.Mode)=1,  // are we creating a new record?
Lookup('Resource Clients',
'Resources Name'.Email = User().Email).Client, //new record, get client column from other list
ThisItem.Client // existing record, display the existing record column
)

This will then display the existing Client column (if it's not a new record), or the client column from the 'Resource Clients' list if it is a new record.

 

I hope this is helpful for you.  There were some assumptions that were made in the above, so, if they do not reflect what you have and you have trouble altering for your conditions, then post back.

 

 

_____________________________________________________________________________________
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.
JFexp
Level: Powered On

Re: Autofill a sharepoint column based on data from another sharepoint list

Hi @RandyHayes 

 

The 'Resource Clients' list will have one client per person yes. The Resource Name column is a Person or Group Type and all users will have unique emails but there may be some names which will be the same.

 

You are correct Client column is Text column in both lists.

 

I'm having an issue that after the Lookup it cannot find the 'Resource Client' list from the options available.

 

The Client will always need to be displayed and then inputted to the new entry I am creating with the form.

 

I'm quite new to PowerApp so apolgies if I'm not explaining this very well. Thanks for your help.

Super User
Super User

Re: Autofill a sharepoint column based on data from another sharepoint list

@JFexp 

All is good then...the formula I gave you should do what you are looking for. 

Although it seems you might be having some issues:


I'm having an issue that after the Lookup it cannot find the 'Resource Client' list from the options available.

Can you explain what you mean by that statement?  Also, how many records does your "Resource Clients" list have in it?

 

_____________________________________________________________________________________
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.
Community Support Team
Community Support Team

Re: Autofill a sharepoint column based on data from another sharepoint list

Hi @JFexp ,

Do you want to auto-populate the Client column in your Edit form (which connects to your "Timesheet" list) with values from the Client column in your "Resource Clients" list?

 

Based on the needs that you mentioned, I have made a test on my side, please take a try with the following workaround:

Firstly, you need to add your 'Resource Clients' list as data source into your app. More details about adding a data source into an app, please check the following article:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/add-data-connection

 

Then within your Edit form (EditForm1), unlock the "Client" Data card, set the DisplayMode property to following:

DisplayMode.Disabled

Set the Default property of the Text Input box within the Client Data card to following:

If(
    EditForm1.Mode = FormMode.New,   /* <-- EditForm1 represents the Edit form in your app */
    LookUp('Resource Clients', 'Resources Name'.Email = User().Email, Client),
Parent.Default )

Or

If(
    EditForm1.Mode = FormMode.New,   /* <-- EditForm1 represents the Edit form in your app */
    LookUp('Resource Clients', 'Resources Name'.Email = User().Email).Client,
ThisItem.Client )

Please consider take a try with above solution, then check if the issue is solved.

 

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

JFexp
Level: Powered On

Re: Autofill a sharepoint column based on data from another sharepoint list

@RandyHayes  @v-xida-msft 

 

Thank you both for your assistance.

 

I had forgotten to add the data source for my second list and that was why it wasn't finding it previously.

 

After some playing around I realised that the Sharepoint email 

'Resources Name'.Email

does not match that of the user

User().Email

and was causing issues along with my Client column in the Timesheet list which I had set up as a Choice type but have now chnaged this to a Single Line Text.

 

I have had to compromise and will settle with matching it based on the DisplayName of the Sharepoint account and the FullName of the User:

 

If(
    EditForm1.Mode = FormMode.New,
    LookUp('Resource Clients', 'Resource Name'.DisplayName = User().FullName).Client,
    ThisItem.Client
)

Thanks again for the quick resolution of my issues greatly appreciated.

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
Users Online
Currently online: 187 members 6,052 guests
Please welcome our newest community members: