Post

14 followers Follow
0
Avatar

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?

A.R.E.I.S Support

Please sign in to leave a comment.

62 comments

0
Avatar

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 

Rainer Grabowski 0 votes
0
Avatar

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")

 

A.R.E.I.S Support 0 votes
0
Avatar

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.

Rainer Grabowski 0 votes
0
Avatar

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

 

 

Rainer Grabowski 0 votes
0
Avatar

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

Rainer Grabowski 0 votes
0
Avatar

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")
Felix Lepoutre 0 votes
0
Avatar

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")

Jonas Markou 0 votes
0
Avatar

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

Rainer Grabowski 0 votes
0
Avatar

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"
}

Jonas Markou 0 votes
0
Avatar

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

  

 

Rainer Grabowski 0 votes
0
Avatar

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 : ''

Jonas Markou 0 votes
0
Avatar

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] + ")"

Rainer Grabowski 0 votes
0
Avatar

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.

Jonas Markou 0 votes
0
Avatar

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.?

Jonas Markou 0 votes
0
Avatar

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

Rainer Grabowski 0 votes
0
Avatar

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 : ''

Jonas Markou 0 votes
0
Avatar

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 : ''

 

Jonas Markou 0 votes
0
Avatar

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

Rainer Grabowski 0 votes
0
Avatar

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] + ")");
};

Rainer Grabowski 0 votes
0
Avatar

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 : ''

Jonas Markou 0 votes
0
Avatar

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).

Rainer Grabowski 0 votes
0
Avatar

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
Christopher Gibbins 0 votes