Help please understanding what is happening: If(), Collection

(0) Share

Report

Posted on by ChrisChong

G'Day you clever folks,

The puzzle is that a simple IF() statement produces unexpected results and I cannot determine why.

The code fragment below illustrates the puzzle.

I expected If(Len( Last(FirstN(myNumberedCollection,r1)).Result) = 3,1,0) to produce the number 1 if the Result was "Yes". Nope! WHY? What does produce predictable but to my mind puzzling results is the code below.

The collection, myNumberedCollection contains 60 2-column records. I search through the collection until I come to a record containing the word "Plant". It is the following record which contains the information I seek. The information could be, Yes, No, or Blank.

With(
{r1: LookUp(
 myNumberedCollection,
 StartsWith(Result, "Plant"), RowNumber + 1)
},
If (Len( Last(FirstN(myNumberedCollection,r1)).Result) <> 3,1,0)
)

Can someone explain the reason please? I have spent a few hours coming to this puzzling solution. I fear my understanding of these PowerApps elements is faulty and will cause me further grief in the future.

Thank you for any help.

Signed: Puzzled (aka Chris)

Categories:

Building Power Apps

All responses (7)

Answers (0)

Sort by

EddieE 4,650 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
@ChrisChong
Do some troubleshooting. Replace your code with this and see what gets returned

Last(FirstN(myNumberedCollection,r1)).Result
You may find it’s not what you expect ? I only say this because I don’t know what you data looks like
PowerRanger 3,458 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@ChrisChong The formular which you have applied will not act as For-Loop. It will use one specifc record which you have told to use.

LookUp( myNumberedCollection, StartsWith(Result, "Plant"), RowNumber + 1)

RowNumber + 1 doenst make sense in this context.

Beside that the If part should look like:

If (Len(r1.yourColumnName) <> 3,1,0)

But as mentioned before, using the With() function will NOT iterate through your whole collection.
Could you describe what you are actually trying to achieve - This would help to provide the correct formular. Please descrive the steps and maybe provide some screenshots.

And it might be good to know how your collection myNumberedCollection looks like. (With column names)

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.
ChrisChong on at

Like
a
(0)

Report
Copy link

Link copied!
Thanks for your response. I have done several hours of "troubleshooting" including what you suggested.
Last(FirstN(myNumberedCollection,r1)).Result
... returns "Yes" which is correct. The issue is that I must further process that, "Yes", so that it becomes the number 1. I thought that this should work: If(Result = "Yes", 1, 0) This returns "0" not 1 as I expect.
I hope this makes sense.
Chris
PowerRanger 3,458 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@ChrisChong thats not what I have suggested. Have a look at my Formular again.
ChrisChong on at

Like
a
(0)

Report
Copy link

Link copied!
Thank you for your assistance.
I understand what you did by substituting Lookup. I did that as well. Getting to the correct row isn't the problem.
"RowNumber + 1" is necessary because the data I want is in the row following "Plant". In the snip below, I want what is in RowNumber 32.
What puzzles me is the answer on the following row (row 32) is "Yes" so why should the following code work? It says if the len of Result is NOT equal to 3 then return 0. But it returns 1.
If (Len(r1.yourColumnName) <> 3,1,0)
I do not use With() to iterate of the collection but to simplify the formula.
The collection contains two columns: RowNumber and Result
EddieE 4,650 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
@ChrisChong
Try this to see what it returns
Len(r1.yourColumnName)
It maybe that there’s a space or hidden <return> character on the end?

Also, the RowNumber +1 inside your LookUp maybe returning a false positive and this may be causing issues? I’d use this if I was using this code structure

LookUp(
myNumberedCollection,
StartsWith(Result, "Plant"), RowNumber) + 1
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
@ChrisChong

You might want to be a little more specific on the record you are looking up.

Consider the following formula:

With({r1: LookUp(myNumberedCollection, StartsWith(Result, "Plant"), RowNumber + 1 ) }, If( Len(LookUp(myNumberedCollection, RowNumber = r1, Result)) <> 3, 1, 0 ) )

This way you are sure you are getting the correct row from your collection.

I hope this is helpful for you.