Post

7 followers Follow
0
Avatar

Using calculation field to sort related items

Good afternoon. I am trying to give our sales team an easy way to look at a master request and see all the related steps, sorted in order, and the status of each step. I currently have a requests app that holds that master request and a steps app that holds all the related steps. The steps are given a title of step 1, step 2 by a workflow. The step number changes depending on other factors.

If I just look at the "related items" section that Podio automatically creates, these are out-of-order based on the title I have given them. My question is:

1) Is there a way to sort related items 

and

2) If there's not, is there a way I can use a calculation field to pull all the related steps, sort them by this field, and then pull in their status. If someone can give me an example of how this would look, that would be awesome.

My fields are:

@Sort Order

@Progress

 

 

Amber Sullard

Please sign in to leave a comment.

11 comments

0
Avatar

Hi Amber,

to 1): No,not possible

to 2). yes, possible. You can sort them in a calculation field by name (more about the function .sort())

var steps = @all of steps title field;
var progress = @all of progress;
order = [];
for(var i = 0; i  < steps.length; i++){
order.push(steps[i] + " = " + progress[i]);
};
order.sort().join("\n")

This woud give you a list like

step 1 = closed
step 2 = started
...
step 9 = open

But be careful if you have more then 9 steps. Cause .sort() sorts in an app you would get an order like

step 1
step 10
step 11
step 2

Two possible solutions: You rename your steps with a leading zero like step 01 or you need a more elaborated code.

Rainer

 

Rainer Grabowski 0 votes
Comment actions Permalink
0
Avatar

Hi Rainer,

Thanks for your help alot.

Let me know that how can i use .sort() for dates? Oldest to newest.

 

mvm Top Management 0 votes
Comment actions Permalink
0
Avatar

Are the dates in the same app with the calculation field or do you pull them from related items?

If they are in the same app enter:

var dates = [@date1, @date,@date3 ]

if you pull them from another item: 

var dates = @all of dates;

Then for both cases the same calc.

var datesNumber = [];
for(var i = 0; i < dates.length;i++){
datesNumber.push(moment(dates[i]).format("YYYYMMDD"));
};
var datesSort = datesNumber.sort()
var datesClean = [];
for(var i = 0; i < datesSort.length; i++){
datesClean.push(moment(datesSort[i], "YYYYMMDD").format("MM/DD/YYYY"));
};
datesClean.join("\n")

The result is a list with oldest date at the top like
09/18/2016
09/17/2016
....

Rainer

Rainer Grabowski 0 votes
Comment actions Permalink
0
Avatar

@Rainer ,

When I try to implement this code I get a "Script syntax error: Unexpected token ;"


Any idea why?

Joe Montore 0 votes
Comment actions Permalink
0
Avatar

Hi Joe,

sorry, my bad - my favorite typo: I've forgotten the closing brackets in both .push() lines. Just add in both lines a ) at the end (before the ;). I've fixed that in my above post.

Rainer

Rainer Grabowski 0 votes
Comment actions Permalink
0
Avatar

@Rainer,

What if I wanted to present these dates with the "amount" next to them.

So in my scenario I was sorting the dates on donations that were given by the particular contact record we are writing the calculation on. We have a relationship to Donations where there is a Date on Gift and Amount Given.

I want to display:

12/26/2016 - $500.00
01/25/2017 - $250.00

Do you know what I'd need to add to your code? Right now it's just displaying dates in order (which is fantastic and I appreciate it).

Joe Montore 0 votes
Comment actions Permalink
1
Avatar

Hi Joe,

var dates = @all of dates;
var other = @all of other field;
var datesNumber = [];
for(var i = 0; i < dates.length;i++){
datesNumber.push(moment(dates[i]).format("YYYYMMDD") + "|" + @other[i]);
};
var datesSort = datesNumber.sort()
var datesClean = [];
for(var i = 0; i < datesSort.length; i++){
splitDate = datesSort[i].split("|").shift();
splitOther = datesSort[i].split("|").pop();
datesClean.push(moment(splitDate, "YYYYMMDD").format("MM/DD/YYYY") +  " - " + splitOther);
};
datesClean.join("\n")

Rainer

Rainer Grabowski 1 vote
Comment actions Permalink
0
Avatar

Little update, there was a $ that gave an ILLEGAL error in the last post. 

var dates = @all of dates;
var other = @all of other field;
var datesNumber = [];
for(var i = 0; i < dates.length;i++){
datesNumber.push(moment(dates[i]).format("YYYYMMDD") + "|" + other[i]);
};
var datesSort = datesNumber.sort()
var datesClean = [];
for(var i = 0; i < datesSort.length; i++){
splitDate = datesSort[i].split("|").shift();
splitOther = datesSort[i].split("|").pop();
datesClean.push(moment(splitDate, "YYYYMMDD").format("MM/DD/YYYY") +  " - " + splitOther);
};
datesClean.join("\n")
Felix Lepoutre 0 votes
Comment actions Permalink
0
Avatar

Rainer - I am trying to use the calculation below to sort by date when referenced number is 0, and then by number. It is sorting by date perfectly, how do I get it to switch to sort by number?

Dan Stroehlein 0 votes
Comment actions Permalink
0
Avatar

Rainer - I am trying to use the calculation below to sort by date when the referenced number is 0, and then by number. It is sorting by date perfectly, how do I get it to switch to sort by number?

Dan Stroehlein 0 votes
Comment actions Permalink