Answered

Update and get data

(0) Share

Report

Posted on by adri_microsoft

On the same screen I want to enter data, load data and update.
Right now, I have a window in which I enter data (which is saved in an excel on One drive) and I want to retrieve the line from my excel sheet based on an ID. For this I use the following:

LookUp (MyExcelFile_1; ID = record.ID)

but it says "The data type is not supported". I have tried with Value () and Text () with no success.

In turn I want that when I edit a field, update the data in the excel sheet. But what it does is create a new row with data. How can I do?

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (4)

Answers (1)

EricRegnier 8,720 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

Hi @adri_microsoft, this channel is more related to Common Data Service (CDS). Suggest to try the Power Apps forum to get better support: https://powerusers.microsoft.com/t5/Building-Power-Apps/bd-p/PowerAppsForum1
Cheers

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!
Hi @adri_microsoft ,

Could you please share a screenshot about the error message in your LookUp formula?

For your LookUp formula, please make sure the record variable store the record from your Excel table, which you want to retrieve. As an fixed solution, you could consider add a Gallery (Gallery1) inside your app screen, then set the Items property of the Gallery to following:

MyExcelFile_1

then all available records from your Excel table would be shown up in your Gallery.

Note: I assume that you have formatted your Excel data as a Table already. And the MyExcelFile_1 is your Excel table.

Set the TemplateFill property of this Gallery to following:

If( ThisItem.IsSelected; RGBA( 255; 127; 80; 1 ); RGBA(0; 0; 0; 0) )

above formula would highlight the selected item in your Gallery.

If you want to display the detail info of the selected item in your Gallery, please consider add a Display form in your screen, set the Data source property of the Display form to MyExcelFile_1. Set the Item property to following:

LookUp(MyExcelFile_1; ID = Gallery1.Selected.ID)

For your second issue, how do you submit your data back to your data source? Using SubmitForm function or Patch function? If you use SubmitForm function, you need to add a Edit form in your app screen, and only when the Edit form Default Mode is in Edit mode, press the "Submit" button would update record in your data source. Otherwise, it would add a new entry into your data source.

If you use Patch function to submit data, please use the following Patch formula in OnSelect property of "Submit" button:

Patch( MyExcelFile_1; LookUp(MyExcelFile_1; ID = Gallery1.Selected.ID); { ColumnName: TextInput1.Text } )

Of course, if you think the above solution is complex, I think the Editable Gallery functionality could achieve your needs:

https://www.youtube.com/watch?v=Y5G5p84mD84

https://www.youtube.com/watch?v=gRAnpZJESiE

Please try above solution, then check if the issue is solved.

Regards,

Was this reply helpful? Yes No
adri_microsoft 30 on at

Like (0)

Report
Copy link

Link copied!

Thanks for your answer.

My problem is that in the same window I want to retrieve a record or update a record. That is, I have a window with four fields: name, surname, date and date and a SubmitForm button.
The mode of the form is FormMode.Edit and in each of the fields I have the Default field a ThisItem.Name, ThisItem.Surname, etc.
When I hit the save button it creates a new record with the ID field. So far good. Now, when I click on the form to edit those fields and click on save, it creates a new record for me.
If I close the application and click on the registry to edit, it does not recover the value, even though I put Set (record; ThisItem).

Thanks in advance.

Was this reply helpful? Yes No
Verified answer

v-xida-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @adri_microsoft ，

Do you want to use the same Edit form to create or update items in your data source?

If you want to use the Edit form to create new item in your data source, you should make sure the DefaultMode property of the Edit form is set to following:

FormMode.New

if you want to use the Edit form to edit an existing record in your data source, you must make sure the DefaultMode property of the Edit form is set to following:

FormMode.Edit

and set the Item property of the Edit form to following (bind it to specific item you want to update😞

LookUp(MyExcelFile_1; ID = Gallery1.Selected.ID)

So on your side, please connect the Edit form to your Excel table data source, and set the Item property of the Edit form to following:

LookUp(MyExcelFile_1; ID = Gallery1.Selected.ID) // bind to a speicific record you want to update, replace it with your own LookUp formula

Add a "New" button (click it to add new entry), set the OnSelect property to following:

NewForm(EditForm1)

Add a "Edit" button (click it to edit existing record), set the OnSelect property to following:

EditForm(EditForm1)

Please try above solution, check if the issue is solved.

Regards,

Was this reply helpful? Yes No