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 :
… for the first nominated book.
It simply returns the first row (
Nth(1)) in your filtered view
For the other books, each lookups would need to have a similar formula :
- Book 2 →
- Book 3 →
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 )
Results table, I first created an
Order field which just find the position of
thisRow within the table using this formula :
Then I’ve added a single select lookup field from the table
[Book List] and added this formula :
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(
Votes.[Vote Book 1] is the list of all the votes the Book 1 received in the table
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
Then, for each list of votes within the list (each specific list being stored/represented by
CurrentValue), I ask the formula to return the
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
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