Offline / Collections Not Funnction Properly

[rkeeran76v2]

Hello,

I am having some issues with getting my app to work offline and then submit entries back to the SharePoint list once the user gets connection back. I am experimenting with some code that doesnt give me an error but doesnt seem to be working. I am not sure if I am having issues with the SaveData/LoadData, the collection or the connection piece in my code but needless to say it doesnt want to work.

Can you all take a look at my code below and let me know what this newbie is doing incorrectly?

OnStart:

LoadData(TempCollection,"temporary",true);
If(!IsEmpty(TempCollection.ColStatus),ForAll(TempCollection,Collect(Success,{id: Colid, Record:SubmitForm(EditForm1)})));
Clear(TempCollection);
SaveData(TempCollection,"temporary")

OnSelect (Submission Entry):

If(Connection.Connected,SubmitForm(EditForm1),
Collect(TempCollection,{
Colid: Max(TempCollection,Colid)+1,
ColStatus:DataCardValue6.Selected,ColTaskInfo:DataCardValue10.Text,ColPlant:DataCardValue9.Selected,ColTrailerFSUPP:DataCardValue32.Text,ColTractor:DataCardValue31.Text,ColEEScheduled:DataCardValue38.Selected,ColLoadingDock:DataCardValue26.Selected,ColTrailerSched:DataCardValue2.Text,ColTrailerActu:DataCardValue22.Text,ColFieldStorageSched:DataCardValue25.Text,ColFieldStorageActu:DataCardValue24.Text,ColSilo1Sched:DataCardValue12.Text,ColSilo1Actu:DataCardValue11.Text,ColSilo2Sched:DataCardValue15.Text,ColSilo2Actu:DataCardValue13.Text,ColSilo3Sched:DataCardValue29.Text,ColSilo3Actu:DataCardValue21.Text,ColBin4Sched:DataCardValue17.Text,ColBin4Actu:DataCardValue16.Text,ColWasteLine:DataCardValue20.Text,ColEmptyWeight:DataCardValue23.Text,ColScaleWeight:DataCardValue28.Text,ColReturnWeight:DataCardValue27.Text,ColBlowDownWeight:DataCardValue19.Text,ColComments:DataCardValue7.Text});
SaveData(TempCollection,"temporary"));
Back()

Thanks in advance for your help.

[v-albai-msft]

Hi @rkeeran76v2 ,

Do you want to upload collection “Success” (with only two columns) into SP list? Or do you want to upload records with all columns into SP list?

I assume you want to upload all records with all columns inside TempCollection into SP list:

1. When no connection, keep all your records into a collection called “TempCollection”.

2. When has connection: When app starts, upload all records inside collection into SP list, and directly submit form into SP list.

If yes, no need to create another collection called “Success”, you can directly upload the records inside collection TempCollection. Use below formula on OnStart:

OnStart:

LoadData(TempCollection,"temporary",true);
If(!IsEmpty(TempCollection.ColStatus)&& Connection.Connected, ForAll(TempCollection,Patch(listname,Defaults(listname), {
Colid:Colid,
ColStatus: ColStatus,
ColTaskInfo: ColTaskInfo,
ColPlant: ColPlant
…
})); Clear(TempCollection); SaveData(TempCollection,"temporary"))

For more information about PowerApps Offline function, I suggest you refer to the video by @Shane Young:

PowerApps Offline Mode - YouTube

Best Regards,

Allen

[rkeeran76v2]

Thanks Allen for your help. That is exactly what I am looking to do.

I ran with your suggestion and tried to test it on the phone. It is wanting to perform the action, but for some reason when I get back to service and restart the app it wants to upload the data I feel, but gives an error on startup that states, "The requested operation is invalid. Object must implement IConvertible."

I am debating going back through the patch variables and seeing if there is a specific one that is causing the problem. Other than that I am not sure where to go. Have you seen this before?

Thanks again for all your help.

Ryan

[rkeeran76v2]

Hello Allen,

I have watched the @Anonymous Young video and still have some issues that I think are because he is making it work with text entries and my SharePoint has choices.

My choice data cards will not allow me to enter as .text so I have to take the extension out or have .selected and that still doesn't get into the collection. So I think I am missing some of the basics when making this collection.

Here is my new code. I also noticed that I can not enter the Colid into the patch as when I do it says that it does not exist so like I stated I am a novice with all this stuff so i am sure I am missing something but there still seems to be a disconnect with my code working. 

OnStart:

LoadData(TempCollection,"temporary",true);
If(!IsEmpty(TempCollection.ColStatus)&&Connection.Connected,

ForAll(TempCollection,

Patch('Rockies Activity Tracker',Defaults('Rockies Activity Tracker'),
{
Status:ColStatus})));

Clear(TempCollection);SaveData(TempCollection,"temporary")

OnSelect (Submission Button):

If(Connection.Connected,
SubmitForm(EditForm1),

Collect(TempCollection,{
Colid: Max(TempCollection,Colid)+1,
ColStatus:DataCardValue6,
ColTaskInfo:DataCardValue10.Text,
ColPlant:DataCardValue9,
ColTrailerFSUPP:DataCardValue32.Text,
ColTractor:DataCardValue31.Text,
ColEEScheduled:DataCardValue38,
ColLoadingDock:DataCardValue26,
ColTrailerSched:DataCardValue2.Text,
ColTrailerActu:DataCardValue22.Text,
ColFieldStorageSched:DataCardValue25.Text,
ColFieldStorageActu:DataCardValue24,
ColSilo1Sched:DataCardValue12,
ColSilo1Actu:DataCardValue11,
ColSilo2Sched:DataCardValue15,
ColSilo2Actu:DataCardValue13,
ColSilo3Sched:DataCardValue29,
ColSilo3Actu:DataCardValue21,
ColBin4Sched:DataCardValue17,
ColBin4Actu:DataCardValue16,
ColWasteLine:DataCardValue20.Text,
ColEmptyWeight:DataCardValue23,
ColScaleWeight:DataCardValue28,
ColReturnWeight:DataCardValue27,
ColBlowDownWeight:DataCardValue19,
ColComments:DataCardValue7.Text});
SaveData(TempCollection,"temporary"));
Back()

Thanks in advance for all your help.

Ryan

[v-albai-msft]

Hi @rkeeran76v2 ,

For your choice column, you should use formula like this(you can use a label to check the result of different DataCardValue):

DataCardValue10.Selected.Value

If this choice column allows for multiple selections, you should use formula like this:

Concat(DataCardValue10.SelectedItems,Value,",")

For your Colid column, this is because each time your start the app, it will clear(TempCollection), so TempCollection is blank. (due to my mistake in my first reply...), so use this formula in your OnStart(clear TempCollection only after uploading data into SP):

LoadData(TempCollection,"temporary",true);
If(!IsEmpty(TempCollection.ColStatus)&&Connection.Connected,
ForAll(TempCollection,
Patch('Rockies Activity Tracker',Defaults('Rockies Activity Tracker'),
{
Status:ColStatus}));
Clear(TempCollection);SaveData(TempCollection,"temporary")
)

There is, of course, another situation: last time, you have upload all data into SP, so this time, TempCollection is still blank, so use this formula for Colid:

If(!IsEmpty(TempCollection.ColStatus),Max(TempCollection,Colid)+1,1)

This time, even when TempCollection is blank, the Colid will equals to "1". And next time, if still has no connection, the Colid will equals to "2". (Max(TempCollection,Colid)=1)

Best regards,

Allen

[Anonymous]

Thanks @v-albai-msft 

One last follow up. I have noticed that the Defaults function from everything I have read and played around with will only give me a new entry into the SharePoint list as opposed to updating a entry already on the SharePoint list. 

It looks like I do have the code as you put it, but it just adds a new entry into the list when I just need to update a current entry. I have a feeling I need to do more with the ID of the entries, but I am not sure where to incorporate this.

I feel like there is an easy fix, but at this point I cant seem to crack it.

Thanks for all your help!

Ryan

[v-albai-msft]

Hi @Anonymous,

This is because you are using Defaults('Rockies Activity Tracker') inside Patch.

See below example about Patch function from this article:

So you can add an If condition to judge if there is already a record in the list. If yes, update this entry, if no, create a new one. You can use a unique column to be the condition(for example your Title is unique, and each record in SP/collection has a different Title) , formula like this:

If(!IsEmpty(TempCollection.ColStatus)&&Connection.Connected,

ForAll(TempCollection,

If(Title in 'Rockies Activity Tracker'.Title,

Patch('Rockies Activity Tracker',Lookup('Rockies Activity Tracker', Title = 'Rockies Activity Tracker'[@Title]),{Status:ColStatus}),

Patch('Rockies Activity Tracker',Defaults('Rockies Activity Tracker'),

{

Status:ColStatus})));

Clear(TempCollection);SaveData(TempCollection,"temporary")

)

Best regards,

Allen

[Anonymous]

Hey @v-albai-msft,

This is just kicking my butt. You can tell I am for sure a novice. I am still having issues. I did modify what you had a little bit as all I want is for the users in the field to be able to just modify but not add when they get back to service. With this I did remove the second If statement and just used the patch to patch based off of ID. I have put my code below, but when I tried it on my phone I am getting an error saying that the operation is invalid. Fetching items failed. Possible invalid string in filter query.

The Title that you refer to in the SharePoint list is this the name of a column within the list or is it a Powerapps specific variable? Instead of title I ended up using the ID as in the ID column of the SharePoint, this could be where I am going wrong. Do I need to pick a SharePoint column name or a name that is in the app itself?

I am also wondering if I am having issues with my collection for the submission button. I tried using the .Selected.Value for my choice variables but whenever I do it gives me an error in my patch function that I dont know how to handle as well. If I just have the .Selected it will not throw the error but the moment I put the .SelectedValue I get an error on my patch function on the OnStart code.

Below is my code again. Like I have stated before I am very novice with this kind of stuff so my apologies ahead of time.

OnStart:

LoadData(TempCollection,"temporary",true);
If(!IsEmpty(TempCollection.ColStatus)&&Connection.Connected,

ForAll(TempCollection,

Patch('Rockies Activity Tracker',LookUp('Rockies Activity Tracker', ID ='Rockies Activity Tracker'[@ID]),{Status:ColStatus})));

Clear(TempCollection);SaveData(TempCollection,"temporary")

Submit:

If(
Connection.Connected,
SubmitForm(EditForm1),
Collect(
TempCollection,
{
ColID: DataCardValue42.Text,
ColStatus: DataCardValue6.Selected,
ColTaskInfo: DataCardValue10.Text,
ColPlant: DataCardValue9.Selected,
ColTrailerFSUPP: DataCardValue32.Text,
ColTractor: DataCardValue31.Text,
ColEEScheduled: DataCardValue38.Selected,
ColLoadingDock: DataCardValue26.Selected,
ColTrailerSched: DataCardValue2.Text,
ColTrailerActu: DataCardValue22.Text,
ColFieldStorageSched: DataCardValue25.Text,
ColFieldStorageActu: DataCardValue24,
ColSilo1Sched: DataCardValue12,
ColSilo1Actu: DataCardValue11,
ColSilo2Sched: DataCardValue15,
ColSilo2Actu: DataCardValue13,
ColSilo3Sched: DataCardValue29,
ColSilo3Actu: DataCardValue21,
ColBin4Sched: DataCardValue17,
ColBin4Actu: DataCardValue16,
ColWasteLine: DataCardValue20.Text,
ColEmptyWeight: DataCardValue23,
ColScaleWeight: DataCardValue28,
ColReturnWeight: DataCardValue27,
ColBlowDownWeight: DataCardValue19,
ColComments: DataCardValue7.Text
}
);
SaveData(
TempCollection,
"temporary"
)
);
Back()

Thanks again for all your patience.

Ryan

[v-albai-msft]

Hi @rkeeran76v2 ,

Yes, you can use the ID column, since ID column is unique in SP list.

From your formula, seems you only want your users to update existing record, so you remove the Patch(XXX,Defaults(),{}) part. This is OK, but pay attention to the location of Clear() and SaveData() function, they should inside the ForAll function. Each time you update records into SP successfully, the collection should be cleared. So check below for OnStart:

LoadData(TempCollection,"temporary",true);

If(!IsEmpty(TempCollection.ColStatus)&&Connection.Connected,

ForAll(TempCollection,

Patch('Rockies Activity Tracker',LookUp('Rockies Activity Tracker', ID ='Rockies Activity Tracker'[@ID]),{Status:ColStatus})));

Clear(TempCollection);SaveData(TempCollection,"temporary")

)

For your second question, this related to the control type of your controls.

If you are using a combo box control with single choice, try formula like this:

DataCardValue3.Selected.Value

If you are using a combo box control with multiple choices, try formula like this(this result is a text, so it should be saved into a SP Text column):

Left(Concat(DataCardValue3.SelectedItems,Value&","),Len(Concat(DataCardValue3.SelectedItems,Value&","))-1)

If you are using a dropdown control, try formula like this:

DataCardValue3.Selected.Value

Besides, you cannot directly patch a control into a column like below. You must use like “ColEmptyWeight:DataCardValue23.Text”.

Best regards,

Allen

[v-albai-msft]

Hi @rkeeran76v2 ,

How are things going? Have you solved your issue?
If my replies are helpful to you, please mark my answer. This will benefit other community members who stuck with the same question.

Best regards,

Allen