Using Sequence() As Counter For Record Field in Collection

[StevePenner]

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 ItemsTrying A Formula For Creating The Collection

[RandyHayes]

@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.

[WarrenBelz]

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]

@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).

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 · Accepted solution]

@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.

[StevePenner]

@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]

@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