I have a client that has an expression field and he would like to return the last date of an activity type from the appointment object if the activity type is "Partner - Breakfast". Currently the the field is returning the first date it sees (oldest record) and if the oldest record is not of that activity type it returns null.
Essentially, the client wants to return the most recent date of an appointment where activity type is "Partner - Breakfast". I am stuck and not sure how to create the query for this...any help is appreciated.
They are currently on an older version of Rollbase "2.2.2.0" but should be updating to 4.0 or higher in the next coming months.
Thanks :)
I'm not sure. I don't use expression fields because of some of the oddities they can present if you are using values from more than one object, but they should work just like a formula field in general. I think maybe the ID is what is wrong on the formula. If you are trying to find the value for a specific partner, I assume that partner is an object in your application. If that is the case, replace the id =? with the field name of partner relationship. Then, replace the {!id} token with the token for the related partner. That will change the query to return the date for the most recent appointment with a type of Partner - Breakfast and only for the related partner of the current record. I hope that helps.
Hi Paul,
You could use the selectQuery API to handle this. The key is to use the ORDER BY in DESC order for the date field that you are tracking. Below is some sample code that should point you in the right direction. Just update the field names and the object name to reflect what you are using(all values in red).
var activityArr = rbv_api.selectQuery("SELECT field FROM object WHERE typeField = 'Partner - Breakfast' ORDER BY datefield DESC", 1);
if (activityArr.length > 0)
{
var lastDate = new Date(activityArr[0][0]);
}
return lastDate;
If you have any questions, please let me know.
Jason
Hi Jason,
Thanks for the snippet of code but it is pulling in the same date for all Partner records...what am I missing. I need the view to display this expression field with the most recent date...
var activityArr = rbv_api.selectQuery("SELECT startDate FROM appointment WHERE R529621 = '10143598' ORDER BY startDate DESC", 1);
if (activityArr.length > 0)
{
var lastDate = new Date(activityArr[0][0]);
}
return lastDate;
R629621 = related Activity type and 10143598 = Partner - Breakfast
Ah, got it. You'll need to add another variable to your where clause. You need to make sure it selects the value for the current appointment. Try this and see if that does the trick.
var activityArr = rbv_api.selectQuery("SELECT startDate FROM appointment WHERE R529621 = '10143598' and id = ? ORDER BY startDate DESC", 1, {!id});
if (activityArr.length > 0)
{
var lastDate = new Date(activityArr[0][0]);
}
return lastDate;
Returning undefined...see debug log
Parsed Formula
001 var rbv_api = new Packages.com.rb.core.services.api.ServerSideAPI(9355387, 9356526);
002
003 function wrapper() {
004 rbv_api.setVerbose(true)
005
006 var activityArr = rbv_api.selectQuery("SELECT startDate FROM appointment WHERE R529621 = '10143598' AND id = ? ORDER BY startDate DESC", 1, 29033275);
007
008 if (activityArr.length > 0)
009 {
010 var lastDate = new Date(activityArr[0][0]);
011 }
012 rbv_api.println(lastDate)
013 return lastDate;
014 }
015 wrapper();
Formula return type: Date/Time (Adjust to user time zone)
Debug
==>selectQuery: SELECT A.EVENT_DATE FROM RB_OBJ_DATA A WHERE A.OBJ_DEF_ID=9357701 AND (A.INTG11 = '10143598' AND A.OBJ_ID = ?) ORDER BY A.EVENT_DATE DESC
arg[1]: 2.9033275E7
undefined
Result
NULL
Do you think I need perhaps an actual date field and a trigger that will set that date field using the above script?
Thanks again...appreciate the help.
I'm not sure. I don't use expression fields because of some of the oddities they can present if you are using values from more than one object, but they should work just like a formula field in general. I think maybe the ID is what is wrong on the formula. If you are trying to find the value for a specific partner, I assume that partner is an object in your application. If that is the case, replace the id =? with the field name of partner relationship. Then, replace the {!id} token with the token for the related partner. That will change the query to return the date for the most recent appointment with a type of Partner - Breakfast and only for the related partner of the current record. I hope that helps.
Got it..thanks! :)
You're welcome. I'm glad you got it working.
Jason