cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Update and get data

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Update and get data

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,

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

4 REPLIES 4
Highlighted
Super User II
Super User II

Re: Update and get data

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

Highlighted
Community Support
Community Support

Re: Update and get data

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,

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.
Highlighted
Regular Visitor

Re: Update and get data

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.

Highlighted
Community Support
Community Support

Re: Update and get data

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,

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Users online (7,148)