Tables In Calculation Field

Comments

66 comments

  • Rainer Grabowski

    Hi,

    yes, there's a way using markdown 

    var mix = @all of unit mix;
    var bd = @all of breakdown;
    var rent = @all of unit rent;
    var lines = [];
    for(var i = 0; i < mix.length;i++){
    lines.push(mix[i] + " | " + bd[i] +  " | " + rent[i]);
    };
    "Unit Mix |  Breakdown | Rents \n" +
    "--- | --- | --- \n"+
    lines.join("\n")

    Tables are not supported in the mobile apps, so they are only visible in the browser.

    Rainer 

    0
    Comment actions Permalink
  • A.R.E.I.S Support

    Hi Rainer,

     

    Thank you for the reply. The code worked but the image looks off. Here is a screen shot. 

    var mix = @All of Unit Mix Breakdown by Bed/Bath;
    var bd = @All of Unit Mix Breakdown by Quantity;
    var rent = @All of Unit Rents Breakdown by Unit Mix;
    var lines = [];
    for(var i = 0; i < mix.length;i++){
    lines.push(mix[i] + " | " + bd[i] + " | " + rent[i]);
    };
    "Unit Mix | Breakdown | Rents \n" +
    "--- | --- | --- \n"+
    lines.join("\n")

     

    0
    Comment actions Permalink
  • Rainer Grabowski

    The code seems to be correct. Can you please share a screenshot of the calculation and  of an item with the 3 fields  Unit Mix Breakdown by Bed/Bath etc.

    0
    Comment actions Permalink
  • A.R.E.I.S Support

    Sure. Here you go.

     



    0
    Comment actions Permalink
  • Rainer Grabowski

    Ok, I see where the problem is. It seems, you don't pull the values from 5 related items, but only from one. Just to clarify:

    The calculation field (table) is in App A, the other fields are calculation fields with lists in App B. In these App B fields  you pull the data from 5 related items in App C - is that right?

    The calculation code I've provided is made for  the use case that there is only one value in each field not a list. So if B is related to 5 items in C, my calculation would show the correct table in a calculation field in B. If you want the table in app A you first have to split the values in each @all of -Array. At the moment you have only one value (the list) in each of the 3 @all of-Arrays and cause there are line breaks in each list, the table isn't show correctly. This code should split the arrays and create the table:

    var mix = @All of Unit Mix Breakdown by Bed/Bath.join().split("\n");
    var bd = @All of Unit Mix Breakdown by Quantity.join().split("\n");
    var rent = @All of Unit Rents Breakdown by Unit Mix.join().split("\n");
    var lines = [];
    for(var i = 0; i < mix.length;i++){
    lines.push(mix[i] + " | " + bd[i] + " | " + rent[i]);
    };
    "Unit Mix | Breakdown | Rents \n" +
    "--- | --- | --- \n"+
    lines.join("\n") 

    Rainer

     

     

    0
    Comment actions Permalink
  • A.R.E.I.S Support

    That worked! Thanks!

    0
    Comment actions Permalink
  • A.R.E.I.S Support

    Hi Rainer,

     

    Quick question. Is there a way to center and bold the text in the table?

    0
    Comment actions Permalink
  • Rainer Grabowski

    Yes, that's possible. 

    Wrap the 3 dashes for the column you want to center in colons:

    :---:

    Colons right ---: does right align. 

    Bold: wrap the words /colons in double asterisks **should be bold**,  wrapping in single asterisks *italic*.
    This would bold each string in the column "Breakdown"

    lines.push(mix[i] + " | **" + bd[i] + "** | " + rent[i])

    Here you find more about markdown.

    Rainer

    0
    Comment actions Permalink
  • Felix Lepoutre

    Love this. Just wanna share another example for the people. 

    This is my input 

    25 - "Description" - 45,50 - 21
    25 - "Description" - 45,50 - 19


    25 - "Description" - 45,50 - 06

    This is my calc

    field_array = @Fields.split('\n')
    var lines = [];
    var newline = "";
    for(var i = 0; i < field_array.length;i++){
    newline = ""
    field_split = field_array[i].split(' - ')
    for(var ii = 0; ii < field_split.length;ii++){
    newline = newline + field_split[ii] + " | "

    }
    lines.push(newline + "");
    };

    "Amount | Description | Price | VAT \n" +
    "--- | --- | --- | --- \n" +
    lines.join("\n")
    0
    Comment actions Permalink
  • ROBOT

    I am trying to get this to work, but having problems with line breaks. Cold you have a look at this?

    var datoVAR = @All of Dato.join().split("\n");
    var typeVAR = @All of Type.join().split("\n");
    var kategoriVAR = @All of Kategori.join().split("\n");
    var timerVAR = @All of Timer.join().split("\n");
    var lines = [];
    for(var i = 0; i < datoVAR.length;i++){
    lines.push(typeVAR[i] + " | " + kategoriVAR[i] + " | " + timerVAR[i]);
    };
    "Type | Kategori | Timer \n" +
    "--- | --- | --- \n"+
    lines.join("\n")

    0
    Comment actions Permalink
  • Rainer Grabowski

    Hi Jonas,

    why do you split your first 4 vars by "\n"? It seems that the length of each var is 1 after the split.
    Remove .join().split("\n") from each var and try again.

    Rainer

    0
    Comment actions Permalink
  • ROBOT

    Thanks a lot Rainar!

    Works perfectly. I have a new problem however... With date field, I would like to format it. Without formatting it works perfectly but not with...

    My code:

    var datoVAR = @All of Dato ? moment(@All of Dato).format('YYYY-MM-DD') : ' ';
    var typeVAR = @All of Type;
    var kategoriVAR = @All of Kategori;
    var timerVAR = @All of Timer;
    var sumVAR = @Sum of Timer;
    var itemidVAR = @All of PODIO ITEM ID (calc);
    var lines = [];
    if (datoVAR!='') {
    for(var i = 0; i < datoVAR.length;i++){
    lines.push(datoVAR[i] + ' | ' + typeVAR[i] + ' | ' + kategoriVAR[i] + ' | ' + timerVAR[i]);
    };
    "Dato | Type | Kategori | Timer \n" +
    "--- | --- | --- | --- \n"+
    lines.join("\n") +
    "\n **Total** | | | **" + sumVAR + "**\n"
    }

    0
    Comment actions Permalink
  • Rainer Grabowski

    Hi Jonas,

    @All of Dato returns multipe Dates (as an Array), but moment().format you can use for one date only. So you have to format each one in the for-loop.

    var datoVAR = @All of Dato;
    var typeVAR = @All of Type;
    var kategoriVAR = @All of Kategori;
    var timerVAR = @All of Timer;
    var sumVAR = @Sum of Timer;
    var itemidVAR = @All of PODIO ITEM ID (calc);
    var lines = [];
    for(var i = 0; i < datoVAR.length;i++){
    lines.push(moment(datoVAR[i]).format('YYYY-MM-DD') + ' | ' + typeVAR[i] + ' | ' + kategoriVAR[i] + ' | ' + timerVAR[i]);
    };
    var table = "Dato | Type | Kategori | Timer \n" +
    "--- | --- | --- | --- \n"+
    lines.join("\n") +
    "\n **Total** | | | **" + sumVAR + "**\n";
    datoVAR != '' ? table : ''

    Rainer

      

     

    0
    Comment actions Permalink
  • ROBOT

    Thanks Rainer,

    It works perfectly!

    0
    Comment actions Permalink
  • ROBOT

    Hi Rainer,

    Sorry but this is really annoying me...I simply can't figure it out myself.

    When I try to put in direct links to items it breaks up my table. If I remove the link, then the table works perfectly fine.

    My code:

    var id = @All of PODIO unikt ID;
    var nameVAR = @All of Fuldt navn;
    var telefonVAR = @All of Telefon;
    var emailVAR = @All of Email;
    var url = "https://podio.com/northernbcom/kunder/apps/kontaktpersoner/items/";
    var urltext = "Gå til kontakt";
    var lines = [];
    for(var i = 0; i < nameVAR.length;i++){
    lines.push(nameVAR[i] + ' | ' + telefonVAR[i] + ' | ' + emailVAR[i] + ' | ' + urltext.link(url + id[i]));
    };
    var table = "Navn | Telefon | E-mail | Link til kontakt \n" +
    "--- | --- | --- | --- \n"+
    lines.join("\n");
    nameVAR != '' ? table : ''

    0
    Comment actions Permalink
  • Rainer Grabowski

    You can't use urltext.link(url + id[i])) cause this breaks the markdown. You have to create the link also by markdown:
    "[" + urltext + "](" + url + id[i] + ")"

    0
    Comment actions Permalink
  • ROBOT

    Thanks again Rainer :) It works perfectly with the links.

    However I can see that my e-mail and phone fields does not work. I tried to put in 

    emailVAR.map(function (obj) { return obj.value}).join()

    but can not get it to work.

    0
    Comment actions Permalink
  • ROBOT

    Would it be possible to combine several results in the same calculation field in the same list?

     

    Field 1    Field 2     Field 3

    Relation 1, item 1

    Relation 1, item 1

    Relation 2, item 1

    etc.?

    0
    Comment actions Permalink
  • Rainer Grabowski

    Did you try 

    emailVAR[i].map(function (obj) { return obj.value}).join() 

    in lines.push()?

    0
    Comment actions Permalink
  • Rainer Grabowski

    Sorry, I don't understand your last question. What is Field 1    Field 2     Field 3? A table head?

    0
    Comment actions Permalink
  • ROBOT

    Could I do something like this (I have another APP where I want to pull information from 5 different APPs into the same table):

    var id = @All of PODIO unikt ID;
    var nameVAR = @All of Fuldt navn;
    var telefonVAR = @All of Telefon;
    var emailVAR = @All of Email;
    var url = "https://podio.com/northernbcom/kunder/apps/kontaktpersoner/items/";
    var urltext = "Gå til kontakt";

    var id2 = @All of PODIO unikt ID;
    var nameVAR2 = @All of Fuldt navn;
    var telefonVAR2 = @All of Telefon;
    var emailVAR2 = @All of Email;
    var url2 = "https://podio.com/northernbcom/kunder/apps/kontaktpersoner2/items/";
    var urltext2 = "Gå til kontakt";


    var lines = [];
    for(var i = 0; i < nameVAR.length;i++){
    lines.push(nameVAR[i] + ' | ' + telefonVAR[i] + ' | ' + emailVAR[i] + ' | ' + "[" + urltext + "](" + url + id[i] + ")");
    };

    lines.push(nameVAR2[i] + ' | ' + telefonVAR2[i] + ' | ' + emailVAR2[i] + ' | ' + "[" + urltext2 + "](" + url2 + id2[i] + ")");
    };


    var table = "Navn | Telefon | E-mail | Link til kontakt \n" +
    "--- | --- | --- | --- \n"+
    lines.join("\n");
    nameVAR != '' ? table : ''

    0
    Comment actions Permalink
  • ROBOT

    Regarding:

    emailVAR[i].map(function (obj) { return obj.value}).join() 

    it says:

    Script-error TypeError: undefined is not a function

    complete code:

    var id = @All of PODIO unikt ID;
    var nameVAR = @All of Fuldt navn;
    var telefonVAR = @All of Telefon;
    var emailVAR = @All of Email;
    var url = "https://podio.com/northernbcom/kunder/apps/kontaktpersoner/items/";
    var urltext = "Gå til kontakt";
    var lines = [];
    for(var i = 0; i < nameVAR.length;i++){
    lines.push(nameVAR[i] + ' | ' + telefonVAR[i] + ' | ' + emailVAR[i].map(function (obj) { return obj.value}).join() + ' | ' + "[" + urltext + "](" + url + id[i] + ")");
    };
    var table = "Navn | Telefon | E-mail | Link \n" +
    "--- | --- | --- | --- \n"+
    lines.join("\n");
    nameVAR != '' ? table : ''

     

    0
    Comment actions Permalink
  • Rainer Grabowski

    Is Email a text field or an Email field? Do all email fields have a value or are some empty?

    0
    Comment actions Permalink
  • ROBOT

    its an e-mail field. They can be empty

    0
    Comment actions Permalink
  • Rainer Grabowski

    If there are empty fields the whole calculation won't work with @all of field. You have to use @all of field with nulls (see my post here: https://help.podio.com/hc/en-us/community/posts/222414428/comments/232268707)

    Then do:

    for(var i = 0; i < nameVAR.length;i++){
    EMAIL =  emailVAR[i][0] != null ? emailVAR[i][0].map(function (obj) { return obj.value}).join() : "-"
    lines.push(nameVAR[i] + ' | ' + telefonVAR[i] + ' | ' + EMAIL + ' | ' + "[" + urltext + "](" + url + id[i] + ")");
    };

    0
    Comment actions Permalink
  • ROBOT

    It still gives me an error: Undefined is not a function

    var id = @All of PODIO unikt ID;
    var nameVAR = @All of Fuldt navn;
    var telefonVAR = @All of Telefon;
    var emailVAR = @All of Email;
    var url = "https://podio.com/northernbcom/kunder/apps/kontaktpersoner/items/";
    var urltext = "Gå til kontakt";
    var lines = [];
    for(var i = 0; i < nameVAR.length;i++){
    EMAIL = emailVAR[i][0] != null ? emailVAR[i][0].map(function (obj) { return obj.value}).join() : "-"
    lines.push(nameVAR[i] + ' | ' + telefonVAR[i] + ' | ' + EMAIL + ' | ' + "[" + urltext + "](" + url + id[i] + ")");
    };
    var table = "Navn | Telefon | E-mail | Link \n" +
    "--- | --- | --- | --- \n"+
    lines.join("\n");
    nameVAR != '' ? table : ''

    0
    Comment actions Permalink
  • ROBOT

    With the "old" code it got me the following results (where only the last contact was without any information in e-mail)

    NavnTelefonE-mailLink 

    Mejken Dupont[object Object][object Object]Gå til kontakt

    Jonas Markou[object Object][object Object]Gå til kontakt

    Jonas TEST MarkouundefinedundefinedGå til kontakt

    0
    Comment actions Permalink
  • Rainer Grabowski

    The new code only works with the tokens @all of field with nulls.
    The [object Object] in the old code means: you have to use .map(). But .map() doesn't work with "undefined" (= empty field).

    0
    Comment actions Permalink
  • Christopher Gibbins

    What if I want to have one column have static text in there.

    In this example Rate and Price are static and wont ever change.

    Example

    Type | Company 1 | Company 2

    ---|---|---

    Rate | 1% | 2%

    Price | $5.00 | $6.00
    0
    Comment actions Permalink
  • Rainer Grabowski

    Depends on how you get the numbers for the columns Company 1 and 2.

    0
    Comment actions Permalink

Please sign in to leave a comment.

Powered by Zendesk