🛠️ Generate Timesheet Rows in Joget Advance Grid with JavaScript

Overview

For developers building complex Joget applications, the Advance Grid (based on PQGrid) offers immense flexibility. While standard data entry is straightforward, there are scenarios—like generating monthly timesheets or project schedules—where you need to programmatically inject dozens of rows and perform real-time calculations. This guide dives deep into using JavaScript, Moment.js, and the PQGrid API to automate grid management.

How It Works

The technical implementation centers on a few core developer concepts:

1. Date Iteration: Using moment.js to loop through a range.
2. PQGrid DataModel Manipulation: Accessing the grid's internal dataModel to read and write cell values.
3. Asynchronous Row Handling: Because UI triggers (like adding a row) take time to reflect in the DOM, we use Promises and setTimeout to ensure data is only written once the row exists.
4. PQGrid Refresh: Manually updating the .pq-td-div and the underlying hidden textarea that Joget uses to persist grid data to the database.

Where to Use in Joget

• Form Builder: Add a Custom HTML element to your form.
• Dependencies: Ensure your Joget environment has access to jQuery (standard) and include the Moment.js library via CDN as shown in the code.

Full Code

Example Use Cases

• 📅 Timesheet Automation: Generate a row for every day of the month based on a selection.
• 📊 Financial Projections: Populate a grid with 12 months of empty rows for budgeting.
• ✅ Inventory Audits: Generate rows for all items in a specific category to be checked.

Customization Tips

• ⚙️ Column Keys: Ensure the column keys in the PQGrid dataModel match the order of your setValue calls.
• ⚙️ Masking/Placeholders: If using database-linked data, replace statusgrid with your specific grid ID.
• ⚙️ Timeout Duration: The 100ms delay in setValue is a safe buffer; for large grids (50+ rows), you may need to optimize the injection logic.

Key Benefits

• ✅ Developer Control: Bypass manual entry constraints and manipulate data directly in the grid's memory.
• ✅ Rich UX: Instant feedback for users with automatic summaries.
• ✅ Standardization: Enforces date formats and calculation logic client-side.

🔒 Security Note

Since this is a client-side script, developers should treat this as a UX Enhancement only. Always implement Server-Side Validation (BeanShell) to verify that the submitted totals match the grid data, as client-side values can be modified by end-users via the console.

Source Code

<input value='Apply' type='button' id="apply" class="form-button" style="float: right;" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.29.1/moment.min.js"></script>

<script>
  $(document).ready(function () {
    // Calculate();
    function setValue(grid, rowIndx, colIndx, colValue) {
      return new Promise(resolve => {
        setTimeout(() => {
          var DM = $(grid).find(".pq_grid").pqGrid("option", "dataModel");
          var data = DM.data;
          // Ensuring the json parsing is safe
          var json = data[rowIndx][data[rowIndx].length - 1];
          var obj;
          try {
            obj = JSON.parse(json);
          } catch (e) {
            console.error('Error parsing JSON:', e);
            resolve();
            return;
          }

          var colKey = DM.columnKey[colIndx];
          obj[colKey] = colValue;

          //update data
          json = JSON.stringify(obj);
          DM.data[rowIndx][colIndx + 1] = colValue;
          DM.data[rowIndx][data[rowIndx].length - 1] = json;

          //update hidden table
          var indexKey = DM.data[rowIndx][0];
          var tbl = $(grid).find('.table_container table');
          tbl.find("tr.key_" + indexKey).find("[column_key=" + colKey + "]").text(colValue);
          tbl.find("tr.key_" + indexKey).find("textarea").val(json);

          //update table cell
          var colCell = $(grid).find("tr[pq-row-indx=" + rowIndx + "] .pq-grid-cell[pq-col-indx=" + (colIndx + 1) + "]");
          $(colCell).find(".pq-td-div").html('<div class="subform-cell-value"><span>' + colValue + '</span></div>');


          console.log({
            DM: DM,
            DM_data: DM.data,
            json: json,
            obj: obj,
            colKey: colKey,
            colIndx: colIndx,
            obj_col: obj[colKey],
            json: json,
            indexKey: indexKey,
            tbl: tbl,
            colCell: colCell
          });

          resolve();
          //   Calculate();
        }, 100);
      });
    }

    $("#apply").click(async function () {

      // $(FormUtil.getField("statusgrid")).find('.table_container table tbody tr').remove();
      // clear the table Cache
      // Get the pqGrid widget directly
      var gridWidget = $(FormUtil.getField("statusgrid")).find(".pq_grid");

      // Clear the data in the pqGrid widget
      gridWidget.pqGrid("option", "dataModel.data", []);

      $(FormUtil.getField("statusgrid")).find('.table_container table tbody tr.grid-row').remove();


      var BillNo = $('select[name="if_bill_exist"]').val();

      if (BillNo !== null) {
        alert("This TimeSheet bill is already Created");
        return
      };

      var advgrid = FormUtil.getField("statusgrid");
      var fromDate = $('input[name="from_date"]');
      var toDate = $('input[name="to_date"]');

      var newFromDate = moment(fromDate.val(), 'DD-MM-YYYY');
      var newToDate = moment(toDate.val(), 'DD-MM-YYYY');

      var normalHours = $('select[name="normal_hours"]');
      var WeekendOverTimeH = $('[name="weekend_overtime_hour"]');
      var overTimeHour = $('#overtime_hours');

      var selectedCheckboxValues = [];
      $("input[name='select_overtime_hour_type']:checked").each(function () {
        selectedCheckboxValues.push($(this).val());
      });

      var rowIndex = 0;
      var promises = [];

      let sum_of_Normal_working_hours = 0;
      let sum_total_days = 0;
      let sum = 0;
      let weekendsum = 0;
      let publicsum = 0;

      for (var date = moment(newFromDate); date.isSameOrBefore(newToDate); date.add(1, 'days')) {
        var dayOfWeek = date.format('dddd');
        var isWeekHoliday = $('#week_holiday_chosen ul li:eq(0)').text().toLowerCase().split(';').includes(dayOfWeek.toLowerCase());
        var month = date.format('MMM');
        var day = date.date();
        var year = date.year();
        var fullDate = date.format('DD-MM-YYYY');
        var week_day = dayOfWeek;
        var values = FormUtil.getGridCellValues("statusgrid.project_name");
        var hoursValue = selectedCheckboxValues.includes('Normal Hour') ? normalHours.val() : '';
        var onesvalue = 1;
        var day_calc = isWeekHoliday ? '' : onesvalue;
        var Unique_ID_for_TimeSheet = date.format('YYYYMMDD');
        var Weekendvalues = (isWeekHoliday && selectedCheckboxValues.includes('Weekend OverTime')) ? WeekendOverTimeH.val() : '';
        var weekend_overtime_hour = Weekendvalues;
        var overTimeHourValue = selectedCheckboxValues.includes('OverTime Hour') ? overTimeHour.val() : '';
        var overtime_hour = isWeekHoliday ? '' : overTimeHourValue;
        var hour = isWeekHoliday ? '' : hoursValue;

        var btn_plus = advgrid.find(".ui-icon-circle-plus");
        btn_plus.trigger("click");

        // Adjusting row index if necessary

        console.log({ values: rowIndex });
        console.log({
          month: month,
          day: day,
          week_day: week_day,
          hour: hour,
          day_calc: day_calc,
          overtime_hour: overtime_hour,
          weekend_overtime_hour: weekend_overtime_hour,
          holiday: FormUtil.getValue("public_holiday_overtime_hour"),
          projectName: FormUtil.getValue("project_name"),
          ContractNumber: FormUtil.getValue("contract_number"),
          Name: FormUtil.getValue("name"),
          Unique_ID_for_TimeSheet: Unique_ID_for_TimeSheet
        });

        // promises.push(setValue(advgrid, rowIndex, 0, rowIndex));
        promises.push(setValue(advgrid, rowIndex, 0, month));
        promises.push(setValue(advgrid, rowIndex, 1, day));
        promises.push(setValue(advgrid, rowIndex, 2, week_day));
        promises.push(setValue(advgrid, rowIndex, 3, year));
        promises.push(setValue(advgrid, rowIndex, 4, fullDate));
        promises.push(setValue(advgrid, rowIndex, 5, hour));
        promises.push(setValue(advgrid, rowIndex, 6, day_calc));
        promises.push(setValue(advgrid, rowIndex, 7, overtime_hour));
        promises.push(setValue(advgrid, rowIndex, 8, weekend_overtime_hour));
        promises.push(setValue(advgrid, rowIndex, 9, FormUtil.getValue("public_holiday_overtime_hour")));
        promises.push(setValue(advgrid, rowIndex, 10, FormUtil.getValue("project_name")));
        promises.push(setValue(advgrid, rowIndex, 11, FormUtil.getValue("contract_number")));
        promises.push(setValue(advgrid, rowIndex, 12, FormUtil.getValue("name")));
        promises.push(setValue(advgrid, rowIndex, 14, Unique_ID_for_TimeSheet));

        sum_of_Normal_working_hours += parseFloat(hour) || 0;
        sum_total_days += parseFloat(day_calc) || 0;
        sum += parseFloat(overtime_hour) || 0;
        weekendsum += parseFloat(weekend_overtime_hour) || 0;
        publicsum += parseFloat(FormUtil.getValue("public_holiday_overtime_hour")) || 0;

        rowIndex++;
      }

      let object = {
        sum_of_Normal_working_hours: sum_of_Normal_working_hours,
        sum_total_days: sum_total_days,
        sum: sum,
        weekendsum: weekendsum,
        publicsum: publicsum
      }

      console.log(object);
      console.log(promises);
      await Promise.all(promises).then(Calculate(object));
    });

    function Calculate(Value_object) {
      $('[name=total_normal_working_hours]').val(Value_object.sum_of_Normal_working_hours);
      $('[name=total_days]').val(Value_object.sum_total_days);
      $('[name=total_overtime_hour]').val(Value_object.sum);
      $('[name=total_weekend_overtime_hour]').val(Value_object.weekendsum);
      $('[name=total_public_holiday_overtime]').val(Value_object.publicsum);
    }




    // Assuming you want to get all span values from the 7th column (index 6)
    var previousSpanValues = []; // To store the previous values

    function checkAndLogChange() {
      var DM = $(".pq_grid").pqGrid("option", "dataModel");
      var data = DM.data;
      // Ensuring the json parsing is safe
      var json = data.map(function (row, index) {
        return JSON.parse(data[index][data[index].length - 1]);
      })

      var obj;
      try {
        //obj = JSON.parse(json);
        console.log({ Json: json });
      } catch (e) {
        console.error('Error parsing JSON:', e);
        resolve();
        return;
      }



      // var spanValues = $('.pq_grid').pqGrid("option", "dataModel").data.map(function(row) {
      //     return 1;
      // });

      // console.log({
      //     spanValues: $('.pq_grid').pqGrid("option", "dataModel").data
      // })

      spanValues = json.map(function (row, indox) {
        return row['hour']
      });

      var normalHours = parseInt($('select[name="normal_hours"]').val());
      for (var i = 0; i < spanValues.length; i++) {
        if (spanValues[i] !== previousSpanValues[i]) {

          // var day_calc = isWeekHoliday ? '' : onesvalue;
          // spanValues[i]=isWeekHoliday ? '' : 1;
          console.log('Span value changed at index ' + i + ':', previousSpanValues[i], '->', spanValues[i]);
          // var spanValue = spanValues[i];
          var days_calc = parseInt(spanValues[i]) / normalHours;


          if (days_calc) {
            setValue(FormUtil.getField("statusgrid"), i, 6, days_calc);
          }

          console.log('daycalc is', days_calc);
        }
      }

      // Update the previous values for the next check
      previousSpanValues = spanValues.slice();

      // Schedule the next check after a certain time (e.g., 1000 milliseconds)
      //setTimeout(checkAndLogChange, 1000);
    }


    function handleStatusGridChange() {
      setTimeout(function () {
        checkAndLogChange();
      }, 1000); // 1000 milliseconds = 1 second
    }

    $('[name=statusgrid], .save').on('click change', handleStatusGridChange);


    // Initial check
    //checkAndLogChange();


  });
</script>