How Do I Properly Increment a Field?

[chriscorbett]

Hello All -

I have a table that has three fields: u_date, u_total and u_total_amount

u_total_amount is a calculated field based on u_total.

I am trying to write a Client Script that checks the table for an existing date. If the date is not in the table, a new record is inserted. I have this part working in the code below. Where I am running into difficulty is with a record for an existing date. If the date exists, I want to increment u_total by one.

Here is what I have so far:

function onSubmit() {

      var dateNow = g_form.getValue('u_entry_date');

      var dateCheck = new GlideRecord('u_daily_totals');

      dateCheck.addQuery('u_date',dateNow);

      dateCheck.query();

              if (dataCheck.get(current.u_total)){

              ******************

              This is where I am having trouble

            *******************

      } else {

              var insertRec = new GlideRecord('u_daily_totals');

              insertRec.initialize();

              insertRec.u_date = dateNow;

              insertRec.insert();

      }

}

The starred section is where I'm having problems. I cannot figure out how to read in the u_total value of the current record, increment it by one and save it back to the u_total field.

Any help the community could provide would be appreciated!

[Chuck Tomasi]

It should work Chris. onSubmit() is what happens BEFORE it's submitted to the server (and hence database.)

You could also use a BEFORE update business rule to do something similar. Let us know if you have issues.

[chriscorbett]

Sorry. I should clarify.

I'm actually working with two tables - u_daily_stats and u_daily totals.

Daily stats is the form I'm actually dealing with. I enter a date and a category and onSubmit of THAT form I have the script above where I am trying to update the daily_totals field.

In that case, I don't think g_form will work as I am actually working with a different form.

Thoughts?

[Chuck Tomasi]

You are correct. If it's happening at submit time, then I'd go with an AFTER business rule. Take the info from the current record and do an update on the second table (u_daily). As long as you have a related field between those two tables, it should be pretty straightforward to script.

[chriscorbett]

I'm still confused about how to pull the count field in the event that a record is there. Would you have an example of how this is done?

[Chuck Tomasi]

Hi Chris,

Based on your tables mentioned earlier, I'll make up an example.

Business Rule

Name: Add daily stats to totals

When: After

Insert: Checked

Update: Checked

Advanced: Checked

Condition: (empty)

Script:

// Add the daily stats to the daily totals

// Assumptions: Table u_daily_totals has two fields u_day (yyyy-mm-dd) and u_total (int or float)

//                                             Current is a record of daily stats. We're only dealing with 1 field u_total

//                                           If there's already a daily record, add the latest total to it, otherwise create a new one

var date = new GlideDateTime().getDate(); // 2016-05-27

var tot = new GlideRecord('u_daily_totals');

tot.addQuery('u_day', date);

tot.query();

// If there is a record for today, add the stats to it...

if (tot.next()) {
        tot.u_total += current.u_total;         tot.update();

} else {

        // otherwise create a new one

        tot.newRecord();

        tot.u_total = current.u_total;

        tot.u_day = date;

        tot.insert();

}