Insert or Update record to a joined file if it does not exsist.
Insert or Update record to a joined file if it does not exsist.
How could I insert or add a record to a joined file in DataTable Editor.
As an example, I have 2 files Payroll and Stock Contribution file.
The Payroll file would be used as the main driver file, it contains all employees.
The Stock Contribution file contains records only if an employee has made a dollar amount contribution. (joined file)
The front-end DataTable would contain data from both files.
If the employee does not have a record in Stock Contribution file the column for dollar amount would be 0.00 (this column would be available for inline editing).
If dollar amount is entered, I would need to insert the record into the Stock Contribution file.
If the employee does have a record in Stock Contribution file and the column for dollar amount was changed, I would need to update the dollar amount field in the Stock Contribution file.
I would use an Ajax call from the front-end script to call a server-side script (SSP) which would perform this function.
Are there any examples of this architecture I can refer to?
Answers
I would put your files into a mysql database (that gives you referential integrity, commit, rollback and all of these things that you don't have with simple files). Pretty simple: You have two tables "payroll" and "stock_contribution". For 1 payroll record you have 0 - N "stock_contribution" records (It is not clear whether N can be greater than 1, but you will know.) Hence it is a 1-N relationship.
Based on that the easist solution is to buy an Editor license and do this with Editor at the back end. It is a simple LEFT JOIN from "payroll" to "stock_contribution".
This example: https://editor.datatables.net/examples/advanced/joinArray.html
contains a LEFT JOIN from table "users" to table "sites".
It also contains an mJoin and hence is even more complicated than your case. Just focus on how the LEFT JOIN is done and you should be fine.
And here is a pretty good explanation of LEFT JOIN: https://editor.datatables.net/manual/php/joins#Left-Join
This post gives you some ideas on the limitations Editor might still have when making inserts of new records through LEFT JOINS, but it can be done:
https://datatables.net/forums/discussion/comment/127594/#Comment_127594