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

Using Sequence() As Counter For Record Field in Collection

I have created a simple one column list in SharePoint (named 'Test List'). The default column name 'Title' has been filled with 5 items with text 'Entry <n>' where <n> is 1, 2, 3, 4, 5 for the rows  (image below bordered in red).

 

The list was connected to a test app. So I want to create a collection (you pick the object/control and the correct property like Items, or I suppose OnStart or OnVisible). The records will for the test simply be with two fields, Order and SPCol, and the Order field value should just be an increment of an integer starting from 1 for the number of items there are in any SP list column. The SPCol field will be the string that represents the text/string value of the item in the list.

{
  Order: <an integer starting with 1, provided by an incrementing counter, probably Sequence()>
  SPCol: the value in the SP list
}

When the collection is created, it should easily look like this:

{ Order: 1, SPCol: "Entry 1" },
{ Order: 2, SPCol: "Entry 2" },
{ Order: 3, SPCol: "Entry 3" },
{ Order: 4, SPCol: "Entry 4" },
{ Order: 5, SPCol: "Entry 5" }

 

I am pretty sure that the operating functions involved will be ForAll() and Sequence(), which later will be contained in a ClearCollect() function. But I somehow cannot get the text/string from the SP list item column values into the SPCol field. It sees references to 'Test List'.Title as a single column table, which it is.  This would be no problem if PowerApps used something like JavaScript, since I might reference it as "SPCol: 'Test List'.Title[Value]" but using that produces an error and clearly the PowerApps formula syntax does not work that way (even in Excel, I think a ROW() or other indexing function would work).

 

How am I suppose to set up this loop and create the collection?

Thanks

 

 

SharePoint List with One Column and 5 ItemsSharePoint List with One Column and 5 ItemsTrying A Formula For Creating The CollectionTrying A Formula For Creating The Collection

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User III
Super User III

@StevePenner 

Sorry, I should have been clearer in my response based on your situation.  I was giving a generic response.

 

Formula would be this:

With({yourList: yourSharePointList},
    ClearCollect(yourCollection,
        ForAll(Sequence(CountRows(yourList)),
            {Order: Value,
             SPCol: Last(FirstN(yourList)).Title
            }
        )
    )
)
        

 Now, you can replace the "yourSharePointList" above with a variety of things, like a Filter:

   Filter(yourSharePointList, <someCriteria>)  or a Sort:  Sort(yourSharePointList, ID), etc.

 

I hope that is more to your scenario.

_____________________________________________________________________________________
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

6 REPLIES 6
RandyHayes
Super User III
Super User III

@StevePenner 

ForAll returns a Table.  So you can simply use that for your collection:

    ClearCollect(yourCollection, ForAll(Sequence(4), {ID:Value, Entry: "Entry " & Text(Value)}))

Will result in a collection called yourCollection with the following records:

    {ID:1, Entry:"Entry 1"},

    {ID:2, Entry:"Entry 2"},

    {ID:3, Entry:"Entry 3"},

    {ID:4, Entry:"Entry 4"}

 

I hope 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!
WarrenBelz
Super User III
Super User III

Hi @StevePenner ,

This should do it - not using sequence though

ClearCollect(
   colTemp1,
   YourListName
);
Clear(colTemp2);
ForAll(
   colTemp1,
   Collect(
      colTemp2,
      Last(
         FirstN(             
            AddColumns(
               colTemp1,
               "RowNumber",
               CountRows(colTemp2) + 1
            ),
            CountRows(colTemp2) + 1
         )
      )
   )
);
Clear(colMyColl);
ForAll(
   colTemp2,
   Collect(
      colMyColl,
      {
         Order: RowNumber,
         SPCol: Title
      }
   )
)

 

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.

StevePenner
Helper I
Helper I

@RandyHayes 

Thanks, Randy. I guess I did not construct my example very well since your solution did not require referencing the values in the SharePoint list.  But suppose my SP list actually is one column with the item values shown in the image below (which is really the case with my needs, and now it requires referencing the SP list).

 

Capture2.PNG

This now means I want my collection in the app to have this result

{Order: 1, SPCol: "Dave moved to proceed to New Business. Motion passed."},
{Order: 2, SPCol: "Anne moved we discuss neighborhood traffic situation. Discussion proceeded. Motion to add stop sign was tabled."},
{Order: 3, SPCol: "Bill informed leadership of committee action"},
{Order: 4, SPCol: "Lisa moved to adjourn. Passed"}

 Now there is no option other than the formula reference the SP list items under the column

 

How is this done?

Thanks

RandyHayes
Super User III
Super User III

@StevePenner 

Sorry, I should have been clearer in my response based on your situation.  I was giving a generic response.

 

Formula would be this:

With({yourList: yourSharePointList},
    ClearCollect(yourCollection,
        ForAll(Sequence(CountRows(yourList)),
            {Order: Value,
             SPCol: Last(FirstN(yourList)).Title
            }
        )
    )
)
        

 Now, you can replace the "yourSharePointList" above with a variety of things, like a Filter:

   Filter(yourSharePointList, <someCriteria>)  or a Sort:  Sort(yourSharePointList, ID), etc.

 

I hope that is more to your scenario.

_____________________________________________________________________________________
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

@RandyHayes and @WarrenBelz 

Both of your solutions achieved the desired results. Bravo! I am going with Randy's since it was shorter, but I will look at both of them to understand what thinking is involved in constructing these formulas.

RandyHayes
Super User III
Super User III

@StevePenner 

Glad that worked for you.  

Yes, the key thing (which I see many miss) is that a ForAll is not just the only way to loop in PowerApps, it is a real function that returns a table of whatever you do in it.  So in this case of my formula, the results of the ForAll are actually used to populate the collection.  If ForAll is used as a primary statement without capturing its results, then it is just a wasted table.

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!

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.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,413)