Conditional value list from a many to many relation

[muskee]

I tried looking in the forum but couldn't find anything similar.

I'm having a hard time with creating a value list from a many to many relationship, this is the diagram.

The goal is to have 2 drop down lists,
ddl 1) shows all of the companies
ddl 2) shows the programs that are related to the company selected

I'm not sure how to create the conditional value list, do I have to create a table occurrence? if so, of what table?

This is what the ProgramCompany table looks like


Thank you guys for your time, you're awesome

[comment]

2 minutes ago, muskee said:

ddl 2) shows the programs that are related to the company selected

Where and how is the company "selected"?

[muskee]

1 minute ago, comment said:

Where and how is the company "selected"?

The dropdown lists should be in the Program table (because I have set up some important finds in this table that need this option) ,  I'm not sure what you mean with "How"? This is what I wish for

[comment]

Why would you have a Company field in the Program table? Is it a global field?

[muskee]

No, the purpose of this is to
1) select the company
2) select the program related to the company selected
3)when you select the program, I do a find in the program table and show the information of that found program.

But if necessary I guess I could have the drop down lists in another table and make a script to go to the programs table with the selected programID

[comment]

I am sorry, but that doesn't make sense to me. You should "select" the company by clicking on it in a list view of the Company table. This would then become the current record in the Company table. And you can then select a program from a portal to the Program table (placed on a form view of the Company table) and show its details by doing Go to Related Record.  No fields are necessary to hold either selection - and no value lists are required to make them.

[muskee]

4 minutes ago, comment said:

I am sorry, but that doesn't make sense to me. You should "select" the company by clicking on it in a list view of the Company table. This would then become the current record in the Company table. And you can then select a program from a portal to the Program table (placed on a form view of the Company table) and show its details by doing Go to Related Record.  No fields are necessary to hold either selection - and no value lists are required to make them.

The value lists are important because I have more than 150 programs, so I need to find the programs related to a certain company.  If I select the company and then could only see the related programs to that company, the list would reduce itself to about 10.
I just need to get the related programs and obtain the ID, with the id search for the record in the programs table.

[comment]

7 minutes ago, muskee said:

I need to find the programs related to a certain company. 

As I suggested: be in a layout of the Company table and show the related programs in a portal.

[muskee]

7 minutes ago, comment said:

As I suggested: be in a layout of the Company table and show the related programs in a portal.

So it's not possible to do the lists even from the companies table?

[comment]

Of course it's possible. It's just much more complicated than what I suggested - and if your purpose is to display a selected program then I don't see a good reason to do it the hard way.

But of course you could add a global CompanyID field to the Program table (it must be global so that different users can make different selections), link it to another occurrence of the ProgramCompany table and make your value list show only related values of the ProgramID field from there.

[muskee]

Hey man, thanks for the tip, but it didn't work, or maybe I did it wrong lol

1) So I created a global field in the Program Field, attached it a value list that brings all of the companies ID
2) Related the global field to an occurrence of ProgramCompany, related global field with CompanyID of the ocurrence
3) Created another field in the program table, made a value list where I got the ProgramID from the table ocurrence and selected include only related values from ProgramCompany Ocurrence
 

[comment]

2 hours ago, muskee said:

selected include only related values from ProgramCompany Ocurrence

It should be include only related values, starting from Program. Note that you'll need to add a ProgramName field to the ProgramCompany table if you want to also show the name in the value list. Or add another occurrence of Program  to the graph, and make it the source of the value list. 

[muskee]

Thank you for everything, that solved the problem!