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?
-
Rainer, hi!
Is there any way to order tables by date?
this is my code:
var fecha = @All of Fecha;
var desc = @All of Comentarios;
var monto = @All of Monto Fc;
var lines = [];
for(var i = 0; i < fecha.length;i++){
lines.push(moment(fecha[i]).format("DD/MM/YYYY") + " | " + desc[i] + " | " + monto[i]);
};
"Fecha de Factura | Proyecto | Monto de Fc \n" +
"--- | --- | --- \n"+
lines.join("\n") -
Hi Marcos,
yes, it's possible by the .sort() function. But it needs a bit more code (more about .sort() http://www.w3schools.com/jsref/jsref_sort.asp ).
var fecha = @All of Fecha;
var desc = @All of Comentarios;
var monto = @All of Monto Fc;
var lines = [];
for(var i = 0; i < fecha.length;i++){
lines.push(moment(fecha[i]).format("DD/MM/YYYY") + " | " + desc[i] + " | " + monto[i]);
};
var lines = lines..sort(function(a,b){
a = moment(a.split(" | ").shift(),"DD/MM/YYYY").toDate();
b = moment(b.split(" | ").shift(),"DD/MM/YYYY").toDate();
return a - b;
});
"Fecha de Factura | Proyecto | Monto de Fc \n" +
"--- | --- | --- \n"+
lines.join("\n")In the line: return a - b you can define if the sorting is ascending or descending (just change a and b).
Rainer
-
Rainer,
Is it possible to create an amortization mortgage schedule through a calculation field and have display in a table. Like the picture below. I have number of payments field, Total financed amount, and interest rate. I think i need to add a field cumulative principal, cumulative interest, and principal bal though. But didnt want to start til i was sure it was possible. Thanks
-
Hi Jennifer,
yes, such a table is possible. And you don't need a field for each column, the values for a column can be calculated in the same code which creates the table. I've tables, where no column represents a field.
Rainer
rg@delos-consulting.com -
Hi Rainer. Trying to do a table with SELLER's and BUYER's as top headers, and NAME, Phone, eMail, ATTORNEY, Phone, eMail, RE AGENT, Phone, eMail, RE AGENCY, Address as left side headers. I was trying this entry, but it keeps giving me ILLEGAL in Podio. I must be doing sth wrong here..... :-(
var firstLine = ["", @SELLER'S, @BUYER'S];
var secondLine = ["**NAME**", @SELLER(S) NAME, @BUYER(S) NAME];
var thirdLine = ["**Phone**", @S's Phone, @B's Phone];
var fourthLine = ["**eMail**", @S's eMail, @B's eMail];
var fifthLine = ["**Address**", @S's Full Address, @B's Full Address];
var sixthLine = ["**ATTORNEY**", @S's ATTORNEY NAME, @BUYER's ATTORNEY NAME];
var seventhLine = ["**Address**", @S's Lawyer Address, @B's Lawyer Address];
var eighthLine = ["**Phone**", @S's Lawyer Phone, @B's Lawyer Phone];
var ninthLine = ["**eMail**", @S's Lawyer eMail, @B's Lawyer eMail];
var tenthLine = ["**RE AGENT**", @S's REALTOR NAME, @B's REALTOR NAME];
var eleventhLine = ["**Phone**", @S's RE Agcy Phone, @B's RE Agcy Phone];
var twelfthLine = ["**eMail**", @S's RE Agcy eMail, @B's RE Agcy eMail];
var thirteenthLine = ["**RE AGENCY**", @S's RE Agency Address, @B's RE Agcy Address];var start = "";
var l = “\n:— | :— | :—\n”;
var i = 0;
for(; i < firstLine.length; i++){
start = start.concat(firstLine[i]);
start = start.concat(” | “);
}
for(start += “\n”; i > 0; i –){
start = start.concat(“:—|”);
}
for(start += “\n”; i < firstLine.length; i++){
start = start.concat(secondLine[i]);
start = start.concat(” | “);
}
if(thirdLine.length != 0){
for(start += “\n”, i = 0; i < firstLine.length; i++){
start = start.concat(thirdLine[i]);
start = start.concat(” | “);
} }
if(fourthLine.length != 0){
for(start += “\n”, i = 0; i < firstLine.length; i++){
start = start.concat(fourthLine[i]);
start = start.concat(” | “);
} }
if(fifthLine.length != 0){
for(start += “\n”, i = 0; i < firstLine.length; i++){
start = start.concat(fifthLine[i]);
start = start.concat(” | “);
} }
if(sixthLine.length != 0){
for(start += “\n”, i = 0; i < firstLine.length; i++){
start = start.concat(sixthLine[i]);
start = start.concat(” | “);
} }
if(seventhLine.length != 0){
for(start += “\n”, i = 0; i < firstLine.length; i++){
start = start.concat(seventhLine[i]);
start = start.concat(” | “);
} }
if(eighthLine.length != 0){
for(start += “\n”, i = 0; i < firstLine.length; i++){
start = start.concat(eighthLine[i]);
start = start.concat(” | “);
} }
if(ninthLine.length != 0){
for(start += “\n”, i = 0; i < firstLine.length; i++){
start = start.concat(ninthLine[i]);
start = start.concat(” | “);
} }
if(tenthLine.length != 0){
for(start += “\n”, i = 0; i < firstLine.length; i++){
start = start.concat(tenthLine[i]);
start = start.concat(” | “);
} }
if(eleventhLine.length != 0){
for(start += “\n”, i = 0; i < firstLine.length; i++){
start = start.concat(eleventhLine[i]);
start = start.concat(” | “);
} }
if(twelfthLine.length != 0){
for(start += “\n”, i = 0; i < firstLine.length; i++){
start = start.concat(twelfthLine[i]);
start = start.concat(” | “);
} }
if(thirteenthLine.length != 0){
for(start += “\n”, i = 0; i < firstLine.length; i++){
start = start.concat(thirteenthLine[i]);
start = start.concat(” | “);
} }
start -
Hi Gus,
honestly, for me your code looks completly wrong. All for-loops have the wrong syntax, the line-array-length would never be 0 but always 3 (so your if-clauses will always return true) cause you've always 3 elements in it: Element 1: the string (left side header), Element 2 can be null or has a value, same with element 3.
I don't know what your IF-condition is for showing a row. If your IF-condition is that min. 1 of the fields has a value (e.g. @S's Phone, != null OR @B's Phone != null) you can try this:
var secondLine = ["**NAME**", @SELLER(S) NAME, @BUYER(S) NAME];
var thirdLine = ["**Phone**", @S's Phone, @B's Phone];
var fourthLine = ["**eMail**", @S's eMail, @B's eMail];
var fifthLine = ["**Address**", @S's Full Address, @B's Full Address];
var sixthLine = ["**ATTORNEY**", @S's ATTORNEY NAME, @BUYER's ATTORNEY NAME];
var seventhLine = ["**Address**", @S's Lawyer Address, @B's Lawyer Address];
var eighthLine = ["**Phone**", @S's Lawyer Phone, @B's Lawyer Phone];
var ninthLine = ["**eMail**", @S's Lawyer eMail, @B's Lawyer eMail];
var tenthLine = ["**RE AGENT**", @S's REALTOR NAME, @B's REALTOR NAME];
var eleventhLine = ["**Phone**", @S's RE Agcy Phone, @B's RE Agcy Phone];
var twelfthLine = ["**eMail**", @S's RE Agcy eMail, @B's RE Agcy eMail];
var thirteenthLine = ["**RE AGENCY**", @S's RE Agency Address, @B's RE Agcy Address];
var arr = [secondLine, thirdline, and so on ];
var i = 0;
var len = arr.length;
var rows = [];
for(i; i < len; i++){
if(arr[i][1] != null || arr[i][2] != null ){
rows.push(arr[i].join(" | ").replace(/null/g,"n.a."));
}};
" | " + @SELLER'S + " | " + @BUYER'S + "\n" +
" --- | --- | --- \n" +
rows.join("\n")Rainer
-
In your first posting it seems you have fields named SELLER'S and BUYER'S
var firstLine = ["", @SELLER'S, @BUYER'S];If you have those fields they seem to be empty and therefore null is shown. If you only want to have the words SELLER'S and BUYER'S as column headers change this line
" | SELLER'S | BUYER'S + \n" +
-
Hi Nicolas,
in general it's
"Col A | Col B | Col C \n"*
"--- | --- | --- \n" +
@field1 + " | " + @ field2 + " | " + @field3 + "\n" +
@field4 + " | " + @ field5 + " | " + @field6 + "\n" +
and so onIf you have many fields maybe the rows can be created more comfortable .But it depends on the content of the fields and in which way/order you want to show them in the table.
Rainer
-
Hi Rainer,
Is there any mistake in the following code? For some reason you see on the bottom right "undefined" but when i check the related item it show "dag(en)" in the field .
RESULT :
CODE :
var omschrijving = @All of Omschrijving Type tarief;
var aantal = @All of Aantal;
var eenheidaantal = @All of Eenheid aantal NL
var prijs = @All of Prijs incl.;
var eenheidprijs = @All of Prijseenheid NL;
var duur = @All of Duur;
var eenheidduur = @All of Eenheid duur NL;
var totaal = @All of Tot. incl. (TL)
var lines = [];for(var i = 0; i < omschrijving.length; i = i + 1)
{lines.push
(
omschrijving[i] + " | "
+ aantal[i] + " " + eenheidaantal[i] + " | "
+ prijs[i] + " €" + eenheidprijs[i] + " | "
+ (duur[i] ? (duur[i] + " " + eenheidduur[i]) : ".") + " | "
+ totaal[i] + " €"
);
};"Omschrijving | Aantal | Prijs | Duur | TOT. \n" +
"--- | --- | --- | --- | --- \n" +
lines.join("\n") -
Hello Nicolas,
I'm sure that in the item Milieutaks the field eenheidduur is empty.
This: (duur[i] ? (duur[i] + " " + eenheidduur[i]) : ".")
doesn't help anything.. If one field is empty, the pulled array doesn't have a "null"-element. Test it:
eenheidduur.length will 2
omschrijving.length will be 3I've described that in some discussions here.
Instead of the "normal" field tokens you've to take the tokens "with null" (I would do it for all @all of fields).
But then you need [i][0] instead of [i] in the for loop. Also: Take i++ in the loop instead of i=i+1.Rainer
-
Rainer,
Would it be possible to have the columns with money values aligned to the right in stead of to the left?
For example in column TOT. :
96.68 €
5.00 €
80.75 €
VIEW :
THIS THE CODE :
var omschrijving = @All of Omschrijving Type tarief with nulls;
var aantal = @All of Aantal with nulls;
var eenheidaantal = @All of Eenheid aantal NL with nulls;
var prijs = @All of Prijs incl. with nulls;
var eenheidprijs = @All of Prijseenheid NL with nulls;
var duur = @All of Duur with nulls;
var eenheidduur = @All of Eenheid duur NL with nulls;
var totaal = @All of Tot. incl. (TL) with nulls;
var lines = [];if(omschrijving.length > 0)
{
for(var i = 0; i < omschrijving.length; i++)
{lines.push
(
omschrijving[i][0] + " | " +
(aantal[i][0] ? (aantal[i][0] + " " + eenheidaantal[i][0]) : "") + " | " +
(prijs[i][0] ? (prijs[i][0].toFixed(2) + " €" + eenheidprijs[i][0]) : "") + " | " +
(duur[i][0] ? (duur[i][0] + " " + eenheidduur[i][0]) : "") + " | " +
(totaal[i][0] ? (totaal[i][0].toFixed(2) + " €") : "")
);
};"Omschrijving | Aantal | Prijs | Duur | TOT. \n" +
"--- | --- | --- | --- | --- \n" +
lines.join("\n")
}
else{""} -
Add colons at the right sight of the dashes for that column (---:), wrap the dashes in colons if you want it centered (:---:)
"Omschrijving | Aantal | Prijs | Duur | TOT. \n" +
"--- | --- | --- | --- | ---: \n" +
lines.join("\n")https://github.com/adam-p/markdown-here/wiki/Markdown-Cheatsheet
-
I have an issue with this table that my if condition will only accept the first value of my category field.
Example my category is 1 2 3and my if condition is if( field=="1") ... this works fine BUT if I set if( field=="2") I get error "The script returned an undefined result". AND this being the ONLY change in the table. AND yes there is an item with value 2.
If I Change my category field to 2 1 3 then 2 is accepted and 1 is not. SO this is telling me that only when the first item i the category field is true the if returns without the error.
Very strange. Any ideas? -
The Full code of my calc is:
var deltagerorg=@All of deltagertekst1
var deltager=@All of deltagertekst2
var status=@All of Status
var url="https://podio.com/cleancluster/clean-events/apps/deltagere/items/"
var id=@All of UniktIDvar lines=[];
for (i=0; i < deltager.length; i++)
{
if (status[i]=="Deltager")
{
lines.push("["+deltager[i]+"]("+url +id[i]+")" + "|" +deltagerorg[i]);
"Deltager | Organisation \n" + "--- | --- \n"+
lines.join("\n")
}
}And "Deltager" is the first category in the field and therefore the table is shown with the if condition in action. Perfekt!!
BUT if I want to set the second value in my if condition the error appears.
-
Hi Mikkel,
Do the 3 variables all have the same length (number of items in the array)?
And why is the table part of the for loop?
Shouldn't it be:var lines=[];
for (i=0; i < deltager.length; i++)
{
if (status[i]=="Deltager")
{
lines.push("["+deltager[i]+"]("+url +id[i]+")" + "|" +deltagerorg[i]);
}
};
"Deltager | Organisation \n" + "--- | --- \n"+
lines.join("\n") -
Hi Rainer.
Yes I believe the variables are all linked with the same relation and therefore the same "length"
I am not sure I understand what the difference is if the table is inside or outside of the for loop... BUT after trying your suggestion to put the table after the loop It seems to work as intended.
THAT IS BRILLIANT!!
Thank you!
-
You can have as much rows and columns as you want - within the technical limits There are 2 limits:
1. The width of the calculation field limits the number of columns (depending on how wide each column is; I've tables with 20 columns which are all very small).
2. the total number of characters in a calculation field is limited to 512k (incl. blank spaces) -
Quick question - I have created a table that shows all the sales made to a client but really I just want it to show those that have a value greater than zero in the outstanding field. Is this possible and how do I tweak this formula? Id also like it to format the date as DD/MM/YYYY but thats no big problem. Thanks for any advice
var date = @All of Date of Invoice;
var so = @All of Invoice Number;
var due = @All of Outstanding Amount;
var lines = [];
for(var i = 0; i < date.length;i++){
lines.push(date[i] + " | " + so[i] + " | " + due[i]);
};
"Date | Sales Order | Outstanding \n" +
"--- | --- | --- \n"+
lines.join("\n") -
Hi Chris,
var date = @All of Date of Invoice;
var so = @All of Invoice Number;
var due = @All of Outstanding Amount;
var lines = [];
number = Number(due[i]) || 0;
if(number > 0){
for(var i = 0; i < date.length;i++){
lines.push(moment(date[i]).format("DD/MM/YYYY") + " | " + so[i] + " | " + due[i]);
}};"Date | Sales Order | Outstanding \n" +
"--- | --- | --- \n"+
lines.join("\n")Rainer
Please sign in to leave a comment.
Comments
66 comments