Deliverable summary table with hyperlinks
I've been learning javascript for the purpose of customising my Podio apps, and I thought I would share this with the member of the community here
The scope was to create a table that summarised the deliverables linked to a project showing their name (that is also an hyperlink to the item in the deliverables app), their status, and their % progress with a final average of the progress.
Here is the result
the code to achieve this is as follows
- var url = "https://podio.com/accountName/projects/apps/deliverables/items/"
- var markdownTable = "|Deliverable |Staus |Progress |";
- markdownTable += "\n" + "|------------------------|:--------------:| -------: |";
- for (i=0; i<@All of Deliverable Summary.length; i++) {
- markdownTable += "\n |" + "[" + @All of Deliverable Summary[i] + "]("+ url + @All of delID[i] + ")" + " | *" + @All of Status[i] + "* | " + @All of Deliverable Progress[i] * 100 + "% |"};
- markdownTable += "\n | |----------------|-----------|"
- markdownTable += "\n | |** Combined Progress: **|" + "**" + @Avg of Deliverable Progress + "% **";
- write = markdownTable
I'm pretty green to javascript but I'll try to explain it line by line for those interested, and so I can return to refresh my memory of how I achieved it
a few things first
- =+ --> appends the following data to the existing variable
- "\n" --> starts a new line
- when you call up an @All of Deliverable Summary this is considered a Javascript array (sadly you cannot set this array to a variable and have that variable continue to be an array)
- a great video to get started on javascript is here
- and of course w3schools.com for Javascript and Javascript reference
- using markdown formatting. the markdown cheatsheet for tables
OK so lets get started
1. specify the url variable to make it easier to reuse later. The url was copy pasted (minus the last number) from an item in the deliverables app. The bold sections will need to be replaced to suit your Podio setup
2. specify the markdownTable variable and set to the table header (refer the markdown cheatsheet for tables)
3. append to the markdownTable the next line of formatting (the colons ":" are used to specify justification of the text in that column)
now it starts to get interesting. I hope I make it simple enough to any people starting out
4. start the for loop and loop for the length of the array (the length of the array is equal to then number of referenced deliverables). i is used to return the value of the array item at array index i
5. there is a bit happening in this line. but simply put it is concatenating all the formats and values (including calculated values) in the body of the table. Lets look into this a little. It starts with a new line, then adds the formatting for the table with | , then adds the required formatting to create the hyperlink - [This link](http://example.net/)
All of this has to be done with concatenations ( + ) to make it all work.
- NOTE: the @All of delID[i] returns the id number of of the deliverable item. I had to create this calculated field in the Deliverable app. You will need to make sure it is only the integer and not any additional formatting you may have setup for your unique ID. For example mine was formatted to "D0001", but I need to return just "1" for the hyperlink to work. Doing this was more difficult than I thought it would be. See below for the code used to calculate that
6. this appends a separator row to the table with ---
7. appends the average progress to the bottom of the table
8. writes the table into the calculation field
Pure Javascript writes (or outputs) using document.write(variable) however this does not seem to work in Podio calculation fields. the simple function write = variable is used instead.
This is the code I used to get the unique ID in the deliverables app so I could create the hyperlink
var txt = @Unique ID.substr(1,8) // strips the D off the front and assigns it to the variable txt
var j=0 // sets up the varialbe to be used to determine where to slice the string to get the unique ID
while(j<6) { // starts a while loop. will do a maximum of 6 loops
if(txt.charAt(j)!== "0") {break}; //checks that the character at position j in the string is not equal to "0" then exits the loop if true
++j; //increments j by one
};
txt.slice(j) //slice the string at the correct location to give the unique number
I hope this helps someone customise their Podio setup, and I hope when I come back to review it myself, I can remember what I've done.
Also if anyone can tell me cleaner ways to achieve the same things I'd be keen to learn
-
Yes good question Ambiente,
I'm certainly no expert on setting Podio up. And I'm keen to get feedback on better ways to do things.
So to answer why I did it this way... It is my understanding when using relational databases if you are using a many to one relationship it is considered best practice to established that relationship from the app (table) containing the many items not the one item. Therefore rather than create new deliverables via the project app, I create new deliverables from the deliverables app and reference them to the project item. It seems doing this in Podio also creates better grouping/reporting of the deliverables back in the deliverables app. My only problem was that you had to scroll to the bottom of the Projects app to see the "Related Items", which can't be ordered in any way - meaning if a relationship is established from another app first than this appears in the list first etc (I have many apps referencing the projects). Also the "Related Items" list takes a minute to refresh and display those "Related Items". So I thought the best way to put the data right where I wanted it, and maintain relational database best practice, I needed a table as I have achieved. Also down the track I can add functions to filter out certain items (such as the cancelled deliverable), and possibly change the sort order of the items in the table.
Thanks for your question and feedback.
-
Kent,
Thanks for the complete reply. I too have been irritated by the need to scroll to the bottom of an App in Podio to see related items.
I solved my version of the problem using Globiflow, and a relationship field.
I agree that the one to many construct is a 'standard' for those of us at a certain age(!). In my example I have various properties, each with (many) inspection recommendations. I wanted to show all the active recommendations in a summary format in the property app.
I added a relationship field from the property to the recommendations. Then a GF script monitors updates on the recommendation app. Following any update of a recommendation, the script gets the parent property and then finds all the recommendations associated with it. Then it filters the non completed recommendations and write the list of recommendations back into the Property relation field.
This took me quite some time to sort out! But it works well now.
The reason I'm interested with your method is that it uses less screen space to display summary data. and in some ways its more attractive.
I notice that Javascript and Calc Fields vs Globiflow and PHP are overlapping solutions in the Podio universe. I suspect and hope that we will see some harmonisation as the system evolves. Especially around these specific of what I would call 'real' database functionality.
Maybe you've seen this page already. http://www.techego.com/blog/dynamic-markdown-tables-in-podio
It describes (albeit in a rather obscure fashion) a very similar approach to the one you developed. It may give you some inspiration!
good luck
-
Thanks Ambient,
Are you 21 also 😁, I had no idea following that many to one philosophy showed my age... maybe that I didn't know shows it even more!
I don't have a subscription level that grants access to Globiflow. I see its power from a demo I had, but yes I did find it difficult to get into the advanced functions. I was unaware it was based on php, although it makes sense as I'm guessing it is processed at the GF server not at the browser as JavaScript does.
Yes that post was my inspiration, thank you for the reference. I also found it obscure and hard to follow, which was kind of my inspiration to write this post, so I had something to come back to that Mae sense to me.
Have you got a screen capture of the result you managed to achieve with globiflow?
-
Hi Kent - you suggested I look at this solution in relation to my question. Would you mind humouring my less technical brain by saying whether or not this would solve my problem if I explain it a different way?
So - what bugs me about relationships is that I can not look back at the 'history' created at any point - for example, if I was building a workshop app with 'workshops' and 'delegates' with many-to-many relationships. Over time people will have go to more than one workshop, and I want to record something specific about their relationship with each workshop (category; speaker, attendee, chair etc.). At the moment, I would have to store the category within the delegate record, for the most current instance of the category field. After the workshop is over, and the next time a delegate goes to a workshop, that category field may change.
Do I use your method above (or simply do a calculation triggered by a workflow) to record a snapshot of the delegate/workshop relationship category?
Thanks!
Matthew
-
Hi Matthew,
you have to do a snapshot. For that you need either A) a "Delegate/Workshop-History"-App or B) a text field in the Delegates-App where you store a snapshot for each workshop the delegate visited. A) you can achieve with Podio Flows, for B) you would need Globiflow. If you prefer B) I recommend to store each teh entry for each workshop with the same structure like:
Workshop: Workshop title
Date: date
Category: text
xxx: text
yyy: text
This way you can use a calculation field to analyse the entries in the text field. If you prefer A) you can also use a calculation field for an analyze.Rainer
rg@delos-consulting.com
Please sign in to leave a comment.
Comments
7 comments