Post

3 followers Follow
1
Avatar

Table inside another table using a calculation field

Hello,

I've got two apps, the first one is the "Main" app and the other is the "Invoice" app. In the Invoice app, I have a relationship field (linked to Main app), 14 different date fields and another 14 number fields. And in my Main app, I've got a calc field that I want to show everything.

For example, I've got two Invoice items that are linked to one same Main item. So, on my Main, this is how I want my calc field to show:

Whereas the Invoice 1 and Invoice 2 at the top are hyperlinks to its respecting Invoice item and so on and so forth.

Hope I'm making any sense at all.

Thanks!

Au

Please sign in to leave a comment.

5 comments

0
Avatar

Hi Au,

2 questions before I can show you the solution: 
1. Are in all invoices all 14 date fields and all 14 Number fields ALWAYS filled? Or can it happen that one of the 28 fields is empty?
2. In which format do you want to see the date in the table?

Rainer

Rainer Grabowski 0 votes
0
Avatar

Hi Rainer,

1. Nope, sometimes the date fields will not have a value. But once Date1 doesn't have a value, Number1 will also be null.

2. MM/DD/YYYY would be cool.

Many thanks.

Au 0 votes
1
Avatar

In your Invoice App add a calculation field and name it e.g "Table lines". 
Then enter:

var
d1 = @date field 1,
d2 = @date field 2,
...
d12 = @date field 12,
n1 = @Number field 1,
n2 = @Number fields 2,
... 
n12 = @number field 12,
l1 = d1 != null && n1 != null ? moment(d1).format("MM/DD/YYYY") + " | " + n1 : "",
l2 = d2 != null && n2 != null ? moment(d2).format("MM/DD/YYYY") + " | " + n2 : "",
...
l12 = d12 != null && n12 != null ? moment(d12).format("MM/DD/YYYY") + " | " + n12 : "",
result = [l1,l2,l3 ... l12].filter(function(i){ return i != ""; }).join("\n");
result

You have to add vars for d1-d12,n1-n12,l1-l12
The l-variables exclude empty lines in the tables (if eg. date 12 and number 12 are empty then that line 12  won't be shown).

Save the template, open an item and copy the URL (without the number at the end) of the item. Add as second calculation field called "Invoice URL". 
Paste the URL into that field, wrap it in quoation marks and add:
+ @Unique ID
Save it. 


Then go to your Main app. 
Enter in the calculation field:

var i,
lines = @All of Table lines,
urls = @All of Invoice URL,
tableHead = "Date | Invoice Number \n --- | --- \n"
tables = [];
for(i = 0; i < lines.length; i++){
tables.push("## [Invoice " + (i+1) + "](" + urls[i] + ")\n\n" + tablehead + lines[i]);
};
tables.join("\n\n&nbsp;\n")

That's it. I hope, there are no typos in the code :) If any problems occur please share a screenshot of the calculations.

Rainer 

 

Rainer Grabowski 1 vote
0
Avatar

Hi, I have a project tracking app that has a several activities which are in another app linked by the project relationship. The activities app have 4 fields: activity type, activity item, status and progress. What I would like is to show a calculation field on the project app that shows the activities grouped by activity type. Not all the projects have all the activity types, so one project might show 3 sections while another might show 5. Below is an example. I hope I was clear. 

Is this possible?

 

Thanks



Gaspar Rivera 0 votes