cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Community Support
Community Support

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.
Highlighted
Advocate I
Advocate I

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

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

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

Top Solution Authors
Top Kudoed Authors
Users online (5,405)