How to update Hidden lookup column without creating data cards ?

[luuminhvuong93]

Dear all,

For example I connect form to a sharepoint list,

So column A , I want user to select from dropdown list, then B column is lookup from another table to get result.

Thus column B datacard is currently hidden,

In my list I have many column like this, and I don;t want to create data card for every column and then hide it,

Is there a way to still update these columns without having to create data cards for all of them?

[yashag2255]

Hey @luuminhvuong93 

If you have multiple fields where you don't want to take input from the user and refer to another table for value, you can use Patch command.

There could be 2 ways to tackle this scenario:

1) If you are using EditForm, as per its configurations you have to define the properties of all the required attributes, this is mapped by the Update property of each datacard. For this, you can hide the data cards as you are doing currently, and map the fields. At the end, SubmitForm() reads the value in the update property and uses it to either create/modify record.

2) If you want to modify just few fields, instead of using edit form, you can have individual controls on your screen which accepts certain user inputs and finally you can create a button that patch the record into your data source.

Expression:

Patch(DataSource, Defaults(DataSource),{Field1:"Value1",Field2:"Value2"})
Patch(DataSource, Lookup(DataSource,Field1 = "Test"),{Field1:"Value1",Field2:"Value2"})

Here, DataSource refers to my datasource, please replace with your actual name. The first expression creates a new record while the second one edits an already existing record.

For more information about the patch command, please refer: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-patch

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[luuminhvuong93]

@yashag2255  Thank you for suggestion,

with Patch you have to specify very detailed column....SubmitForm just save me the time with too many columns too update,

In addition when you use Patch, whow can you get the Last Submit item ?

[yashag2255]

@luuminhvuong93 

Yes for patch you have to define a required set of attributes but this is the only way moving forward with it, while if you want to perform operations using an Edit Form, you don't have to worry about formulas. This decision totally depends on your use-case.

Their is no built-in function that gets back the value of last submitted item using patch. But as a workaround, you can collect the details of specific record while patching the record, maybe set a variable or collect a collection and then use these values wherever you want to do a lookup/filter. 

Hope this Helps!

[v-xida-msft · Accepted solution]

Hi @luuminhvuong93 ,

Do you want to update the LookUp column in your SP list without enabling it within the Edit form?

Do you want to update the LookUp column for same record (the last submitted record) the Edit form submitted previously?

Based on the needs that you mentioned, I think the OnSuccess property of the Edit form and the Patch function could achieve your needs.

I have made a test on my side, please take a try with the following workaround:

Set the OnSuccess property of the Edit form to following:

Patch(
         'YourSPList',
          EditForm1.LastSubmit,   /* <-- Capture the last submitted record */
          {
            LookUpColumn: {
                              Id: LookUp(ReferencedList, ReferencedColumn = "A specific value", ID),
                              Value: "A specific value"
                          }
          }
)

Note: The LookUpColumn references values from the ReferencedColumn in your ReferencedList.

Or

Patch(
         'YourSPList',
          LookUp('YourSPList', ID = EditForm1.LastSubmit.ID),   /* <-- Capture the last submitted record */
          {
            LookUpColumn: {
                              Id: LookUp(ReferencedList, ReferencedColumn = "A specific value", ID),
                              Value: "A specific value"
                          }
          }
)

When you press "Submit" button to submit your Form data successfully (using SubmitForm() function), the LookUpColumn would also be updated immediately.

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

Best regards,

[Anonymous]

Hi @luuminhvuong93 how are you progressing with this? Were the above replies helpful?

@Anonymous