I have a reservations table that has four fields I’m concerned with; recordID, roomID, startDate and endDate. I need to create a record in a new table, related to the current one by the recordID, for every date in each master record, starting with the startDate and stopping the day before the endDate. Thus if I have a reservation as follows:
recordID roomID startDate endDate
1000 17 5 March 2004 9 March 2004
It would create the following records in the bookings table:
recordID roomID Date
1000 17 5 March 2004
1000 17 6 March 2004
1000 17 7 March 2004
1000 17 8 March 2004
There was an article in the Forum that talked about making a loop thru the foundset and make a new record in the related file which I read but it didnM-^Rt cover this scenario.
The purpose of this is so that I can get an accurate count, on a day to day basis of rooms sold by the roomID and the Date.
TIA
something like this:
var day1 = utils.dateFormat(beginDate,'D');
var day2 = utils.dateFormat(endDate,'D');
var between = day2-day1;
for(var i=0;i<between;i++)
{
reservations_to_bookings.newRecord();
reservations_to_bookings.roomID = roomID;
var bookDate = beginDate;
bookDate.setDate(bookDate.getDate() + i);
reservations_to_bookings.date = bookDate;
}
Johann, this works brilliantly:
var day1 = utils.dateFormat(indate, ‘D’);
var day2 = utils.dateFormat(outdate, ‘D’);
var between = day2-day1;
for(var i = 0; i<between;i++)
{
tblreservationroom_to_nights.newRecord();
tblreservationroom_to_nights.reservationid=reservationid;
tblreservationroom_to_nights.roomassignid=roomassignid;
tblreservationroom_to_nights.lastname=lastname;
tblreservationroom_to_nights.firstname=name;
var bookdate = indate;
bookdate.setDate(bookdate.getDate() + i);
tblreservationroom_to_nights.indate=bookdate;
}
However it only does it for the current record. How would I extend it to loop through the foundset?
Also, what does ‘D’ refer to in the following line?
var day1 = utils.dateFormat(indate, ‘D’);
Many thanks.
what do you want to do?
through which foundset do you walk?
Or do you want to do it one time for youre reservationroom table?
then just put a for loop over the codethat loops over all the records in the foundset:
for(var k=1;k<=controller.getMaxRecordCount();k++)
{
// here the code
}
then it will go over all youre records and create the nights things.
But i take it must do this only onces?
the D is a date formatting thing of the javacode under the uitls.dateFormat.. look here:
http://java.sun.com/j2se/1.4.2/docs/api … ormat.html
Hi Johann
I need to run the loop just once for all the records in the reservations table and then it will run each time a new reservation is added just for that reservation.
Following your instructions, I came up with:
for(var k = 1;k<=controller.getMaxRecordIndex();k++)
{
controller.recordindex=k
var day1 = utils.dateFormat(indate, ‘D’);
var day2 = utils.dateFormat(outdate, ‘D’);
var between = day2-day1;
for(var i = 0; i<between;i++)
{
tblreservationroom_to_nights.newRecord();
tblreservationroom_to_nights.reservationid=reservationid;
tblreservationroom_to_nights.roomassignid=roomassignid;
tblreservationroom_to_nights.lastname=lastname;
tblreservationroom_to_nights.firstname=name;
var bookdate = indate;
bookdate.setDate(bookdate.getDate() + i);
tblreservationroom_to_nights.indate=bookdate;
}
}
however this isn’t working. I’ve tried several different variations also without success. Perhaps you could tell me where I have gone wrong here. Many thanks.[/quote]
i have no idee what can go wrong in that script.
It should work fine.
What goes wrong?
doesn’t it update any reservation?
what happens if you set some:
application.output(a_(reservation)_dataprovider)
on various places in that code?
All it does is do the first record over and over again. It seems to be doing it the right number of times though
are you sure you use that script exactly as above?
what if you print the k variable?
This is the method exactly:
for(var k = 1;k<=controller.getMaxRecordIndex();k++)
{
controller.recordindex=k
var day1 = utils.dateFormat(indate, ‘D’);
var day2 = utils.dateFormat(outdate, ‘D’);
var between = day2-day1;
for(var i = 0; i<between;i++)
{
tblreservationroom_to_nights.newRecord();
tblreservationroom_to_nights.reservationid=reservationid;
tblreservationroom_to_nights.roomassignid=roomassignid;
tblreservationroom_to_nights.lastname=lastname;
tblreservationroom_to_nights.firstname=name;
var bookdate = indate;
bookdate.setDate(bookdate.getDate() + i);
tblreservationroom_to_nights.indate=bookdate;
}
}
I have indented it to make it more readable but other than that, it is exactly the same.
I also tried the following modification to line 3:
controller.recordindex=k+1
with the same results
You asked “what if you print the k variable?”
Unfrtunately I don’t have a printer attached to this machine but is ther a way that I can capture it in a label or another field?
did you add some tracing to it as i asked before?
please insert some application.output() lines between them..
like
application.output("maxindex at start: " + controller.getMaxRecordIndex());
for(var k = 1;k<=controller.getMaxRecordIndex();k++)
{
controller.recordindex=k
application.output("currentindex: " + controller.recordindex);
var day1 = utils.dateFormat(indate, 'D');
var day2 = utils.dateFormat(outdate, 'D');
var between = day2-day1;
for(var i = 0; i<between;i++)
{
tblreservationroom_to_nights.newRecord();
tblreservationroom_to_nights.reservationid=reservationid;
tblreservationroom_to_nights.roomassignid=roomassignid;
tblreservationroom_to_nights.lastname=lastname;
tblreservationroom_to_nights.firstname=name;
var bookdate = indate;
bookdate.setDate(bookdate.getDate() + i);
tblreservationroom_to_nights.indate=bookdate;
}
}
Johann, you wrote:
did you add some tracing to it as i asked before?
please insert some application.output() lines between them..
like
I entered the following line into the method:
application.output("maxindex at start: " + controller.getMaxRecordIndex());
enabled the debugger and then pressed F8. I get maxindex at start: 200 appearing multiple times but don’t see any other information so I’m not sure how this helps.
I’m probably being very dense here but am totally unfamiliar with the debugging process.
did you do it exactly as i said?
because i am much more interessted in:
application.output("currentindex: " + controller.recordindex);
inside the loop
Added line to method: Method now reads:
application.output("maxindex at start: " + controller.getMaxRecordIndex());
for(var k = 1;k<=controller.getMaxRecordIndex();k++)
{
application.output("currentindex: " + controller.recordindex);
controller.recordindex=k
var day1 = utils.dateFormat(indate, ‘D’);
var day2 = utils.dateFormat(outdate, ‘D’);
var between = day2-day1;
for(var i = 0; i<between;i++)
{
tblreservationroom_to_nights.newRecord();
tblreservationroom_to_nights.reservationid=reservationid;
tblreservationroom_to_nights.roomassignid=roomassignid;
tblreservationroom_to_nights.lastname=lastname;
tblreservationroom_to_nights.firstname=name;
var bookdate = indate;
bookdate.setDate(bookdate.getDate() + i);
tblreservationroom_to_nights.indate=bookdate;
}
}
globals.recordCount=controller.getMaxRecordIndex()
The debugger window shows:
currentindex: 1
currentindex: 2
etc
going all the way up to 199. It seems that it is not setting the variables in this section:
var day1 = utils.dateFormat(indate, ‘D’);
var day2 = utils.dateFormat(outdate, ‘D’);
var between = day2-day1;
for(var i = 0; i<between;i++)
{
tblreservationroom_to_nights.newRecord();
tblreservationroom_to_nights.reservationid=reservationid;
tblreservationroom_to_nights.roomassignid=roomassignid;
tblreservationroom_to_nights.lastname=lastname;
tblreservationroom_to_nights.firstname=name;
var bookdate = indate;
bookdate.setDate(bookdate.getDate() + i);
tblreservationroom_to_nights.indate=bookdate;
}
for each individual record; in other words, it does it for the first record, then goes to the next but doesn’t reset the variables.
then i am out of idee’s because it seems to loop through youre records just fine..
Do you have an example? where i can test this.
Johann
Thanks for spotting the error and for your help.
The method works fine now that the syntax has been corrected.
However, when I run the routine, it runs for a while and then gives me the following error:
WrappedException of java.lang.OutOfMemoryError.
Any suggestions? I have 512Mb of RAM and this happens when Servoy is the only application running so I think it may be a bug. TIA.
the problem is that servoy only uses 64MB for itself (in windows this can be 128MB as seen in taskmanager)
The problem is that you are loading in every record in a very tied loop and generated many records in that loop also.. So this is only a real problem in youre case where you want to generated an enorumous bulk of data.
you can fix it by giving servoy a bit more memory just start it with:
javaw.exe -Xmx256M -jar servoy_developer.jar
(see the bat/startup script)