cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate I
Advocate I

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
Community Support

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.

@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
News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

Power Apps Community Call

Power Apps Community Call- January

Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST

PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

Top Solution Authors
Top Kudoed Authors
Users online (6,778)