CODEWITHSUNDEEP
netsuite
CodeMoto
CodeMoto

Reputation: 353

Getting the greatest value for a field for all records

For our Employee records within NetSuite, we have a custom field called "Employee Number" with an ID of custentity1. I've created a workflow that will automatically create a new employee record and populate various fields but the one I'm having difficulty with is the Employee Number field. All I want to do is to grab the largest employee number there is out of all of the Employee records and add one to it for the new employee record.

The Employee Number field is a free-form text field so I know I'll have to use TO_NUMBER, but anytime I try and reference {custentity1} I keep getting an error saying that field is not found.

UPDATE: I've created a new custom field for our employee records called "Employee No." with an ID of custentity_employeenumber. I've also created a javascript file with the following:

function getMaxEmployeeNumber(){
   var empNumber = nlobjSearchColumn('custentity_employeenumber', null, 'max');
   return empNumber;
}

But how to do I get this to work with my records?

Upvotes: 0

Views: 2386

Answers (3)

Rusty Shackles
Rusty Shackles

Reputation: 2840

If you use this search column

nlobjSearchColumn('custentity1', null, 'max');

You can also sort it in decreasing value so that the first result is always the max. Something like

nlobjSearchColumn('custentity1', null, 'max').setSort(true);

Upvotes: 0

erictgrubaugh
erictgrubaugh

Reputation: 8847

NetSuite does have an auto-numbering mechanism built in to its native functionality that most of our customers use for this exact purpose. Is there a special reason this functionality is not being leveraged? This functionality is accessible at Setup > Company > Auto-generated Numbers.

I do not work much with workflows, so I do not know if this same functionality is possible there, but here is how I would solve this in SuiteScript:

  1. Create User Event script that is executed on Before Submit Create event for Customer records

  2. Create a Customer search that has a Search Column for custentity1 with a summary type of max

    new nlobjSearchColumn('custentity1', null, 'max');

  3. Running this search should give you 1 result, which is the maximum customer number. You can then just add 1 to it.

You could create a similar Saved Search in the UI to see what the result set looks like.

This will only really work if the field is a Number, not Text. I would suggest changing the field to an Integer field if you know that it will always be a number. This may clear out existing data, so first you could export all customers and their number to Excel and then do a CSV import after changing the field.

Upvotes: 1

felipechang
felipechang

Reputation: 924

How are you looking for the last employee in a workflow?

I know this can be done in js:

  • Search employees - returns max 1000

  • For number of employees give me the custentity1 of the last one - nlapiLookupfield('employee',employees[employees.length],'custentity1')

  • Add +1 and save on new record

Upvotes: 0

Related Questions