[Jino]

I know how to use set_relation_n_n as it is.

My question is, can the Select table be a join between 2 tables instead of just a normal table?

E.g.

Pay (wages)
------------
ID (PK)
WorkerID(FK)
Date
Amount

Advances
------------
ID (PK)
WorkerID(FK)
Date
Amount

Deductions
--------------
ID (PK)
PayID(FK)
Advances(FK)
Amount

worker
-----------
ID(PK)
Name

This is a payroll system where workers can request 1 or more advances(loan) before their monthly pay.
A worker can have one or many advances.
A pay can have one or many deductions
A deduction can only have one advance.
An advance can have many deductions

During pay day, the manager issues pay but want to make deductions from the pay against any outstanding advances (the deductions may be full or partial amount against the advance amount).

So, on the pay form, I have a set_relation field to Worker table to select worker.
$crud->set_relation('WorkerID','worker' ,'Name');

I have a set_relation_n_n field using Pay table as "this table", Deductions table as "Relation table" and Advance table as "Selection table".
$crud->set_relation_n_n('Deduction List','Deductions','Advances','PayID','AdvanceID','WorkerID','Priority');


This will bring up all the advances rows for all workers and I can only see the worker's ID in the Deduction List's Select column.

What I want to do is, when the user select a worker, I want the select column to only show all the advances belonging to this work in the Select column.

Also, how can I show the worker's name instead of worker's ID in the select column?

Hope that makes sense.

[Jino]

Hi,

I thought I should give a better explanation of what Im doing.

[attachment=201:payroll.jpg] This is the screen shot of what Im trying to achieve. The drop down list for worker's name is OK.

The Deduction list (set_relation_n_n) is the problem. When the form starts, it shows all the Advances' worker's ID in the Select table. How can I make it to show the Worker's name and Advance Type and Advance Date instead?

Also, how can I make the select list filter to the worker selected it the workerID field?

Thanks...

[Jino]

OK... after reading so many posts, I think I have to use the set_model function to extend grocery_crud and override set_relation_n_n. At least now I know what I have to do...
Great work on this library. Im sure Im only touching the surface what is possible!

[Jino]

I'm trying to use a MySQL view in the select table of set_relation_n_n by extending grocery_crud. I see I have to put in a primary key in the view which doesnt exist. Have anybody tried using a View? Or should I just set a WHERE param?
Thanks in advance.

[Jino]

OK. Got the set_primary_key working. The set_relation_n_n field is working as I want it.

It updates all the related tables correctly.

The only thing is doing the WHERE on the selection table. I was wondering if I can use the search/filter field in set_relation_n_n field to only show the items I want. This is in the Add/Update form.

I feel so lonely talking to myself.. haha

[imjooseo]

Hi Jino,

I ran into this issue too, I'd like to set WHERE in joined table in relation n-n.
I'll make a workaround and see if I can get it working.

Don't feel so lonely!

[David Leiva]

Same thing... anyone could set a where clause joined with the relation table ? I need only a filter of results....