cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Owizardo
Helper III
Helper III

How to check if record already exists in a forall loop

Hello,

 

Ive been banging my head for weeks now, I can't seem to understand how to check if record already exists in a forall loop. 

 

Currently I am using a Sharepoint list and I need to do bulk edits on my Powerapps form. In my app I created a collection named Modified_records_collection, this is where all modified records will end up so I created the following script: 

 

 

ForAll(
	Modified_records_collection,
	If(
		IsBlank(

		  Filter(Sharepoint_List, ID = Modified_records_collection[@ID]) //Name identifier does not exist ....

		),

		Patch(
		Sharepoint_List,
		{ID: Blank()},
		{Data1: "Blah Blah Blah"}
		),
		
		Patch(
		Sharepoint_List,
		{ID: Modified_records_collection[@ID]}, //Name identifier does not exist ....
		{Data1: "Blah Blah Blah"}
		)
	)
);

 

Its suppose to be a simple check to see if the record already exists or not, if it does exists then update if not then create. The problem is when there is no existant records it returns an error saying name is invalid, This identifier isn't valid at the {ID: Modified_records_collection[@ID]} and breaks the whole forall loop. 

 

Any idea what im missing?

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User III
Super User III

@Owizardo 

Yes, since you are trying to check for existence with a Filter function, you will want to use IsEmpty instead of IsBlank on a table of Filtered items.  IsBlank of any filter will always be false.  You might want to review the criteria for the IsBlank function in the docs.

If you want to use IsBlank, you will want to specify a Lookup result for your ID check rather than a Filter function.  Something like this:

IsBlank(Lookup(SharePointList, ID = Modified_records_collection[@ID]).ID)  <= Would return true if the record does not e
xist.

Personally I find the above to be the best and easiest to remember.

If you want to switch over to IsEmpty, keep in mind it is all about Tables (which is something your filter would return), but IsEmpty is picky.  The docs say it is equivalent to CountRows=0 and this is the best way to think about this function:

IsEmpty(Lookup(SharePointList, ID = Modified_records_collection[@ID]))  <= Would return false if the record does not exist because this lookup will not return an empty table.

 

IsEmpty is a bit picky.  It will only return true on a completely empty table devoid of any records.

The Falses:
IsEmpty({}) <= returns false, because it has one empty row in the table.
IsEmpty(Table({})) <= returns false...same as above.
IsEmpty([{}]) <= same as above

The Trues:
IsEmpty(Table()) <= returns true, because there are NO rows in the table.
IsEmpty([]) <= same as above

For the scenario you are addressing here, IsBlank is definitely the way to go when used with a Lookup.  If you want to stick to the filter, then change to IsEmpty.

 

I hope that this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

8 REPLIES 8
RandyHayes
Super User III
Super User III

@Owizardo 

Yes, since you are trying to check for existence with a Filter function, you will want to use IsEmpty instead of IsBlank on a table of Filtered items.  IsBlank of any filter will always be false.  You might want to review the criteria for the IsBlank function in the docs.

If you want to use IsBlank, you will want to specify a Lookup result for your ID check rather than a Filter function.  Something like this:

IsBlank(Lookup(SharePointList, ID = Modified_records_collection[@ID]).ID)  <= Would return true if the record does not e
xist.

Personally I find the above to be the best and easiest to remember.

If you want to switch over to IsEmpty, keep in mind it is all about Tables (which is something your filter would return), but IsEmpty is picky.  The docs say it is equivalent to CountRows=0 and this is the best way to think about this function:

IsEmpty(Lookup(SharePointList, ID = Modified_records_collection[@ID]))  <= Would return false if the record does not exist because this lookup will not return an empty table.

 

IsEmpty is a bit picky.  It will only return true on a completely empty table devoid of any records.

The Falses:
IsEmpty({}) <= returns false, because it has one empty row in the table.
IsEmpty(Table({})) <= returns false...same as above.
IsEmpty([{}]) <= same as above

The Trues:
IsEmpty(Table()) <= returns true, because there are NO rows in the table.
IsEmpty([]) <= same as above

For the scenario you are addressing here, IsBlank is definitely the way to go when used with a Lookup.  If you want to stick to the filter, then change to IsEmpty.

 

I hope that this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

Nebulas
Helper I
Helper I

Hi @RandyHayes 

I have a very similar use case here where I am trying to build a collection where Sharepoint userID does not exist.

 

My code looks as follows:

 

Clear(colMaterialRecordsNew);
ForAll(
colMaterialRecords,
If(IsBlank(LookUp('Maintenance Materials'.ID, ThisRecord.ID = ID)) = true,
Collect(colMaterialRecordsNew,ThisRecord
)
)
);

 

I have been able to prove the IF statement by adding to a label and substituting 'ThisRecord.ID', with 'First(colMaterialRecords)' and 'Last(colMaterialRecords)'. I then substitute 'Collect(colMaterialRecordsNew,ThisRecord' with a simple "New Record", and "Existing Record" for the else. This works perfectly as the first record in my collection does exist in SharePoint, whereas the last record does NOT.

 

One thing to note is that my existing collection (colMaterialRecords) has a mix of ID sourced from SharePoint, and then dummy or placeholder IDs that my collection inserts.

 

This however does NOT work when I tie it all together in my code above and does NOT create my new collection.

 

When I change the condition to = false, it actually builds the new collection (colMaterialRecordsNew) with every record from the existing collection (colMaterialRecords).

 

For the life of me I can't seem to figure out why this is not working.

 

Any help would be appreciated! 

RandyHayes
Super User III
Super User III

@Nebulas 

I think you're overcomplicating!!  If you are looking for new records, they would NOT have an ID, so you only need to filter your collection :  Filter(colMaterialsRecords, IsBlank(ID))

 

If for some reason that is not the case then consider changing your Formula to the following:

ClearCollect(colMaterialRecordsNew,
    Filter(
        ForAll(colMaterialRecords As _item,
            If(LookUp('Maintenance Materials', ID = _item.ID, true), _item)
        ),
        !IsBlank(ID)
    )
)    
_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

@RandyHayes 

My Modus Operandi is to overcomplicate things... guilty as charged! 🙂

 

The reason for the over-complication is because of my Placeholder/Dummy IDs that I am collecting when as user adds new records/rows on a form. This is to give me a reference point if they keep making further updates to the row. My Placeholder/Dummy IDs are simply a lookup of the Max currently in the SharePoint list + 1 to increment. They are essentially borrowed and then real ID's are patched on the last form.

 

I wouldn't say this is the most elegant of solutions but does work for my purposes.

 

With the two examples you provided, the first won't work due to my Placeholder/Dummy IDs.  The second is great and pointed me in the right direction.  I wasn't able to get it working to detect the NEW records, but it did allow me to detect the EXISTING records. So I just reversed my thinking. I have now created a new collection called 'colMaterialRecordsExisting' and then removed these same records from 'colMaterialRecords'. This left me with only the new records in 'colMaterialRecords'. From this I created a new collection called 'colMaterialRecordsNew' and omitted the ID's from the collection as they are no longer needed and would be recreated by patching to SharePoint.

 

My code now looks like this:

Set(
    varLastMaintenanceRecordItemID,
    Last('Maintenance Records'.ID)
);
// Start Materials Collections and Patching
ClearCollect(
    colMaterialRecordsExisting,
    Filter(
        ForAll(
            colMaterialRecords As _item,
            If(
                LookUp(
                    'Maintenance Materials',
                    ID = _item.ID,
                    true
                ),
                _item
            )
        ),
        !IsBlank(ID)
    )
);
Remove(
    colMaterialRecords,
    Filter(
        ForAll(
            colMaterialRecords As _item,
            If(
                LookUp(
                    'Maintenance Materials',
                    ID = _item.ID,
                    true
                ),
                _item
            )
        ),
        !IsBlank(ID)
    )
);
Patch(
    'Maintenance Materials',
    colMaterialRecordsExisting
);
Clear(colMaterialRecordsNew);
ForAll(
    colMaterialRecords,
    Collect(
        colMaterialRecordsNew,
        {
            ID: Blank(),
            MR_ID: If(
                IsBlank(ThisRecord.MR_ID) = true,
                varLastMaintenanceRecordItemID.ID,
                ThisRecord.MR_ID
            ),
            Material: ThisRecord.Material,
            Quantity: ThisRecord.Quantity,
            Title: ThisRecord.Title
        };
    )
);
Remove(
    colMaterialRecordsNew,
    LookUp(
        colMaterialRecordsNew,
        Title = "ButtonPlaceholder"
    )
);
Patch(
    'Maintenance Materials',
    colMaterialRecordsNew
);
Clear(colMaterialRecordsExisting);
Clear(colMaterialRecordsNew);
Clear(colMaterialRecords);
Notify(
    "Record successfully added",
    Success
);

 

Thanks a lot for your help, I was spinning my wheels for many hours!

I don't see an option to accept as solution?

@Nebulas 

Yes, that formula is way too programmatic for PowerApps.  Not to say it will not work, just to say you should consider that PowerApps is a no-code platform.  Changing your thought process to be more like how it was designed (modeled after Excel) will make your experience much better!!  The dead giveaway is the ForAll in the middle of the formula.  ForAll is being used there as a For/Loop - it is a function that creates a Table...so where is the table going?  Plus the collection after collection is just the hard way to go about designing PowerApps.  The reality is that you already have all of the data that you need in the controls in your app, so collection from collections, etc is just duplicating and re-duplicating data.  I realize you can get it to "work" with such formulas, but they are way overcomplicated for PowerApps design but work great if you were actually developing - which you're not.  

Anyway, just wanted to point that out a bit to give you some things to think about - believe me (been there) you will find PowerApps to be SO simply to design and enhance once you clear the mind of "development".

Happy PowerApping!!

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

@Nebulas 

Yes, you will not have the option to accept as solution because this post was not posted by you.  It was originally posted by @Owizardo 

Always best to post new threads on the forum when you need some help...this will make sure it is your post and that it is also "fresh" for people to respond to.  You can always reference another post in yours and even @ mention specific people in your post that you would like to get "notified" that you are looking for them to help.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
Nebulas
Helper I
Helper I

@RandyHayes 

 

Okay no problem will do this going forward.

 

Yeah there is definitely some room for refactoring here and I am going to revisit this soon because there is most likely something 'very simple' I have missed or overcomplicated in my earlier development.

 

That being said I am extremely happy that everything is now working.

 

Thanks again for your help.

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (1,365)