Auto filling hourly rate based on user selected and hours worked

marcusa007

I have been digging through the site trying to figure out a solution to my problem but have not been able to figure out which way to go.

I am using Datatables with editor and autofill and some other things like tabbing over. Everything works great.
The problem is that we are now adding hourly paid employees to our system where before we only used contractors.
With the hourly employees, we can't just add the lump sum that we pay as we used to do with contractors. It has to be done hourly. I have that part done where you can enter the hours, the rate and it calculates the pay. Where I am stuck is that we pay bi-weekly and we need to keep tabs on overtime.

I need to figure out how to base the hourly rate on the worker that is selected through an autofill that is set for the employee field and fill the hourly rate into the hourly rate field. It also needs to go fetch the right hourly rate based on if it is regular hours or overtime (this part shouldn't be that hard once I know how to fetch the data based on the employee).

Any help would be greatly appreciated.

Sven

allan

Hi Sven,

Is this to be done during an AutoFill drag and fill? So basically you want to be able to customise the calculation that is being performed based on the other data that is in the row? If so, you'll almost certainly have to create a custom plug-in for AutoFill that will do what you are looking for.

Inside the plug-in you have access to the DataTables API and also the cells being modified, so you can use cell().index() to get the index information for a cell and then use that with row() to select a row and thus row().data() to get the row data.

Regards,
Allan

marcusa007

Hi Allan thanks for the quick help. I am looking through the info you sent.

The employees name is pulled autofilled with autocomplete. So the data entry starts typing the name and it starts showing results in a dropdown pulled from the database. They then click on the name of the employee they want to make an entry for and it saves it in the same cell and to the database.
Based on the worker pulled the hour rates should be pulled from the database and placed in another cell.
I am not sure how Autofill drag and fill works but this may not be the same.

Thanks,

Sven

allan

Based on the worker pulled the hour rates should be pulled from the database and placed in another cell.

That doesn't sound like something that would need AutoFill. The dependent() method can be used to get values for one field from another.

Allan

marcusa007

Thanks Allen. I have been working on dependent(). For some reason I can't get the return object to update the hourly rate field that I am targeting after the employee field value changes.

editor.dependent("vendor_billing.vendor", function(val, data, callback) {
  event: "keyup change";
  $.ajax({
    url: "api/hourly_rate",
    dataType: "json",
    success: function(json) {
      console.log(json);
      callback(json);
    }
  });
});

When I console log the json object I get:
vendor_billing.hours_billed_rate : "19.50"

but the value for vendor_billing.hours_billed_rate stays 18 and my table doesn't update.

Am I missing something?

Thanks,

Sven

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan

To set a value for a field, you would need the response JSON from the server to be something like:

{
    "values": {
        "vendor_billing.hours_billed_rate": "19.50"
    }
}

Allan

marcusa007

Thanks Allen that helped quite a bit. I finally have it update information. Maybe you know off the top of your head why the hours_billed_rate is always from the previous Employee every time I change to a different one. It is as if the change to the table view is firing off before the database is updated. Is this something preUpdate handles? If yes how is it used? I tried to find an example but wasn't successful.

allan

Are you able to give me a link to your page so I can take a look at it please? I'm not sure what would be causing that off the top of my head.

Allan

marcusa007

Hello Allan,

I have set up a testing environment. Is there an email or some other secure method I can send you access.

Thanks,

Sven

allan

If you click my name above and then "Send message" that will send me a direct message.

Allan

marcusa007

I made all the changes as you described in the messages but something is still not updating.

Is the callback() function supposed to handle the updating automatically or am I supposed to create this function that updates the cell?

Thanks for all the help.

Sven

marcusa007

I have added the option submit: 'allIfChanged' that you messaged me and that made it stop updating again.

I am also still puzzled why in the table view only every other field is accessible when I turn on serverSide: 'true' or deferRender: 'true'. We pretty much only work in the table and do not use the pop up. It is faster that way. Our database is getting pretty big and it is taking a long time to load. It would really help to be able to use serverSide or deferRender.

Thanks

Sven

marcusa007

Did the login I messaged you for the FTP work?

Sven

marcusa007

Hi Allen, I am still at a loss on this. Can you assist with the cell not updating in the table when I switch to the next cell and our tables are getting really slow but we can't turn on server side since that only makes every other cell accessible.

Sven