Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Limit values in a portal based on values in another portal


aislinn

Recommended Posts

Hi there!

 

I have 3 main tables (Travels, Countries and Companies) that are linked by many-to-many relationships through join tables (TravelCountries and TravelCompanies).

 

In the travel input form, I have portals to Countries and Companies through which I would like to create new relationships (based on existing records).

 

So basically, my Countries portal has a dropdown list field in it letting me select companies.

The part that doesn't work smiley-frown is where I have the same in Companies, where I would like to see only those companies that are based in the selected Countries from the first portal.

 

Any help with this issue would be much appreciated.

Link to comment
Share on other sites

So I think I have understood your structure to look like this:

 

 

Travel -- Country

|

--- Company

 

with your interface based in a Travel layout.

 

To filter a TravelCompany portal by a selected country, you'll need to have a Country field in Company and TravelCompany. Or if a Company could be in more than one Country, you'd need a CountryCompany join table. The filtering method is different for each.

 

 

If there's only one Country per Company, use a Country field in Company with a lookup in TravelCompany. Then you can define a second TO (table occurrence) of TravelCompany and link to it:

 

Travel TravelCompany by Selected Country =

Travel::TravelID = TravelCompany by Selected Country::TravelID

AND Travel::gSelectedCountry = TravelCompany by Selected Country::Country

 

use this for the filtered portal in the interface. You many need another TO of Company linked off that new TO to show Company details in that portal.

 

 

If using a CountryCompany join table, it's a bit more complicated. The best way I've found is to grab the list of keys from the distant join table by using the list() function in an unstored calc in the interface table. Then that can be used as one of the keys for the filtered relationship to the target table. This situation is very similar to what I demonstrated just a couple weeks ago in this thread:

 

http://www.filemakertoday.com/com/showthread.php?t=18012

 

Let me know if you need more details about this. It can be kinda tricky.

Link to comment
Share on other sites

If there's only one Country per Company, use a Country field in Company with a lookup in TravelCompany. Then you can define a second TO (table occurrence) of TravelCompany and link to it:

 

Travel TravelCompany by Selected Country =

Travel::TravelID = TravelCompany by Selected Country::TravelID

AND Travel::gSelectedCountry = TravelCompany by Selected Country::Country

 

use this for the filtered portal in the interface. You many need another TO of Company linked off that new TO to show Company details in that portal.

 

Thanks for your prompt feedback! At the risk of sounding like a real amateur Opps!, I'm not quite sure how to do the above. I know how to create table occurences and relationships, but the section highlighted in red is a bit confusing for me. Any way you could slightly break this down a little for me?

Link to comment
Share on other sites

It's a relationship definition:

 

"Travel" is your current Travel TO based on the Travel table.

 

"TravelCompany by Selected Country" is another occurrence of the TravelCompany table.

 

In the Edit Relationship box, link the fields so they look something like that. You'll need two criteria.

Link to comment
Share on other sites

Thank you, that makes sense now. Sorry 'bout the confusion, I just wanted to make sure I got everything right.

 

I'll give that a go later today.

Link to comment
Share on other sites



×
×
  • Create New...

Important Information

Terms of Use