Making a matrix of data

I’m trying to build a matrix of data and am not sure how to approach this.
I have a table of people with addresses. Then I have a second table of buildings with addresses. I want to make a 3rd table – the matrix – of the distance between eeach person and each building.

I though I could make a table of lists, but it does not appear possible to apply math formulas to a list of values.

Maybe there’s a clever way to use column names to reference rows in another table?

Maybe some other approach?

Usually we do this by creating a 3rd table with three columns:

  1. Person: Formatted as a lookup to the People table
  2. Building: Formatted as a lookup to the Building table
  3. Distance: Formula column - e.g. =GoogleDirections(Person.Address, Building.Address)

Now the trick is to make sure there is a row for every person and every building. There are a couple ways to do this:

  1. Group the 3rd table by Person on the left and Building on the right. Use the column menu to select “Arrange and Manage Groups” and select to “Show all groups”. This will give you the full matrix and you can then click in each of them.
  2. Write a set of buttons to do this. We’re working on making this easier - but for now, the steps would be something like “Select a business” (either in a control or in a table), then write a button on the people table to add a row to the matrix with {thisPerson, currentlySelectedBusiness}. You can write a button that presses all the buttons in the people table. Then you have to do this once per business.

Here’s a sample doc:

I think with a couple upcoming changes, #2 will become much easier and will become the desired path.

1 Like

Awesome! Thanks so much!