• 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

• I want to show like a progressbar in a calculation field.

How can I do that?

• Thank you so much Rainer!

It works great

I´m not a developer but i make my best to get things work. Thanks to you i have solved lots of issues.

Best regards!

• 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 sixthLine = ["**ATTORNEY**", @S's ATTORNEY NAME, @BUYER's ATTORNEY NAME];
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] != null || arr[i] != null ){rows.push(arr[i].join(" | ").replace(/null/g,"n.a."));}};" &nbsp; | " + @SELLER'S  + " |  " + @BUYER'S + "\n" +" --- | --- | --- \n" +rows.join("\n")`

Rainer

• Hi Rainer. Thank you so much for taking the time. Still no luck w/my table. I'm getting: "Unexpected identifier". :-( • Hi Gus,

in var arr - "and so on" was meant as a placeholder. You have to enter all other vars ...Line there:

var arr = [secondLine,thirdline,forthLine,fifthLine, ... thirteenthLine]

Rainer

• Thanks Rainer. That did the trick. Now I have the table but the top two columns which are supposed to show headers as SELLER on one and BUYER on the other are showing, as "null", and "null".  Any suggestions? Thank you.

• 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

`" &nbsp; | SELLER'S |  BUYER'S + \n" +`
• Right on the spot. THANK YOU.

• Hi,

I want to create a table in a calculation field where all the data are specified in separate other calculations field of the same item.

Anybody knows who to do this?

• Hi Nicolas,

in general it's

`"Col A | Col B | Col C \n"*"--- | --- | --- \n" +@field1 + " | " + @ field2 + " | " + @field3 + "\n" +@field4 + " | " + @ field5 + " | " + @field6 + "\n" +and so on`

If 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,

This works perfectly. Thanks.

• 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 3

I'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] instead of [i] in the for loop. Also: Take i++ in the loop instead of i=i+1.

Rainer

• Hi Rainer,

Thanks. Works perfectly.

• 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] + " | " +
(aantal[i] ? (aantal[i] + " " + eenheidaantal[i]) : "") + " | " +
(prijs[i] ? (prijs[i].toFixed(2) + " €" + eenheidprijs[i]) : "") + " | " +
(duur[i] ? (duur[i] + " " + eenheidduur[i]) : "") + " | " +
(totaal[i] ? (totaal[i].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")

• Hi Rainer,

Thanks again !

Regards

• 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 3

and 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 UniktID

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

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!

• Hi,

I was wondering is there  a way to play with the table layout?
It's seems we are restricted to only 3 columns...

Would it be possible to make a table of 4 columns or two rows with 3 columns and one row with one column?

Like this: • 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