Referencing rows above and below based on numerical order

[Holly_Shkolnick]

Hi Coda Community,

I’ve taught myself a lot of things about Coda and formulas from things I’ve seen posted here. I’m trying to do something that I’m not sure can be done

My friends and I play Blood on the Clocktower (BOTC) via asynchronous Discord chat. I’m working on this multi-page doc to help those of us who want to run games easily see information in various views to help them be effective storytellers.

The problem I’m currently running into is being able to correctly identify a player’s nearest neighbors, accounting for whether players are alive or dead. Because BOTC is played in a circle, Player 1’s neighbor “to the left” (Above Neighbor in my table) would be “Player 14” and the player “to the right” (Below Neighbor in my table) would be Player 2. But if Player 1 dies, I can’t figure out how to get my formulas to essentially skip over Player 1 - and thus say that Player 2’s left neighbor is now Player 14 instead of Player 1.

Here is how I’ve set it up:

Seat is calculated with this formula:

rank([thisRow.Rank],[thisTable.Rank].ToNumber()

I cobbled together these formulas from reading other forums for Above Neighbor and Below Neighbor.

Above Neighbor formula:

SwitchIf(
    thisRow.[Seat #] = 0,
  thisTable
    .Filter(
      [Seat #] < thisRow.[Seat #] + varPlayerCount
    )
    .Sort(
      true, thisTable.[Seat #]
    )
    .Last(),
  thisRow.[Seat #] = 1,
  thisTable
    .Filter(
      [Seat #] < thisRow.[Seat #] + varPlayerCount
    )
    .Sort(
      true, thisTable.[Seat #]
    )
    .Last(),
  thisRow.[Seat #] >= 1 AND thisRow.[Seat #] <= varPlayerCount,
  thisTable.Filter([Seat #] < thisRow.[Seat #])
    .Sort(
      true, thisTable.[Seat #]
    )
    .Last()
)

Below neighbor formula:

SwitchIf(
  thisRow.[Seat #] = 0,
  thisTable.Filter([Seat #] > thisRow.[Seat #])
    .Sort(
      true, thisTable.[Seat #]
    )
    .first(),
  thisRow.[Seat #] = 1,
  thisTable.Filter([Seat #] > thisRow.[Seat #])
    .Sort(
      true, thisTable.[Seat #]
    )
    .first(),
  thisRow.[Seat #] > 1 AND thisRow.[Seat #] < varPlayerCount,
  thisTable.Filter([Seat #] > thisRow.[Seat #])
    .Sort(
      true, thisTable.[Seat #]
    )
    .first(),
  thisRow.[Seat #] = varPlayerCount,
  thisTable
    .Filter(
      [Seat #] > thisRow.[Seat #] - varPlayerCount
    )
    .Sort(
      true, thisTable.[Seat #]
    )
    .first()
)

I previously had the seat formula include that if the player wasn’t marked as “alive,” then the seat number would become 0. But that’s where I hit a dead end, because it broke my above/below neighbor formulas.

Would love anyone’s help to solve this or come up with something better!

[Pablo_DV]

Hello Holly,

Welcome to the community!

I don’t know the rules of the game, so I might have simplified something that doesn’t make sense to simplify. If so, just let me know.

Based on my simplified set up (turning Seat # into an editable value and separating your table in two), here you have the formulas for the Next and Previous Player.

Let me know if anything is not clear and I can explain more in detail.

Next player

If(
  thisRow.Player.Status=Dead,"" , 
  //If player in current seat is dead, we don't show anything and stop calculating
  [DB Seats].filter(Player.Status!=Dead).Sort(True(),[DB Seats].[Seat #]).Let(FilteredTable, 
  //filter the seats with alive players, sort it according to 'seat #' and store it under 'FilteredTable'
  let(
    If(
      (thisRow.[Seat #]+1)>[Seat #].Max(),
      [Seat #].Min() ,
      thisRow.[Seat #]+1
    ),
    NextSeat,
    //Determine what should be the 'Seat #'' of the next player and store it under 'NextSeat'
    FilteredTable.Filter([Seat #]>=NextSeat).IfBlank(FilteredTable).First().Player 
    //Filter all the players with seat equal or bigger than NextSeat and select the first. 
    //If there are none (the result of the filter is blank) select the first of all the seats with players that are still alive
  )
  )
)

Previous Player

thisTable.Filter(NextPlayer=thisRow.Player).First().Player

And here you have a demo, that you can duplicate and adjust to your needs:

Hope this helps!

Pablo

Edited to stay DRY

[Rickard_Abraham]

Wanted to give this a try too! Seemed fun

I went for the pretty common index-based approach, which is usually only thisTable.Find(thisRow), but it we apply a filter there we can achieve the dynamic skipping you asked for

Index (Turn Skipcolumn into a formula to suit your needs)

thisTable.Filter(Skip.Not()).Find(thisRow)

Next Row (Make circular by changing to 1 if index is equal to max index)

thisTable.Filter(Index=If(thisRow.Index=thisTable.Index.Max(), 1, thisRow.Index + 1)).First()

Prev Row (Just use next row to stay DRY)

thisTable.Filter(Next=thisRow).First()

[Pablo_DV]

@Rickard_Abraham

Prev Row (Just use next row to stay DRY)

thisTable.Filter(Next=thisRow).First()

D’oh! Should have thought of that

I will edit my answer

[Holly_Shkolnick]

Hey Pablo! Thanks so much for your detailed reply. On my first try, your solution isn’t working - is it possible to use the data that’s all in the same table? Or should I use a view of all player data in the equation? Here’s some screenshots of what’s happening and then what is in my code blocks:

I changed the Seat # column to an editable block (though if at all possible, I would love for this to be automatically calculated by the row ID/rank).

In my ‘Above Neighbor’ column, I have:

thisTable.Filter([Below Neighbor]=thisRow.Player).First().Player

In my ‘Below Neighbor’ column, I have:

If(
  thisRow.[Player Status]!=Alive," ", 
  //If player in current seat is dead, we don't show anything and stop calculating
  [All Player Data].filter([Player Status]=Alive).Sort(True(),[All Player Data].[Seat #]).Let(FilteredTable, 
  //filter the seats with alive players, sort it according to 'seat #' and store it under 'FilteredTable'
  let(
    If(
      (thisRow.[Seat #]+1)>thisRow.[Seat #].Max(),
      thisRow.[Seat #].Min() ,
      thisRow.[Seat #]+1
    ),
    NextSeat,
    //Determine what should be the 'Seat #'' of the next player and store it under 'NextSeat'
    FilteredTable.Filter(thisRow.[Seat #]>=NextSeat).IfBlank(FilteredTable).First().Player 
    //Filter all the players with seat equal or bigger than NextSeat and select the first. 
    //If not Just select the first of all the seats with players that are still alive
  )
  )
)

Wonder if you see any immediate issues that would help me troubleshoot what’s happening here! I also noticed that in your columns, the players are showing up with related data but aren’t doing that for me.

[Pablo_DV]

The way I implemented allows for fix seat numbers, seat removal and seat exchange between players, because a quick search showed that was the case sometimes.

If you don’t need that, you can use Rikard’s solution, which is simpler and more elegant.

Here you have his proposal adapted to your use-case