Load data in table view

Classic Servoy
1

Want to display data in table view which is similar to below query all text field are bound to column in table based on their relationships
I want to load all data based on some criteria. Do I need to pass sql query based on join with AND clause to loadRecords. Can anyone know how to display data in table based on below query.

SELECT a.emp_id, c.startshift, c.endshift, d.Timeofftype,b.startjobs,b.stopjobs
From employee a inner join shift c on a.shift=c.shift
left outer join transactions b on a.emp_id=b.emp_id AND            //SOME CONDITION TO FILTER DATA
Left outer join timeoff d on a.emp_id=d.emp_id
2

In Servoy:

Make a new form based on the Employee table;
Add relationships to the shift, transactions and timeoff tables (looks like based on the emp_id based on your queries);
Place the emp_id, startshift (through the timeoff relation you defined), endshift (through the same relation), timeofftype (through the transaction relation), startjobs and endjobs (through the transactions relation);

Now you can load the records by using a query - but it needs to select just the PK (the emp_id, in this case):

controller.loadRecords("SELECT emp_id FROM employee WHERE //some condition ORDER BY emp_id")

Servoy will handle all the SQL joins for you - so all you have to do is to FIND the employees you want.

Hope this helps,

Bob

3

Thanks Bob,

Created relations and bound all elements in table view to table column. At time of load records I want to load record with condtion which is based on transactions table

SELECT a.emp_id, c.startshift, c.endshift, d.Timeofftype,b.startjobs,b.stopjobs
From employee a inner join shift c on a.shift=c.shift
left outer join transactions b on a.emp_id=b.emp_id AND CONVERT(VARCHAR(8),b.startjobs,101)=CONVERT(VARCHAR(8),GETDATE(),101)  
Left outer join timeoff d on a.emp_id=d.emp_id

In my load record I want to add condition for left outer join transactions b on a.emp_id=b.emp_id AND CONVERT(VARCHAR(8),b.startjobs,101)=CONVERT(VARCHAR(8),GETDATE(),101). How can I add condition since form is bound with employee table.

4

Any suggestion for above discussion.

Thank you in advance.