For All, Patch and If combination (possibly) to Sharepoint code help

[IzzyWizz]

Hi,

I have an audit app that collects the answers to the audit questions in a collection (AuditCol) which is then patched to a Answers Sharepoint list. I have a second collection (PictureCol) that contains images taken for each of the audit questions which is patched to a second Images Sharepoint list. The column QuID (the number of the question to which the answer or image is for) links the two collections and lists. The Patch functions are below.

ForAll(AuditCol,Patch('Ladder Audit Answers',Defaults('Ladder Audit Answers'),{QuID:QuID,AuditID:IDAuditVar,Title:Title, Location:Location,Question:Question,Answer:Answer,Comment:Comment}));

ForAll(PictureCol,Patch('Ladder Audit Answers Images',Defaults('Ladder Audit Answers Images'),{Title:IDAuditVar,QuID:Question,ImageBase64:Mid((JSON(UploadedImage1.Image, IncludeBinaryData)), Find(",", (JSON(UploadedImage1.Image, IncludeBinaryData)))+1, Len(JSON(UploadedImage1.Image, IncludeBinaryData))- Find(",", (JSON(UploadedImage1.Image, IncludeBinaryData)))-1)
,AuditID:IDAuditVar}));

What I want to do is patch the items in the second collection to the first Sharepoint list so I don't need two lists. I need code that says 'For All items in the PictureCol, Patch them to the Answers Sharepoint list but only If the QuID of the image is the same as the QuID of the answer', i.e. the image is patched to the answer to which it relates.

But I am lost in the syntax for this. I think I need a combination of For All, Patch and If but I'm not sure. Can anyone help please?

Thanks,

Izzy.

[WarrenBelz]

Hi @IzzyWizz ,

Maybe something like this if I have read your requirements properly. Note the As statements get rid of Ambiguity.

ForAll(
   AuditCol As Audit,
   Patch(
      'Ladder Audit Answers',
      Defaults('Ladder Audit Answers'),
      {
         QuID:Audit.QuID,
         AuditID:IDAuditVar,
         Title:Audit.Title, 
         Location:Audit.Location,
         Question:Audit.Question,
         Answer:Audit.Answer,
         Comment:Audit.Comment
      }
   )
);
 
ForAll(
   PictureCol As Pic,
   Patch(
      'Ladder Audit Answers',
      {QUID:Pic.QUID},
      {
          Title:Pic.Title,
          AuditID:Pic.AuditID,
          . . .and the rest . . .
      }
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[IzzyWizz]

Hi @WarrenBelz ,

Thanks for helping me again.

I have tried your formula but it doesn't like the second For All bit. Perhaps something to do with the two lots of curly brackets? I don't think I have copied it wrongly.

Thanks,

Izzy.

[WarrenBelz]

Hi @IzzyWizz ,

Can you please post your code as Text in a text box (the </> icon at the top)

[IzzyWizz]

Hi @WarrenBelz ,

This is the code, I have put it all in, in case I have made a mistake elsewhere that is causing the problem:

Set(IDAuditVar,Patch('Audit App Audits',Defaults('Audit App Audits'),{Auditor:AuditorNameTextInput.Text,Title:AuditorNameTextInput.Text,AuditorEmail:AuditorEmailTextInput.Text,AuditDate:Now()}).ID); 

ForAll(LadderAuditGallery.AllItems,UpdateIf(AuditCol,QuID=Value(IDLadder.Text),{AuditID:IDAuditVar,Answer:RadioLadder.Selected.Value, Comment:CommentLadder.Text,NoOfImages:0})); 

ForAll(
   AuditCol As Audit,
   Patch(
      'Ladder Audit Answers',
      Defaults('Ladder Audit Answers'),
      {
         QuID:Audit.QuID,
         AuditID:IDAuditVar,
         Title:Audit.Title, 
         Location:Audit.Location,
         Question:Audit.Question,
         Answer:Audit.Answer,
         Comment:Audit.Comment
      }
   )
);
 
ForAll(PictureCol As Pic,
   Patch(
      'Ladder Audit Answers'
      {QuID:Pic.QuID},
      {Title:Pic.Title,
          AuditID:Pic.AuditID,ImageBase64:Mid((JSON(UploadedImage1.Pic.Image, IncludeBinaryData)), Find(",",  (JSON(UploadedImage1.Pic.Image, IncludeBinaryData)))+1, Len(JSON(UploadedImage1.Pic.Image, IncludeBinaryData))- Find(",",  (JSON(UploadedImage1.Pic.Image, IncludeBinaryData)))-1)
,AuditID:IDAuditVar
      }
   )
)

Clear(PictureCol);Set(IsReset, true);Set(IsReset, false);Navigate(Homescreen, ScreenTransition.None)

[WarrenBelz · Accepted solution]

Hi @IzzyWizz ,

I free-typed that more as a guide to syntax - I generally look to you to fix commas etc. There was one missing (now fixed) after 'Ladder Audit Answers' on the second bit. Please ensure you now understand the logic - it will assist you greatly in the future.

ForAll(
   PictureCol As Pic,
   Patch(
      'Ladder Audit Answers',
      {QuID:Pic.QuID},
      {
         Title:Pic.Title,
         AuditID:Pic.AuditID,
         ImageBase64:
         Mid((JSON(UploadedImage1.Pic.Image, IncludeBinaryData)), 
         Find(",",  (JSON(UploadedImage1.Pic.Image, IncludeBinaryData)))+1, 
         Len(JSON(UploadedImage1.Pic.Image, IncludeBinaryData))- 
         Find(",",  (JSON(UploadedImage1.Pic.Image, IncludeBinaryData)))-1),
         AuditID:IDAuditVar
      }
   )
)

Note that I made no attempt to look at the JSON code - so it this still does not work, remove it and see if this is the issue.

As an aside, I cannot test this, but it might be better

ForAll(
   PictureCol As Pic,
   Patch(
      'Ladder Audit Answers',
      {QuID:Pic.QuID},
      {
         Title:Pic.Title,
         AuditID:Pic.AuditID,
         ImageBase64:
         With(
            {
               vJSON:JSON(UploadedImage1.Pic.Image, IncludeBinaryData)
            },
            Mid(
               vJSON, 
              Find(",",vJSON)+1, 
              Len(vJSON)- 
              Find(",",vJSON)-1
            )
         ),
         AuditID:IDAuditVar
      }
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[IzzyWizz]

Hi @WarrenBelz ,

Thanks, I have put in the comma and made some other amendments to the columns. The JSON bit is working but I used your code as it is much neater.

The Patch is working in that I am now getting the images from the PictureCol in the Ladder Audit Answers list, BUT, they are appearing as a new row, rather than an addition to the row that has the same QuID as the image.

Should the {QuID:Pic.QuID} section of code not be telling it to Patch to the row where the existing items QuID is the same as the images's QuID? 

ForAll(
    PictureCol As Pic,
    Patch('Ladder Audit Answers',{QuID:Pic.QuID},
        {
            QuID:Pic.QuID, Title: Pic.Title,
            ImageBase64: With({vJSON: JSON(UploadedImage1.Image,IncludeBinaryData)},Mid(vJSON,Find(",",vJSON) + 1,Len(vJSON) - Find(",",vJSON) - 1)),
            AuditID: IDAuditVar
        }
    )
);

[WarrenBelz]

Hi @IzzyWizz ,

If you look at View > Collections > PictureCol, can you see the QuID column and does it contain values matching the relevant item in 'Ladder Audit Answers' ? 

If there are matches, that syntax should certainly update the relevant existing record.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[IzzyWizz]

Hi,

Yes it does, I can see the QuID in the AuditCol collection and the PictureCol collection. They both Patch to the Ladder Audit Answers list but as separate rows with the same QuID. Very odd.

But, whilst I was eating my lunch it occurred to me that perhaps I could just add the images to the AuditCol collection and do one Patch. And, using a ForAll and UpdateIf, I have managed to get the image Base64 into the collection along with the other data and then patched to my list.

Thanks for your help, it made me think more carefully about what was going on. All these issues are frustrating at the time but I learn a lot, with help, trying to sort them out!

Izzy.

[WarrenBelz]

No problems @IzzyWizz ,

It is very odd as I use that syntax regularly without issues.