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

Update existing data in DataSource with Collection

Hi,

 

First, I need to mention that I'm new to PowerApps. I'm currently doing the following on my PowerApp solution (OneDrive Business | Excel):

 

Note: In Excel I have a column called GUID where I generate unique id as soon as new row/data is saved.

 

  • New Entry

    If Connection is available: Data is saved/Patched directly to DataSource and also saves a copy in Collection called CollectionSet
    If Connection is not available: 
    Data is saved in a new Collection called EntriesToBeSynced. As soon as there is a connection, the pending data is saved to DataSource and saves a copy in CollectionSet

 

If(Connection.Connected,
Patch(Master_tbl,Defaults(Master_tbl),
{
GUID:
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    "-" &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    "-" &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    "-" &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    "-" &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1),
First_name: DataCardValue8.Text,
Last_name: DataCardValue7.Text,
Date_of_birth: DateValue6.SelectedDate + Time(Value(00), Value(00), 0)
});
Navigate(Dashboard,ScreenTransition.None);
Refresh(Master_tbl);
ClearCollect(CollectionSet,Master_tbl);
SaveData(CollectionSet,"entriesInLocalStorage")
,
Collect(EntriesToBeSynced,
{
GUID:
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    "-" &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    "-" &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    "-" &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    "-" &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1) &
    Mid("0123456789ABCDEF", 1 + RoundDown(Rand() * 16, 0), 1),
First_name: DataCardValue8.Text,
Last_name: DataCardValue7.Text,
Date_of_birth: DateValue6.SelectedDate + Time(Value(00), Value(00), 0)
});
SaveData(EntriesToBeSynced,"newEntriesInLocalStorage");
Navigate(Dashboard,ScreenTransition.None))

 

  • New Data Sync

 

If(Connection.Connected && CountRows(EntriesToBeSynced) > 0,
Collect(Master_tbl,EntriesToBeSynced);
Clear(EntriesToBeSynced);
SaveData(EntriesToBeSynced,"newEntriesInLocalStorage");
Refresh(Master_tbl);
ClearCollect(CollectionSet, Master_tbl);
SaveData(CollectionSet,"entriesInLocalStorage");
Navigate(Dashboard,ScreenTransition.None))

 

 

  • Edit Data/Modify Data

    If Connection is available: New changes are saved/Patched directly to DataSource and also saves a copy in CollectionSet
    If Connection is not available: Data is saved in a new Collection called FirstSurveyToBeUpdated.

 

 

If(Connection.Connected,
Patch(Master_tbl,Browser.Selected,
{ 
First_name:DataCardValue2.Text,
Last_name:DataCardValue3.Text,
Date_of_birth:DateValue3.SelectedDate + Time(Value(00), Value(00), 0)
});
Navigate(Selection,ScreenTransition.None);
Refresh(Master_tbl);
ClearCollect(CollectionSet,Master_tbl);
SaveData(CollectionSet,"entriesInLocalStorage")
,
Collect(FirstSurveyToBeUpdated,
{
GUID: Browser.Selected.GUID,  
First_name: DataCardValue2.Text,
Last_name: DataCardValue3.Text,
Date_of_birth: DateValue3.SelectedDate + Time(Value(00), Value(00), 0)
});
SaveData(FirstSurveyToBeUpdated,"firstSurveysInLocalStorage");
Navigate(Selection,ScreenTransition.None)) 

 

 

Example:

 

Master_tbl____________________________________________________

995B2A63-F368-9142-433A-82FFCA412F65 | Sam | Smith | 09/07/1970

_____________________________________________________________

 

 

FirstSurveyToBeUpdated_______________________________________

995B2A63-F368-9142-433A-82FFCA412F65 | John | Smith | 09/07/1970

_____________________________________________________________

 

Question: How is it possible to update the existing data/row matching GUID in DataSource with FirstSurveyToBeUpdated?

 

Thanks

2 REPLIES 2
Community Support Team
Community Support Team

Re: Update existing data in DataSource with Collection

Hi @Kevin_Maxwell,

 

Do you want to use the Collect function to update the existing row matching the GUID in your datasource with the FirstSurveyToBeUpdated collection?

 

If you want to use the Collect function to update the existing row matching the GUID in your datasource with the FirstSurveyToBeUpdated collection, I afraid that there is no way to achieve your needs in PowerApps currently.

 

The Collect function only supports adding records to a data source. More details about the Collect function, please check the following article:

Collect function in PowerApps

 

If there is only one record in your FirstSurveyToBeUpdated collection, you could consider take a try with following formula to update the existing row matching the GUID in your datasource:

Patch(Master_tbl,LookUp(Master_tbl,GUID=First(FirstSurveyToBeUpdated).GUID),First(FirstSurveyToBeUpdated))

 

If there are multiple records within your FirstSurveyToBeUpdated collection, I afraid that there is no way to achieve your needs in PowerApps currently.

 

More details about Patch function and LookUp function, please check the following article:

Patch function

 

LookUp function

 

 

Best regards,

Kris

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

Re: Update existing data in DataSource with Collection

@v-xida-msft Thanks for the comprehensive reply. It seems the only way would be Patch. However, by using Patch during the Submit form, I will not have the Validation call since there is no Submit function anymore. Because by default Submit(Form) validates the form. How can I validate Required fields before Patch in this case?

Helpful resources

Announcements
Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Microsoft Business Applications Virtual Launch

Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (8,649)