Jump to content
Sign in to follow this  
muskee

Conditional value list from a many to many relation

Recommended Posts

muskee    1

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.
image.png.3d90c84bcdcff8e91fc6fb38ec194b6c.png

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
image.png.464d3450053908d6890056fa5590d061.png

Thank you guys for your time, you're awesome

Share this post


Link to post
Share on other sites
comment    1,392
2 minutes ago, muskee said:

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

Where and how is the company "selected"?

Share this post


Link to post
Share on other sites
muskee    1
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
image.png.9e708176a99e54b73a297c3889ff4558.png

Share this post


Link to post
Share on other sites
comment    1,392

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

Share this post


Link to post
Share on other sites
muskee    1

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

Share this post


Link to post
Share on other sites
comment    1,392

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.

Share this post


Link to post
Share on other sites
muskee    1
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.

Share this post


Link to post
Share on other sites
comment    1,392
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.

Share this post


Link to post
Share on other sites
muskee    1
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?

Share this post


Link to post
Share on other sites
comment    1,392

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.

Share this post


Link to post
Share on other sites
muskee    1

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
 

Share this post


Link to post
Share on other sites
comment    1,392
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. 

  • Thanks 1

Share this post


Link to post
Share on other sites
muskee    1

Thank you for everything, that solved the problem!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.