web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Save auto-populated va...
Power Apps
Answered

Save auto-populated value to SharePoint list based on selected value from another field

(1) ShareShare
ReportReport
Posted on by mikegarcia2023 24

Hello,

 

I have a SharePoint list (List1) that contains two lookup columns (number1 and name1) that point to another source list (List2) with two columns (name2 and number2) that are of type text. I have a form where the value for name1 auto-populates based on the selected value for number1. The issue that I am having is that the value from name1 does not get saved to the SharePoint list (list1).

 

Here is sample code from my DefaultSelectedItems:

If(EditForm2.Mode=FormMode.New || EditForm2.Mode=FormMode.Edit, LookUp('List2',
'number2' = DataCardValue31.Selected.Value)/*Result Value*/, LookUp('List1','anotherColumn'= DataCardValue35.Text).'name1')

 

The part I am struggling with is providing the correct result value for the first condition (see placeholder left as a comment). When I try different combinations I typically receive the "Expected Table value" error message. I believe this could be the main culprit behind the issue.

 

Note: the list and column names provided are used as examples.

 

Any help with this would be much appreciated!

 

Thanks,

Mike

 

Categories:
Building Power Apps
  • poweractivate Profile Picture
    poweractivate 11,078 Most Valuable Professional on at

    @mikegarcia2023 

     

    It seems your formula may be expecting a table but it's getting a record because the LookUp function returns a single record.

     

    To start, you need to confirm if your 'number1' field is indeed a lookup column in SharePoint.

    The reason being that in Power Apps, lookup fields are treated as records, not as single values, and they consist of multiple properties - Value (display name), Id (ID of the record), and @odata.type.

     

    In this case to compare 'number2' with 'DataCardValue31.Selected.Value', you should use 'DataCardValue31.Selected.Value' or 'DataCardValue31.Selected.Id', depending upon the specific requirement.

     

    Assuming 'number1' is a lookup column and you want to compare based on `Value`, try to adjust your formula to:

     

    If(
EditForm2.Mode=FormMode.New || EditForm2.Mode=FormMode.Edit,
LookUp(
List2,
number2 = DataCardValue31.Selected.Value
).name2,
LookUp(
List1,
anotherColumn= DataCardValue35.Text
).name1
)

     

    Replace the above with your actual names of the data source, etc.

    In this formula, we're getting 'name2' from List2 when the form is in New or Edit mode, otherwise, we're getting 'name1' from List1.

     

    If you are still encountering issues, you may need to revise your formula based on the exact structure of your lists and data types of your columns. Please provide more information if you still have issues.

     

    Hope this helps @mikegarcia2023 

     

  • mikegarcia2023 Profile Picture
    mikegarcia2023 24 on at

    Hi @poweractivate ,

     

    Thank you for your quick response. The information that you provided was very useful.

     

    I can confirm that my "number1" and "name1" columns are lookup data types that point to "number2" and "name2" which are text data types.

     

    I tried following this format:

    LookUp(
List2,
number2 = DataCardValue31.Selected.Value
).name2,

    It ends up displaying the "Expected Table value" error. I've tried it this way as well with no luck: 

    LookUp('List2','number2' = DataCardValue31.Selected.Value, 'name2')

     

    Kind regards,

    Mike

     

  • poweractivate Profile Picture
    poweractivate 11,078 Most Valuable Professional on at

    Hello Mike,

    Thanks for confirming the data types of your columns and providing additional information.

    It seems you're getting the "Expected Table value" error because your lookup function returns a single record while the 'DefaultSelectedItems' property expects a table of records.

    In Power Apps, you should use a formula to return a table with a single record when working with the 'DefaultSelectedItems' property of a Lookup field. Here is an example of how to modify your formula:

    If(
 EditForm2.Mode = FormMode.New || EditForm2.Mode = FormMode.Edit,
 Filter(
 List2,
 number2 = DataCardValue31.Selected.Value
 ),
 Filter(
 List1,
 anotherColumn = DataCardValue35.Text
 )
)

    This formula uses the Filter function instead of the LookUp function.

     

    The Filter function returns a table that contains all the records from a data source that satisfy a certain formula, which matches the expected input of the 'DefaultSelectedItems' property.

     

    In this case, it should return a table with a single record where 'number2' equals the selected value from 'DataCardValue31' (when the form mode is 'New' or 'Edit'), or where 'anotherColumn' equals the text from 'DataCardValue35' (for all other form modes).

     

    Please replace the data source and column names with your actual names and try this formula. Let me know if this resolves the issue.

     

  • mikegarcia2023 Profile Picture
    mikegarcia2023 24 on at

    Hi @poweractivate ,

     

    Thank you for providing additional information and an updated formula.

     

    I tested the modified formula and it appears to work similarly to the LookUp function where the value shows correctly on the form when in new/edit mode but it is still having trouble saving it to 'name1' in List1. I've attached a screenshot below for reference. 

    mikegarcia2023_0-1691013394236.png

     

    Please let me know if I can include any additional that might be helpful.

     

    Kind regards,

    Mike

  • poweractivate Profile Picture
    poweractivate 11,078 Most Valuable Professional on at

    Hello @mikegarcia2023 ,

     

    From the information you provided, it appears that the issue might not be with retrieving the data but rather with saving it back to 'name1' in List1.

     

    In Power Apps, Lookup columns are read-only, which means you can display the value from a Lookup column in a form, but you can't directly write to it.

     

    This behavior might explain why you can see the correct value in your form (because you are able to read from 'name1') but can't save changes back to it (because it's read-only).

     

    However, there's a workaround using the Patch function to update the original list in SharePoint with the selected values. The Patch function is used to modify single or multiple records of a data source.

     

    Here's an example of how you might use it:

    SubmitForm(EditForm2); 
Patch(
 List1,
 {
 ID: EditForm2.LastSubmit.ID,
 'name1': {
 Value: EditForm2.LastSubmit.name1.Value, 
 Id: EditForm2.LastSubmit.name1.Id
 }
 }
);

    In the first line, we use SubmitForm function to submit all the changes user made in the form. After that, we use Patch function to explicitly set the value of the 'name1' column.

     

    Please try this formula and let me know how it goes.

  • mikegarcia2023 Profile Picture
    mikegarcia2023 24 on at

    Hi @poweractivate ,

     

    That is correct. That explains why the value hasn't been able to save correctly.

     

    I believe you are on the right track with the Patch function approach. Thank you for the example. I attempted using it and the value was still unable to save, but I feel that we are closer to solving the issue. 

     

    One interesting discovery from this portion of code:

     'name1': {
 Value: EditForm2.LastSubmit.name1.Value, 
 Id: EditForm2.LastSubmit.name1.Id

    When I highlighted over the value and id towards the end, they are both equal to "Blank" even when the name shows on the form.

     

    I changed the Value part to this:

    Value: DataCardValue32.Selected.'name1'

     

    When highlighted this time around it showed the actual value of the name, but the id still shows blank. This might be a clue.

     

    Kind regards,

    Michael

  • Verified answer
    poweractivate Profile Picture
    poweractivate 11,078 Most Valuable Professional on at

    @mikegarcia2023 
    Michael,

    Yes, you are very close to the solution.

    The issue you're encountering, where the ID is blank, typically occurs if the field in SharePoint (List2 in your case) that you're trying to write back to is not a unique identifier field.

     

    In SharePoint, the lookup column (name1 in your case) requires the ID of the corresponding record in the referenced list (List2), not just the value.

     

    But in your Patch formula, you have set the value of 'name1' correctly, however the ID might not have been set correctly which could be causing the issue.

     

    If the 'name1' in List2 does not have a unique ID for each record, you may need to create an additional column in List2 that contains a unique ID for each record, then use that unique ID for the 'Id' in your Patch formula.

     

    Here is a revised Patch formula that assumes you have a unique ID column in List2:

    SubmitForm(EditForm2); 
Patch(
 List1,
 {ID: EditForm2.LastSubmit.ID},
 {
 'name1': {
 Value: DataCardValue32.Selected.'name1', 
 Id: LookUp(List2, name2 = DataCardValue32.Selected.'name1').ID
 }
 }
);

    In this updated formula, we're using the LookUp function to find the record in List2 where 'name2' is equal to the selected 'name1' from the form. We then use the ID of that record for the 'Id' in the Patch formula.

    Please replace the data source and column names with your actual ones and try this formula.

     

    Let me know if this resolves your issue @mikegarcia2023 

  • mikegarcia2023 Profile Picture
    mikegarcia2023 24 on at

    Hi @poweractivate ,

     

    This appears to have solved the issue!

     

    I can't express my gratitude enough with all the time and effort you put into helping me solve this. Thank you so much!

     

    Kind regards,

    Mike

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Congratulations to the August Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
11manish Profile Picture

11manish 381 Super User 2026 Season 2

#2
Mohsin Ali Profile Picture

Mohsin Ali 340

#3
WarrenBelz Profile Picture

WarrenBelz 187 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans