How to work with related table ?

[nettemor]

I have a servicesystem ("jobs" table) where client servicejobs are registrated. In this system the clients name are entered, and also the postalno for the area the job are to be done. Based on the entered postalno a dropdown are filled with all the persons that can be assigned to do a job in the selected postalno area.
So i have a databasetable "persons" with persons. These are the persons can be assigned to jobs. And every person serves multiple postalno's areas.

And here is the thing, i am not sure whats the best way to do this, so i ask for your opinion.

As i see it i have two ways to solve this:
1. I can have a "person-jobs" table related to the persons table. Here i can enter each postalno that this person operates in. When creating a form for this it seems to be quite a job to add all the postalno's for each person. This way i have to add record by record for each new postalno.

2. Use lists (i belive i noticed there was a defined fieldtype for this). If i can use two lists, one with all the postalnos and the other list with the selected postalno's this will be in the same form (and not in a persons-job table). This will make the adding easyer for the user.... which strikes me as more appealing. But here is what i am thinking, if i use this option will it be possible to do a lookup to find the correct persons by using a cascadingdropdown (the same way as i do if i use method 1) ?

Basically my question is:
What is the best way to add these postalno's for each person? And if it is option 2, - can i easily do a lookop in this field to find the persons ?

Hope to hear your opinions, thanks

 

[cheesegrits]

Hard for me t0 answer without actually trying it.

For the second option ... when you select 'checkbox' (or 'multiple select dropdown') as the type for a join element, Fabrik actually automatically creates a parent-child mapping table for the many-to-many relationship. So in your case, if you made the postcode join on the person table a checkbox, it would be a person-postalcode table, which contains foreign keys to both the person and the postalcode table.

I think you could then create a CDD on the jobs table which uses that mapping table.

My best suggestion is try it and see.

-- hugh

 

[nettemor]

Hi Hugh
and thanks for getting back to me.

I landed on option 1 mainly because i could not figure out how to populate the picklist option directly from a database table.

 

[cheesegrits]

Not sure if that's a question, or a statement? Do you still need help?

-- hugh

 

[nettemor]

Hi again, that was ment to be an explanation for what i selected to do to solve my issue. Sorry my english
Appreciate the follow up Hugh.

 

[cheesegrits]

Glad you got it sorted out.

-- hugh