How to put Sharepoint List column values into rows for a gallery?

[sienna28]

I have a Sharepoint list of courses in the following format

I need to populate a gallery using the values, but each column needs to be on a seperate row as follows:

Format for gallery....

CourseName    Dates                Max Candidates          ID
Factura digital  05/05/2022               10                        1
Factura digital  06/09/2022               10                        1
Factura digital  07/09/2022               10                        1
Factura digital  08/09/2022               10                        1

I need to do this for each record in the list - I tried a ForAll with collections, but could not create rows and assign values.

Any code and help appreciated - many thanks

[RandyHayes]

@sienna28 

You will need to do this manually.  (as in - specifying each column)

Your formula would be similar to the following:

Ungroup(
  ForAll(<yourTable> As _item,
    {ItemRow: 
        With(_item,
          Ungroup(
            Table({CourseName: CourseName, CourseDate: CourseDate, MaxCandidates: MaxCandidates},
                  {CourseName: CourseName, CourseDate: CourseDate1, MaxCandidates: MaxCandidates},
                  {CourseName: CourseName, CourseDate: CourseDate2, MaxCandidates: MaxCandidates},
                  {CourseName: CourseName, CourseDate: CourseDate3, MaxCandidates: MaxCandidates},
                  {CourseName: CourseName, CourseDate: CourseDate4, MaxCandidates: MaxCandidates},
          ),
          "CourseName"
        )
    }
  ),
  "ItemRow"
)

I hope this is helpful for you.

[sienna28]

Thanks for coming back Randy

Is it possible to see what is coming back by hovering over parts of the code?

Also, what is the value I should bind the gallery items to?

Many thanks

[sienna28]

I resolved both by wrapping the statement in a collection. I know you are not keen on collections, but I did not know of another way.

ClearCollect(Testgal,
Ungroup(
ForAll(AcademyCourse As _item,

I wondered if you explain each step, specifically 'Ungroup' that is used in 2 instances?

It would really help to aid my understanding.

Many thanks

[RandyHayes · Accepted solution]

@sienna28 

You don't need to re-duplicate all the data into a collection - don't waste the memory resources in your App!!

The formula I provided you was for the Item property of your Gallery!

Setting your Items property to that formula will yield the results you want without needing to re-duplicate into a collection!

And...I apologize I was flying fast putting that together in the response...there only needs to be one Ungroup in it.  SO, your Items property on your gallery becomes:

Ungroup(
  ForAll(<yourTable> As _item,
    {ItemRow: 
        With(_item,
            Table({CourseName: CourseName, CourseDate: CourseDate, MaxCandidates: MaxCandidates},
                  {CourseName: CourseName, CourseDate: CourseDate1, MaxCandidates: MaxCandidates},
                  {CourseName: CourseName, CourseDate: CourseDate2, MaxCandidates: MaxCandidates},
                  {CourseName: CourseName, CourseDate: CourseDate3, MaxCandidates: MaxCandidates},
                  {CourseName: CourseName, CourseDate: CourseDate4, MaxCandidates: MaxCandidates}
             )
        )
    }
  ),
  "ItemRow"
)

More on the above change below...

Ungroup is a function that takes a grouping and normalizes it back into rows.

It is best to look at it from the GroupBy function perspective.

If you have the following data:

TableA

Name	Value
"AA"	1
"AA"	2
"BB"	10
"BB"	20
"CC"	100
"DD"	200

And you were to GroupBy Name.  Your formula would be:

GroupBy(TableA, "Name", "_data")

This would result in a table with the following data:

TableB

Name	_data
"AA"	Value 1 Value 2
"BB"	Value 10 Value 20
"CC"	Value 100 Value 200

A record with a column called "Name" that contains text and a column called "_data" that contains a table.

So at this point you can clearly comprehend that doing a Ungroup(TableB, "Name") would result in the original TableA

Now, with the above in mind...if we had a table with just the _data column (a table column) and it looked like this:

TableC

_data

Value 1 Value 2

Value 10 Value 20

Value 100 Value 200

Then if we Ungroup the above - Ungroup(TableC, "_data") then we would have a resulting table of:

Value

1

2

10

20

100

200

(remember this scenario)

So, now taking the formula I provided, it all starts with a record to convert.  To get that record we enlist the ForAll function (which is a function that builds tables) to iterate over the source table records.

Then, in that ForAll, we define that our table will have records with a single column called ItemRow

Now, to define the ItemRow, we ungroup a table that we build manually (remember, I mentioned it would have to be transposed manually in the formula).  

To do that, we build a table with a common column.  In this case it is CourseName.  That is all given to a Table function that will return a table with the exact rows in it that we defined (one for each CourseData Column) AND in that, we "normalized" the different course data columns into a common column "CourseDate".   So, after this point, those columns don't make a difference any longer as they are now column values in a row in a table.

Now, in the first formula, I had accidentally put in the Ungroup there on the table result...we don't need it as it is already ungrouped.

So...moving on we have just (in the series of iterations over the source data) created the first record of our ForAll table.  That record is a record with a column called ItemRow that has a table in it that we defined with the Table function.

After the ForAll has iterated over all the records of the source data, it will be returning a table that look just like TableC above.  It has a column with a table in it.

SO...just like above, we then ungroup that by that column.

AND, the result is, a table with all the rows put together (ungrouped).

Hopefully that makes sense and is helpful.

(and ditch the collections...they are way overused!)

[sienna28]

Good explanation - thanks Randy

@RandyHayes 

I just wanted to clarify if _item becomes the name of the table and it has a single column called ItemRow.

Also, I guess there is no way in this case of debugging to see what the table looks like at each iteration? It's just helpful when constructing future similar cases.