Saving Data to Excel

[SamantaBoodram]

Hi,

I am new to using PowerApps, my goal is to build an app that can store data locally offline until a connection is established and then save the data to my onedrive excel file. I created a form and used collections SAVEDATA to save the data to the collections. I then used the export function to save the data on the device until internet connectivity is established however I am unable to save the collection online once I am connected.

[wyotim]

Would you mind sharing the code you are using to save the collection once you are online? I'd be happy to help troubleshoot it.

And just to maybe help clarify some things, SaveData saves the data to your device to be used by that same app on that same device. Export allows for moving data between apps so if you don't need to do that you may be okay just using SaveData.

[SamantaBoodram]

I do not have any code for the Save Data that's where I need the help. I am able to collect the data offline and save it as a collection but I need to transfer it to an excel format once online.

[wyotim · Accepted solution]

Gotcha. If you are simply adding new items to the Excel file, your save code could be as simple as:

Collect(
    ExcelTableName,
    CollectionName
);
Clear(CollectionName)

where ExcelTableName is the name of the table you are writing to and CollectionName is the name of the collection of items to add. The Collect function will write each item in CollectionName to ExcelTableName and the Clear function empties the collection so that those items aren't added again.

Also, you would need a LoadData function somewhere like the OnStart property in the event that the user closes and reopens the app (if you don't already have that). Let me know if that gets you headed in the right direction or if I can help further.

[SamantaBoodram]

How can I update an exisiting record? let say I have a record on my excel datasource and i edit the data offline and I have it in my collections how can i update it instead of creating a new record?

[wyotim]

One direct way to update a record is to use the Patch function. Here is an example:

Patch(
    ExcelTableName,
    *code that distinguishes a single record*,
    {
        FieldToUpdate: UpdatedInfo,
        OtherFieldToUpdate: OtherUpdatedInfo
    } 
)

where 

• ExcelTableName is the name of the Excel table you are saving to. This could also be a collection or whatever data source you are writing to.
• *code that distinguishes a single record* is just that, something that distinguishes the record you want to update. This could be a LookUp(...) statement, a First(Filter(...)), even a Gallery.Selected statement if you are viewing the items in a gallery. 
• FieldToUpdate and OtherFieldToUpdate are the fields to be updated and UpdatedInfo and Other UpdatedInfo are the actual data to be put in those fields.

You can also use Patch to create a new record incidentally. To do this, the *code that distinguishes a single record* would be Defaults(ExcelTableName). You would then need to have every field that you are wanting to add in the curly brackets. 

Let me know if that helps out or if I can give more info!

[SamantaBoodram]

Can you explain the following? I am still not clear on what I need to add

• *code that distinguishes a single record* is just that, something that distinguishes the record you want to update. This could be a LookUp(...) statement, a First(Filter(...)), even a Gallery.Selected statement if you are viewing the items in a gallery. 

[wyotim]

Certainly!

Basically, you need to define a single record (like a single table row) so that the Patch knows which record to do its magic on. You can do this in a number of ways, and it can depend on how you are selecting that record (as in are you using a gallery or some other reference to the record).

Let's assume you are using a gallery named Gallery1 and you have selected the item to be updated. One easy way to reference that record would be to use the following:

Gallery1.Selected

Now, this assumes that you have complete records connected to the gallery that match the table you are writing to (as opposed to parts of a record, like if you dropped columns to save drive space or something like that).  

If you have altered the record but still have some identifying information, like an ID or something like that, using a LookUp function is another great way to reference a record. This might look like the following:

LookUp(
    ExcelTableName,
    ID = Gallery1.Selected.ID
)   

 Or if you don't have an ID but can use two or more values to identify the record, you might do something like this:

LookUp(
    ExcelTableName,
    Value1 = Gallery1.Selected.Value1 && Value2 = Gallery1.Selected.Value2
)

So to put it all together, your Patch statement would look like:

// First method
Patch(
    ExcelTableName,
    Gallery1.Selected,
    {
        FieldToUpdate: UpdatedInfo,
        OtherFieldToUpdate: OtherUpdatedInfo
    } 
)

Or:

// Second method
Patch(
    ExcelTableName,
    LookUp(
        ExcelTableName,
        ID = Gallery1.Selected.ID
    ),
    {
        FieldToUpdate: UpdatedInfo,
        OtherFieldToUpdate: OtherUpdatedInfo
    } 
)

Or:

// Third method
Patch(
    ExcelTableName,
    LookUp(
        ExcelTableName,
        Value1 = Gallery1.Selected.Value1 && Value2 = Gallery1.Selected.Value2
    ),
    {
        FieldToUpdate: UpdatedInfo,
        OtherFieldToUpdate: OtherUpdatedInfo
    } 
)

I hope that gets you headed in the direction you want to go! If not, I am happy to try and explain better or you could give some examples of what you have on your end and I can write some code statements that will be tailored to your specific situation. Just let me know!

[SamantaBoodram]

Let me explain, I have App which has the attached lines, I can edit the file but it creates a new line instead of updating the current Farm. If I edit the first person and I add the age as 10 then a new line is saved with just the value 10.

[SamantaBoodram]

The data would be in a collections called Farms and I need to update the Excel farm with the data