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
-
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
-
Ben Kern wow fantastic use case, I am very interested to see rainer's solution on this. Will try it out as well.
-
Hi Ben,
that's a simple one :) . In the Child app:var mdt = @Meeting Date / Time;
var date = mdt != null ? moment(mdt).format("MM/DD/YY" ) : "*Not set*";
var time = mdt != null ? moment(mdt).format("h:mm a") : " ";
var mdtsort = mdt != null ? moment(mdt).format("YYMMDDHHmm") : "9999999999";
"{" + mdtsort + "} | | "+title+" | "+status+" | "+date+" | "+time+" | "+countThen in the Parent app:
@all of child calc field.sort().join("\n").replace(/{.*?}/g,"")Result: Sorted ascending (first for date, then if dates are equal for time) = meeting with oldest date/time at the top, meeting w/o a date at the bottom. The mdtsort value is removed after the sorting is done.
If you want to sort descending: sort().reverse().join("\n").replace() - meetings w/o a date will be at the top.
If you want the descending order but meetings w/o a date at the bottom replace "9999999999" with "1111111111".
Instead of moment(mdt).format("YYMMDDHHmm") you can also use moment(mdt).unix()The alternate would be a calculation in the Parent app like the following one (then you can keep your child calculation as it is without adding mdtsort):
Sort first for value Date then for value Time:
var x,y,v,w;
var arr = @all of child calc field;
var sortedArr = arr.sort(function(a,b){
x = String(a.split(" | ")[4]);
x = x != "Not set" ? moment(x,"MM/DD/YY").unix() : "9999999999";
x = Number(x);
y = String(b.split(" | ")[4]);
y = x != "Not set" ? moment(y,"MM/DD/YY").unix() : "9999999999";
y = Number(y);
v = String(a.split(" | ")[5]);
v = v != " " ? moment(v,"h:mm a").format("HHmm") : "9999";
v = Number(v);
w = String(b.split(" | ")[5]);
w = w != " " ? moment(w,"h:mm a").format("HHmm") : "9999";
w = Number(w);
return (x < y) ? 1 : (x > y) ? -1 : (v < w) ? -1 : (v > w) ? 1 : 0;
}).join("\n");Rainer
-
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 = openBut 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
-
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,
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). -
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") -
Hi Rainer,
for this below post
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")is there a way to sort by the steps as mentioned in the calculation but also display progress and another field value.
Question 1: So we can always get the sorting right with the above calc but also get to show additional value along with progress ?
Question 2: Also can get the results to have goto link to the item as well, so its can be clicked and re-directed
-
Hi Jayraj,
not sure if I understand your question 1.
if you have:var steps = ["B","C","A"];
var progress = ["100","50","0"];then the result of the loop would be:
order = ["B = 100","C = 50","A = 0"].
order.sort().join("\n") would return:
A = 0
B = 50
C = 100I don't understand what you mean by " to show additional value along with progress".
Question 2: Yes possible.
In the related app add a calculation field called "UID" and enter @Unique ID
(it should return the real id only; if you add a prefiix to the unique id in the app settings, the prefix must be removed in that calculation).Then in the calculation where you collect and sort those related items add the variables:
var id = @all of ID;
var url = "https//podio.com/workspace name/app name/items/";(replace workspace name and app name with the correct values of the related app)
In the loop:
orders.push("[ " + steps[i] + "](" + url + id[i] + ")" + " = " progress[i])That links the step name.
Rainer
-
Hi Rainer
var sequence_a = @All of Sequence;
var Title_a = @All of OKR Title;
var Progress_a = @All of Progress;
var uid_a = @All of UID;
var url_a = "https://podio.com/powerstorescom/okrs-and-projects/apps/objectives/items/";order = [];
for(var i = 0; i < sequence_a.length; i++){
order.push(sequence_a[i] + " = [" + Title_a[i] + "](" + url_a + uid_a[i] + ") = " + Progress_a[i] + "%");
};
order.sort().join("\n")Question: can you help me turn Progress value into Percentage? Currently for 68% I am getting a result as 0.68
-
Rainer Grabowski Im trying to set this up where instead of a single field being pulled in from the related item I have a calc field that has a table row that I want to pull in. So that in the parent item its sorting each related items row by date.
This is the row being composed in each child item. (In a calc field in each "Meeting Record")
var total = @Sum of Meetings Count;
var number = @[MeetingNumber];
var url = "https://podio.com/creativedancekidscom/cdk-1/apps/meetings/items/"+@Unique ID;
var title = "["+number+"]("+url+")";
var type = @All of [sessionClassType].join();
var age = @All of [sessionAgeGroup].join();
var status = @Meeting Status;
var count = @Students Present Count>=1?@Students Present Count:"n/a";var date= @Meeting Date / Time!=null?moment(@Meeting Date / Time).format("MM/DD/YY"):"*Not set*";
var time = @Meeting Date / Time!=null?moment(@Meeting Date / Time).format("h:mm a"):" ";" | | "+title+" | "+status+" | "+date+" | "+time+" | "+count
Then in my parent record I basically want to .join("\n") these rows into a single table but Im having trouble getting them to sort by date. I tried adding a number field and then having Gflow number them by date and then sort the calc by that date field but it only sorts the first digit >_<
Please sign in to leave a comment.
Comments
19 comments