Building a new wages/accounting system

Following on from:

I’m wanting to create an account automatically for each employee as they are added onto the ‘system’. Most of you will be aware of how I add new employees using a [?Prompt] and so that snippet of code needs adjusting slightly to do this. not being a code guru myself, and breaking it last time I wondered if someone could check it out for me.

function createUser(u,p) {
  // can get User and Pin fed via function or 
  //use Data.Get to retrieve Program Settings
  var usr = (typeof u === 'undefined' || u=='' ? Data.Get("Username") : u);
  var pin = (typeof p === 'undefined' || p=='' ? Data.Get("PIN") : p);
  var grp = 'Employee';

  var q="SELECT [Id] FROM [UserRoles] WHERE [Name]='" + grp + "'";
  var grp = sql.Query(q).First;

  // more validation
  usr = (typeof usr === 'undefined' ? '' : usr);
  pin = (typeof pin === 'undefined' ? '' : pin);
  grp = (typeof grp === 'undefined' ? '' : grp);
  //check for existing user with that name
  var q="SELECT COUNT([Name]) AS [COUNT] FROM [Users] WHERE [Name]='" + usr + "'";
  var nameCount = sql.Query(q).First;

  //ceck for existing user with that pincode
  var q="SELECT COUNT([Name]) AS [COUNT] FROM [Users] WHERE [PinCode]='" + pin + "'";
  var pinCount = sql.Query(q).First;

  if (usr!='' && pin!='' && grp!='' && nameCount==0 && pinCount==0) {
		var addEmployeeAQ = dlg.AskQuestion('Add Employee Named: '+usr+' ?','OK,Cancel'); 
		if ( addEmployeeAQ == 'OK' ) {

			// run SQL to create the User
			q = "INSERT INTO [Users] ([PinCode], [Name], [UserRole_Id]) VALUES ('" + pin + "', '" + usr + "', '"+grp+"')";
			var r = sql.ExecSql(q);

			// create user entity
			dlg.AskQuestion('Employee named '+usr+' added.','OK');

		} else {
			dlg.AskQuestion('Adding User '+usr+' Aborted','OK');
	} else {
	dlg.AskQuestion('User or PinCode Already in use.','OK');

My next task will be to play around with the accounting of another [?Prompt] question, but ill get to that when I’m set up with this.

Merry New Year


Looks good so far. :wink:

1 Like

Thanks to you guys already! You lot built this with/for me :stuck_out_tongue:

but I need to add the ‘Add Account’ to the code so that when an employee is added it also creates an account in the same name


1 Like

I love it how you show me in the right direction :stuck_out_tongue:

Best way to learn me is that.

I cant help thinking though wether ill hit a wall near the end?

In my head id like to be able to select an employee from a dropdown which lists the employee accounts… if you get me? and adds that to the system setting, and then updates their account when a wages amount has been inputted

Im sure I remember discussion about having options listed on a ?prompt, maybe as csv but think plan was you can call/create csv with a script.

Payroll using Employee Accounts. Wages are tracked Hourly by Timeclock. Payrates are stored as Entity Custom Data and vary depending on Employee Position, with different Rates for Overtime and Holidays.

Uses Reports to compute Earned Amounts and show Account Transactions (Wage Payments), and allows Printing of those Reports including a signature line.

Expand this image to show full detail (the image preview in the Forum is zoomed and cropped for some reason, at least in Chrome):