Check if Record exist in collection, create if not exist

[AchalDesai]

Hi All,

I am creating an App to monitor ecommerce packages that get lost in transit during delivery. I had this part done when I only had to collect 1 value from the gallery and then do a ForAll Patch into my SharePoint List. But now there has been a change in the design and I need to collect and patch 3 values per record instead of just one.

In the screenshot of my app below, I had to only collect the 'Update Ops Status' value selected in the dropdown in a collection with the OnChange Property of the dropdown for all records that were changed in the gallery. And then patch it with for all to the SP list for all records in the gallery.

But now, the problem is that I have to collect 3 values from the dropdowns - 'Update Ops Status', 'Lost at Hub', 'Hub Name'

I tried the single Collect with OnChange property for each dropdown and modified the Patch function on the 'Submit' button. This solution works. But as you may have already guessed, each collect creates 3 records in the collection instead of just 1 and slows down the patch process. What I need is if I change 1 dropdown, check if the record exists in the collection, if exists then update that value in the collection. If does not exist, then create a record in the collection and then update the values in the collection for the other 2 dropdown changes. 

I tried with a If lookup isblank and patch combination. Also tried patch and coalesce combination. But the formula is getting an error in the Patch part of the function. I'm hoping you guys can help me out with this part.

Below is the screenshot of my App.

Current formulas that work

Collect on Dropdown 'Update Ops Status'
Collect(OpsSelectedGalItems,{'Eshop Package Reference': ThisItem.'Eshop Package Reference',OpsNewStatus: Ops_drpgalUpdateOpsStatus.Selected.Value})

Collect on Dropdown 'Lost At Hub'
Collect(OpsSelectedGalItems,{'Eshop Package Reference': ThisItem.'Eshop Package Reference',LostAtHubVal: Ops_drpgalLostAtHub.Selected.Value})

Collect on Dropdown 'Hub Name'
Collect(OpsSelectedGalItems,{'Eshop Package Reference': ThisItem.'Eshop Package Reference',HubNameVal: Ops_drpgalHubName.Selected.Value})

Submit Button
ForAll(RenameColumns(OpsSelectedGalItems,"EshopPackageReference","EshopPkgRefs"),Patch(LostPackagesDB,First(Filter(LostPackagesDB,'Eshop Package Reference'=EshopPkgRefs)),{'Operations Status':{Value:OpsNewStatus}},{'Lost at Hub':{Value:LostAtHubVal}},{'Hub Name':{Value:HubNameVal}}))

[WarrenBelz]

Hi @AchalDesai ,

Try the below combination

Collect(
   OpsSelectedGalItems,
   {
      'Eshop Package Reference': ThisItem.'Eshop Package Reference',
      OpsNewStatus: Ops_drpgalUpdateOpsStatus.Selected.Value
   }
)


Collect(
   OpsSelectedGalItems,
   {
      EshopPkgRefs: ThisItem.'Eshop Package Reference',
      LostAtHubVal: Ops_drpgalLostAtHub.Selected.Value
   }
)


Collect(
   OpsSelectedGalItems,
   {
      EshopPkgRefs: ThisItem.'Eshop Package Reference',
      HubNameVal: Ops_drpgalHubName.Selected.Value
   }
)


ForAll(
   OpsSelectedGalItems,
   With(
      {
         wRef:
         LookUp(
            LostPackagesDB,
            'Eshop Package Reference'=EshopPkgRefs
         ).'Eshop Package Reference'
      },		 
      If(
         IsBlank(wRef),
         Collect(
            LostPackagesDB,
            {
               'Operations Status':{Value:OpsNewStatus},
               'Lost at Hub':{Value:LostAtHubVal},
               'Hub Name':{Value:HubNameVal}
            }
         ),			
         Patch(
            LostPackagesDB,
            {'Eshop Package Reference':wRef},
            {
               'Operations Status':{Value:OpsNewStatus},
               'Lost at Hub':{Value:LostAtHubVal},
               'Hub Name':{Value:HubNameVal}
            }
         )
      )
   )
)

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.

Visit my blog Practical Power Apps

[WarrenBelz]

Hi @AchalDesai ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

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.

Visit my blog Practical Power Apps

[AchalDesai]

Hi @WarrenBelz, Sincere apologies. I was put on a different project this question slipped my mind.

I have circled back to the project now and tried your formula. However, the formula is giving me an error on the following part. Formula checker says that the If statement has incorrect arguments.

      If(
         IsBlank(wRef),
         Collect(
            LostPackagesDB,
            {
               'Operations Status':{Value:OpsNewStatus},
               'Lost at Hub':{Value:LostAtHubVal},
               'Hub Name':{Value:HubNameVal}
            }
         )