Showing related fields from one portal to another

[aislinn]

Hi there!

I have a couple of tables (Travels, Companies and Countries) that have a many-to-many relationship.

This means that in my Travel input form, I am using portals to add the visited Countries and Companies (1 portal for each).

Is it possible to list only the Companies related to the selected Countries in the respective portals?

[bcooney]

I'd love to help, but need more info on how the tables are related. You say you have many-to-many relationships. How did you resolve that? With join tables?

[aislinn]

Yes, I have join tables (TravelCompany and TravelCountry) that contain the ID-fields of each of the linked tables (Travel_ID, Company_ID, Country_ID).

For each of these relationships I have selected the option to create records via the relationships on both sides, and delete related records only on join table side.

[aislinn]

I should also specify that in each portal, I work with drop-down lists (CountryNames in CountryPortal and CompanyNames in CompanyPortal).

The aim is, after selecting the different countries that were visited, to show only the Companies based in the selected Countries in the Company drop-down in CompanyPortal.

This is only to create new relationships in the join tables based on existing records from the main tables. Completely new records are based on a script so there is no problem there.

Hope I'm making sense here...

[comment]

I don't see why you need the join table TravelCountry. Once you have selected the visited companies, the visited countries are obviously the countries of those companies - unless you have visited a country without visiting any company there.

If you only need to filter the value list of Companies, I would add a global field gCountryID to the Travels table and use it to link to a second occurrence of the Companies table. The field could be a checkbox. Then your value list can be based on related values only from Companies 2, starting from Travels.

(This is assuming that the relationship between Countries and Companies is one-to-many.)