Tables In Calculation Field
I have three fields in a relationship app that I would like to combine together to show as a table.
The fields are Unit Mix, Breakdown, and Rents.
Below is a picture of a replica (without the gridlines)
Is there a way to do this?
-
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
-
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") -
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
-
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
-
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 - 06This 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") -
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") -
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"
}
-
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
-
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 : ''
-
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 : '' -
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 : '' -
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] + ")");
}; -
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 : '' -
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
Please sign in to leave a comment.
Comments
66 comments