Moving rows to columns for a form

[Ross_Chehayeb1]

Problem
I need to a filtered selection of rows from one table and use them to be questions in the form, which I believe requires them to be columns in a different table, but I can’t find a way to do this automatically.

Use Case
I’m in a book club and I created a doc to store a list of books we could read in the future and allow people to add new books. Every month, we nominate a selection of books from the list and vote on them to choose the next month’s book. After we have nominated 5 books from the main book list, I need to take those 5 books and put them in a poll so the group can vote on them.

Manual Workaround
I’ve created a separate table with a form for the monthly poll and every month, I’m currently manually typing the names of the nominated books into columns on the Poll table, but I’d love for this to happen automatically with a formula somehow.

Any ideas? Here’s the doc.

[Pch]

Hi @Ross_Chehayeb1

How about using 5 lookup fields from your [Book List] table for the form and 5 scale fields .

Each lookup field would be a single select with a formula looking like this :

[Current Nominations].Nth(1)

… for the first nominated book.
It simply returns the first row (Nth(1)) in your filtered view [Current Nominations]

For the other books, each lookups would need to have a similar formula :

• Book 2 → [Current Nominations].Nth(2)
• Book 3 → [Current Nominations].Nth(3)
• etc…

And for each of those lookups you would need to add a corresponding scale for the voters to vote

It could give you something like looking like this :

For the next month, you shouldn’t have anything to do …
Once 5 other books are nominated, the table should pick the new ones all by itself .

Now, for the resulting chart, if you wish to also “automatise” it a little, I guess you would need to also use a new table and transpose the results
(Please, don’t mind the “broken formula” indicator in the field Sum, I’ve only tested this for 2 books, so it just can’t find results for the books 3, 4, and 5 )

For the Results table, I first created an Order field which just find the position of thisRow within the table using this formula :

thisTable.Find(thisRow)

Then I’ve added a single select lookup field from the table [Book List] and added this formula :

[Current Nominations].Nth(thisRow.Order)

So again, I’m just taking the Nth() row from the filtered view [Current Nominations] but based on the value in thisRow.Order this time

And then, in the 3rd field, I calculate the Sum() for the books based on the values in the Votes table using this formula :

List(Votes.[Vote Book 1], Votes.[Vote Book 2]).ForEach(
    CurrentValue.Sum()
  ).Nth(thisRow.Order)

where Votes.[Vote Book 1] is the list of all the votes the Book 1 received in the table Votes and Votes.[Vote Book 2] is the list of all the votes the Book 2 received in the table Votes (the other lists of votes for the Book 3, 4 and 5 should also be added here ) and those lists are stored within a List().

Then, for each list of votes within the list (each specific list being stored/represented by CurrentValue), I ask the formula to return the Sum().

And as ForEach() returns a list, to get the appropriate sum for each book in the table, I just once more only keep the Nth() number in the list based on the value in thisRow.Order .

After that you can create a view of this table and display it as a chart which also would require less manual work .

It won’t be as pretty though, as there would only be one field needed for the horizontal axis (the lookup with the nominated books for the month) and one field needed for the vertical axis (the sum field), so there will be only one color …

This is just an idea .

But I hope it helps a little