cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AchalDesai
Helper I
Helper I

Check if Record exist in collection, create if not exist

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.

AchalDesai_0-1635788745036.png

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}}))
3 REPLIES 3
WarrenBelz
Super User
Super User

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
Super User
Super User

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
Helper I
Helper I

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}
            }
         )	
      
        

  

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (1,480)