Insert Record to table

snehalkhare · ‎08-21-2017

Hi ,

I have to insert the records of timecard table to a custom table . I'm calculating the sum of hours for a particular day of the week for multiple timecards . if the sum is less than a particular value then insert that record in the custom table .Issue is the first entry is getting inserted again n again .

what may be issue? Also here I have calculated only for sunday , but need to check it for each day on daily basis

doing through background Script;

var count = new GlideAggregate('time_card');

count.addAggregate('COUNT', 'user');

count.query();

if(count.hasNext()){

while (count.next()) {

var categoryCount = count.getAggregate('COUNT', 'user');

gs.print(count.user + ' - ' + categoryCount);

var gr = new GlideAggregate('time_card');

gr.addQuery('user', count.user);

gr.addAggregate('SUM','sunday');

gr.setGroup(false);

gr.query();

while(gr.next()){

var s = parseInt(gr.getAggregate('SUM', 'sunday'));

gs.print('Sum' + s);

}

var gr = new GlideRecord('time_card');

gr.query();

if(gr.next()){

{

var oneday =(gr.user.u_contract_hrs/5)*0.9; /// 90% of contract hrs for 1 day(eg 8 hrs for 1 day)

gs.print(oneday);

var percent = (oneday - s)*0.9; //

gs.print(percent);

if(s<oneday)

{

var table1 = new GlideRecord('u_timecard');

table1.initialize();

table1.u_week_starts_on = gr.week_starts_on;

table1.u_state = gr.state;

table1.u_user = gr.user;

table1.u_sunday = gr.sunday;

table1.u_total = gr.total;

table1.insert();

}

Thanks,

Snehal Khare

treycarroll · ‎08-21-2017

Hi Snehal,

I took a stab at refactoring your code. Please see if I captured your intent:

var count = new GlideAggregate('time_card');

count.addAggregate('COUNT', 'user');

count.query();


while (count.next()) {


       var numTimeCardsForCurrentUser = count.getAggregate('COUNT', 'user');

       var currentUser = count.user + '';


       gs.print(currentUser + ' - ' + numTimeCardsForCurrentUser);


       // Find the Sum of the current user's Sunday Hours

       var grSunHrs = new GlideAggregate('time_card');

       grSunHrs.addQuery('user', currentUser);

       grSunHrs.addAggregate('SUM', 'sunday');

       grSunHrs.setGroup(false);

       grSunHrs.query();


       while (grSunHrs.next()) {

               var sumOfSundayHoursForCurrentUser = parseInt(grSunHrs.getAggregate('SUM', 'sunday'));

               gs.print('Sum:' + sumOfSundayHoursForCurrentUser);

       }


       var grAllCards = new GlideRecord('time_card');

       grAllCards.query();

       while (grAllCards.next()) {

               {

                       var oneday = (grAllCards.user.u_contract_hrs / 5) * 0.9; // 90% of contract hrs for 1 day(eg 8 hrs for 1 day)

                       gs.print(oneday);

                       var percent = (oneday - sumOfSundayHoursForCurrentUser) * 0.9; //

                       gs.print(percent);


                       if (sumOfSundayHoursForCurrentUser < oneday) {

                               var table1 = new GlideRecord('u_timecard');

                               table1.initialize();

                               table1.u_week_starts_on = grAllCards.week_starts_on;

                               table1.u_state = grAllCards.state;

                               table1.u_user = grAllCards.user;

                               table1.u_sunday = grAllCards.sunday;

                               table1.u_total = grAllCards.total;

                               table1.insert();

                       }

               }

       }

}

I replaced an if with a while at line 26. If your issue was that it was only running for one card, that was the trouble.

TC

snehalkhare · ‎08-21-2017

Hi Trey ,

Its not working, its inserting the same timecard entry for the user ,
I want to insert all the available timecards of that user.

Thanks

Snehal

treycarroll · ‎08-22-2017

Hi Snehal,

I have experienced a similar issue when pushing GlideRecords into an array.

Try this:

table1.initialize();

table1.u_week_starts_on = grAllCards.week_starts_on + "";

table1.u_state = grAllCards.state + "";

table1.u_user = grAllCards.user + "";

table1.u_sunday = grAllCards.sunday + "";

table1.u_total = grAllCards.total + "";

table1.insert();

See if that trick fixes your issue. If it does, I'll tell you why the trick works.

Regards,

TC

snehalkhare · ‎08-29-2017

Hi Trey,

Can you help in the following requirement script:

I'm having multiple time cards for different users . I heed to check for the day , if suppose it is monday then I have to sum the entries of all the time cards for monday and save this value in a field , like this for each day I have to check and insert the records accordingly in a custom table. If its sunday then for sunday it should insert , if monday then timecards for monday and so on ..

Can you please help with the script(BR) for this and also the layout of the custom table?

Thanks,

Snehal Khare