TECHeGO
TECHeGO

Creating Dynamic Markdown Tables in Podio Calculation Fields

I recently figured out how to make 100% Dynamic Markdown Tables in Podio. You can create tables with as many rows and/or as many columns as you want, and its dynamic (supports a variable number of rows and/or columns). It’s pretty amazing!

For this example, let’s make the table a small project dashboard that shows each project and employee working on it. Here are the steps you need to take to recreate it:

1. Declare a variable that you will use at the end of the calculation. This variable will contain the actual Markdown Table. Let’s call it markdownTable:
var markdownTable = “”;

1-a. If you want to leave the top left cell of the table blank (so you have rows that match to columns), then you need to make it a non-breaking space:

markdownTable += “ 

2. Let’s say you have a related app called “Projects”, and you want the related project names to be the column headers.

Here’s how to set up that calculation:

for(i=0;i<@AllOfProjectTitle.length;i++){

markdownTable += “ | “ + @AllOfProjectTitle[i]

}

*the ‘ |’ character is how you set up a markdown table. You have to put | (a bar) with spaces around it, between every column.

If, for example, your dashboard app has three related projects, your table headers will look like this inside the variable:
&nbsp; | Project Title 1 | Project Title 2 | Project Title 3

3. Next we have to add text that makes the markdown table actually turn into a table.

To do this, add “--- | --- | ---” etc for as many columns as you have. If the number of columns will vary, then we’re going to need a loop for that as well.


You can use the same loop as the project titles, since that will give us the same number of columns(however you do need to start a new loop, even if its the same as the previous one).

We will use this loop to add a line break and the initial column: (see http://www.techego.com/blog/item/podio-calculations-adding-a-line-breakfor more info on line breaks in Podio calculation fields)
markdownTable += “\n---”

Then we add the rest of the columns:

for(i=0;i<@AllOfProjectTitle.length;i++){

markdownTable += “ | :---:”

}

*I added the colon around each of the ‘---’ because that centers the text in that column, which in this case I want. It is default left-align, and adding a colon only on the right side of the ‘---’ will right-align it.

Now everything will appear as a table as long as we add anything else after that line.

4. Next we need to add a variable that will hold information for the rest of the table. And yes, you read that right- one single variable will hold the entire rest of the table data.
Now that is a bit misleading as the variable will be an array with multiple objects in it, so in reality it’s a variable of variables.


Let’s name this variable after the main row headers, in this case the employees:

var employeeList = [];

*We set the variable equal to “[]” because that declares it as an array. By doing so we can simply push data into the variable as an array and the variable won’t get confused and think the variable is supposed to be a string or some other variable type.

4-a.. One thing we’ll want to do is get a list of Unique Employees. This way we never have 2 rows with the same employee name. You can do this with a quick function:

function onlyUnique(value, index, self){

return self.indexOf(value) === index;

}

var employeeName = @All of Responsible Employee;

var uniqueEmployee = employeeName.filter( onlyUnique );

* If the employee field is a relationship in the project app, you will need a calculation that pulls the name of the employee into a separate calculation field.

5. The variable uniqueEmployee has a list of all employees associated with the projects. Now, this is where things get a bit tricky. We need to get the data of which employee is assigned to which project all into this variable:

for(i=0;i<uniqueEmployee.length;i++){

employeeList[i] = {Name: uniqueEmployee[i]}

*employeeList[i] makes it so that for each ‘i’ value there is a unique value in the array. So employeeList[0] is one set of information and employeeList[1] is another, and so on)

**(don’t close the ‘i’ for loop yet)

6. Next we need to declare the rest of the properties inside the object. To do this we need a new “for” loop inside the one above. Set them to html non-breaking-spaces (&nbsp;):

for(j=0;j<@AllOfProjectTitle.length;j++){

employeeList[i][“Project”+j] = “&nbsp;”

}//end for j loop

}//end for i loop

*(putting [“Project”+j] after the employeeList[i] declares a new property called “Project#” for each existing Project, inside the current ‘i’ array of employeeList. the ‘+j’ portion will add a number to the end)

**(putting ‘//’ and any text after it makes a comment in javascript, this is so you can keep track of the closing of each loop easier in case you have issues in the scripts)

7. Next, we must figure out which employee is associated with which project and mark that in the values we just created. To do this, we will need 2 for loops again (the ‘i’ one on the outside and a ‘j’ one inside that) using the same parameters as before. We should break this into new loops to make sure once we set the values it doesn’t reset them to “&nbsp;” again:

for(i=0;i<uniqueEmployee.length;i++){

for(j=0;j<@AllOfProjectTitle.length;j++){

if(@All of Responsible Employee[j] === uniqueEmployee[i]){

employeeList[i][“Project”+j] = “X”

}//end if

}//end for j loop

}//end for i loop

*This checks to see if the current unique employee in the loop is equal to the employee in the current project being looped through. If they are equal it will change the value to an “X” instead of the “&nbsp;”)

8. Now that we have all the values in the employeeList variable, we need to add that data to our table variable. We’ll need both for loops again to do this:
for(i=0;i<uniqueEmployee.length;i++){

markdownTable += “\n” + employeeList[i][“Name”]

for(j=0;j<@AllOfProjectTitle.length;j++){

markdownTable += “ | “ + employeeList[i][“Project”+j];

}//end for j loop

}//end for i loop

9. All that is left to do is write the table variable so that Podio will show it:
write = markdownTable;

Voila! You now have a Project Dashboard Table that looks like this in Podio (if this were the data you had):


 

Project 1

Project 2

Project 3

Project 4

Employee 1

X

   

X

Employee 2

 

X

   

Employee 3

   

X

 

This is what the table would look like if I only have 4 projects attached. For every other project I relate, the code will add a new column and a new employee (if applicable) automatically.

100% Dynamic!

There are many more things you can do inside the tables for data. Some examples:

  • Count different things in an app

  • Add up numbers/totals

  • Show statuses from a category field

  • Show which times different things happened

I’m sure there are more ways to use these tables than this, be creative!

The only real limit to watch out for with Dynamic Markdown Tables is how wide Podio is. Once there are too many columns with long text it starts auto-breaking the lines, so it can look really bad if there is too much data.At TECHeGO, we’ve done more complicated versions of this kind of table, so if you want help setting up one or more custom dashboard tables like this, please feel free to contact us for help.