Filter Related Records

Okay, this is where I'm currently at:



I made a formula field called "Current Appointments" in my Doctor object.



The formula is:



=======



var apptDateArray = rbv_api.getRelatedFields("R72009332", {!id}, "appt_date");



/* convert current date to milliseconds */

var today = rbv_api. getCurrentDate();

var todayJava = new Date(today);

var todayMilli = todayJava.getTime();



/* loop through appointments and compare to current date */

for (var k=0; k



/* convert appt date to milliseconds */

var apptDateJava = new Date(apptDateArray[k]);

var apptDateMilli = apptDateJava.getTime();



/* compare dates */

if (apptDateMilli >= todayMilli) {

rbv_api.printArr("Current Appt = " + apptDateMilli + "\n");

}

else {

rbv_api.printArr("Past Appt = " + apptDateMilli + "\n");

}

}



=======



When debugging I get:



Past Appt = 1373000400000

Current Appt = 1373605200000



This is correct, It's the two appointment dates I m

Hi Jon,



Can you please let me know your rollbase login and company name, I would like to take a look at your sample application.



Thank You,

Srikanth

Progress|Rollbase Product Support Team

Hi Jon,



Maybe try the following formula:



======



var upcomingAppointments = rbv_api.selectQuery("SELECT name FROM appointment where appt_date >= '{!#TODAY}' and doctor = {!id}", 10);

// count number of results returned

var count = upcomingAppointments.length;



var resultString = "";



// loop through all the results

for (var i = 0; i

var apptName = upcomingAppointments[i][0]; // reference field 0 = name

if (resultString != "") resultString += "; ";

resultString += apptName;

}



return resultString;



======



This will return a string like the following: "Bobbi - 07/12/2013 04:49 PM; Joani - 07/17/2013 04:49 PM", filtered only for appointments that are today and later.



I would suggest changing the integration name for your relationships from R#### to something meaningful like "doctor" or "appointment", as I have done above.



Also, you could create a view instead of "Upcoming Appointments" which allows you to use the UI for view filters, and would display as mu

Is there a way to send this to you privately?

Sorry I thought you were asking for something else. Here's that info. Just to be clear the doctor example was just an easy to understand variation of what I'm actually trying to do.



jon@foxbot.tv

Foxbot Productions

I will give that a shot. Thanks!



I do have the views setup but I also need to be able to see quickly all of the doctors and their appointments. in one big spreadsheet.

Not working as is.



Here's the debug info:



Original Formula

======

var upcomingAppointments = rbv_api.selectQuery("SELECT name FROM appointment3 where Appointment Date >= '{!#TODAY}' and doctor = {!id}", 10);

// count number of results returned

var count = upcomingAppointments.length;



var resultString = "";



// loop through all the results

for (var i = 0; i

var apptName = upcomingAppointments[i][0]; // reference field 0 = name

if (resultString != "") resultString += "; ";

resultString += apptName;

}



return resultString;



Parsed Formula

======

001 var rbv_api = new Packages.com.rb.core.services.api.ServerSideAPI(71896001, 71897643);

002

003 function wrapper() {

004 var upcomingAppointments = rbv_api.selectQuery("SELECT name FROM appointment3 where Appointment Date >= '07/11/2013' and doctor = 73181142", 10);

005 // count number of results returned

006 var count = upcomingAppointments.length;

007

008 var resultString = "";

009

010 // loop throu

Hi Jon,



I don't know the name of your columns, you'll need to put in the correct names. The column "doctor" is the relationship between appointment and doctor, which is probably "R72009332" in your case.

Thanks Kurt! That worked beautifully.



Just wondering, what does the "10" denote in this query?



rbv_api.selectQuery("SELECT name FROM appointment3 where appt_date >= '{!#TODAY}' and doctor = {!id}", 10);



Also... Is there any way to create links that lead to the record like a typical lookup field?

Hey Jon,



The 10 is max number of records returned. That means will only show the next 10 appointments. You can set that to any number between 1 and 999 I believe.



You may want to refer to chapter 6 in the RollBase official documentation for information about the various API methods available, and how their parameters work. I believe there is also information there about how to create record links.



~ Kurt

Thanks again Kurt. I'll need to sit down and take another look. There's a lot to soak in :)

Hey guys, Me again.



Any ideas why the formula stopped working for me.



=====

var upcomingAppointments = rbv_api.selectQuery("SELECT name FROM appointment3 WHERE appt_date >= '{!#TODAY}' and R73229279 = {!id}", 10);



// count number of results returned

var count = upcomingAppointments.length;



var resultString = "";



// loop through all the results

for (var i = 0; i

var apptName = upcomingAppointments[i][0]; // reference field 0 = name

if (resultString != "") resultString += "; ";

resultString += apptName;

}



return resultString;



======



When trying to debug it I notice that:



'{!#TODAY}' returns this date format:

"07/12/2013"



appt_date returns something like this:

"Fri Jul 19 00:00:00 CDT 2013"



Could this have something to do with it?

Hi Jon,



were you able to find a solution to filter the related records? Please let me know if you'd need any assistance.



Thanks,

Corey Prak

I was, I had to merge what kurt showed me and my original script but I finally got it working... with url links taboot. Thanks.

Jon,



you're very welcome, and thanks for your quick reply. I was curious as to the specific portion of code that suddenly stopped working, but I'm glad that the formula works now.



Thanks Again,

Corey Prak