Collection to Update/append to Sharepoint List

[KathrynHughes]

Hi all,

I am creating an app that takes user responses via a form and populates a sharepoint list - I have it working when online, when offline I am storing the responses in a collection however I am unable to populate the sharepoint list from this collection once reconnected.

I am using a timer with the following code OnTimerEnd:

If(Connection.Connected && CountRows(IssuesToBeAdded) >0,
ForAll(IssuesToBeAdded, Patch('Issue List', Defaults('Issue List'),{Title:Body2,Number:Subtitle3,Flavour:Title3})))

Where my sharepoint list is 'Issue List' and the collection 'IssuesToBeAdded'.

Any help would be much appreciated.

Thanks

[RandyHayes]

@KathrynHughes 

The formula appears pretty straight forward.  What issue are you running into?  Errors, no records, collection still contains items, etc.?

[KathrynHughes]

I thought so too@RandyHayes but there must be something not quite right. The collection records have not been added to the sharepoint list, and there is an error message displayed - the requested operation is invalid. Server response: object must implement IConvertible

[RandyHayes]

@KathrynHughes 

In looking at your formula, I suspect the issue is with the Number column.

I'm going to assume that Title (no doubt) and Flavour are Text Columns in your SharePoint list.

But, I'm guessing that Number may not be a text column but instead a Numeric Column.  If so, then you need to adjust your formula to:

  {Title: Body2, Number: Value(Subtitle3), Flavour: Title3}

Hopefully that is all it is...

[KathrynHughes]

Tried adding that in but the formula has been highlighted red - it doesn't seem to like that syntax.

[RandyHayes · Accepted solution]

@KathrynHughes 

Usually when you see the IConvertible error, that means that it is trying to convert from one type to another and whatever you passed it has no ability to be converted.

What I might do to troubleshoot is:

First - verify the column types and make sure you are passing the proper types to them in your Patch.

Second - start to strip down the Patch.  Take out the Number and Flavour assignments and just assign the Title column (that is, if Number and Flavour are not marked as required)

Check those two steps out and see what you have.  

Let's fix this first, and then I have a suggestion for you on the overall process you are doing with this.

[KathrynHughes]

Thanks, I have tried what you have suggested but getting the same errors below is the code for my "submit" button this part is working as I would expect (I have removed all other inputs apart from Title):

If(Connection.Connected,SubmitForm(Form1); NewForm(Form1);Refresh('Issue List'); ClearCollect(Billy,'Issue List');
 SaveData(Billy,"issuesInLocalStorage")
 ,
 Collect(IssuesToBeAdded,{Title:DataCardValue1.Text});SaveData(IssuesToBeAdded, "newIssuesInLocalStorage");ResetForm(Form1))

The new OnEnd coder for the timer is:

If(Connection.Connected && CountRows(IssuesToBeAdded) >0,
ForAll(IssuesToBeAdded, Patch('Issue List', Defaults('Issue List'),{Title:Title1})))

[KathrynHughes]

@RandyHayes I changed timer1 to timer (in the OnEnd function) and it now works perfectly for the title field.

Thanks for your help 🙂

[RandyHayes]

@KathrynHughes 

I never saw that particular "timer1" anywhere in the formulas you posted.  But sounds like you did.

A couple things you might want to consider for your app.

1) Use a Toggle rather than a constantly running timer.  Set the Default of the Toggle to Connection.Connected.  When you are connected, any formula you have in OnCheck be acted on.  It's a little nicer than the timer as you can troubleshoot connectivity a little more visually.

2) Create a true/false field in your IssuesToBeAdded collection.  When you're not connected, add your item as you do and also set this field to true.  Then when your connection is restored, Patch the datasource for all items that are set to true and then set the field to false all in the same formula.  This way, if you get intermittent connection and only 1 or 2 items can be patched before the connection is lost, then the next connection will pick up where that left off (and any new).  Then at the end of the Patching, do a RemoveIf against that true/false field to remove records that DID get recorded.

So, using that Toggle example from above (a bit of "formula on-the-fly", change according to your app):

   tglConnection - Default : Connection.Connected.

   OnCheck:

ForAll(Filter(IssuesToBeAdded, notRecorded=true),
   Patch('Issue List', Defaults('Issue List'), {Title:Title1});
   Patch(IssuesToBeAdded, Lookup(IssuesToBeAdded, Title=Title1), {notRecorded=false})
);
RemoveIf(IssuesToBeAdded, notRecorded=false)

Just a couple suggestions to make sure you don't get duplicates or inaccurate results.  I hope that can be additionally helpful.

[KathrynHughes]

Hi @RandyHayes ,

Thanks for the suggestion - I will have a look at implementing this as the toggle sounds ideal for the way we will be working!

With the patch function, I have attachments within my form - am I able to put this in the collection too and then patch to sharepoint list? I've been reading online and have picked up from older posts that this isn't possible do you know if thats the case? Sorry - very new to PowerApps

Thanks