Define this calculation field with "text" selected as the result type:
Substitute ( YearCommaField ; "," ; ¶ )
to turn your comma delimited list into a return delimited list. Let's call it cYearList.
Then you can use cYEarList as the match field in your portal relationship to match values by year and then specifying a year will match to any one of the return delimited values or you can use this expression in a portal filter calculation:
Not IsEmpty ( FilterValues ( PortalTable::cYearList ; LayoutTable::SelectedYear ) )
Phil, Thanks for the help. As a newbie I'm still struggling. I see how the cYearList seems to work but I can't get the portal to work right. This is an auto parts database filtered on Year, Make and Model. I have 3 fields in table one called SearchYear, SearchMake, SearchModel with a portal into the 2nd table with all the data. I want to be able to enter the year, make and model and have the portal show me available items for that criteria but I must be doing something wrong because it still will only give me answers if the year is stand alone. Any help would be greatly appreciated. Sorry if this is a "novice" question.
Novice questions are what this forum is all about so there is no need to apologize.
It would appear that you need this relationship, but with your names substituted in place of mine.
LayoutTable::SearchMake = PortalTable::Make AND
LayoutTable::SearchModel = PortalTable::Model AND
LayoutTable::SearchYear = PortalTable::cYearList
Thanks for being patient. I must be missing something because it still will only show me the records that have the year I'm searching on as a single entry. If I don't put any filter in the portal it shows me all the records for that make since that is how the tables are related. If I add the table1::SearchYear = Table2::cListYear it will only show me a single record that has just that year and no other years. What am I missing? Screen shots are attached. Thanks
You are trying to specify a calculation where I am describing the match fields to use in the relationship.
You currently only match records by Make. What I am suggesting is that you drag from SearchYear to cYearLIst and from SearchModel to Model so that you have three pairs of match fields instead of one. You can also double click the relationship line and add the extra pairs of match fields there.
I've tried that. When I do that it won't return any records at all. Any thoughts?
Is there something else I can show you that would help figure this out?
That should work. I've used this method myself. What I am recommending here does not use a portal filter as it's all handled by the relationship. If you specify a make, model and year, any records in the related table that have that make, that model and the list of years contains at least one year of the same value, will appear in the portal.
But note the qualifiers in what I just wrote. It's possible that I've assumed details about how this should work that don't apply to your situation.
Also, make sure that cYearList has TEXT selected as the return type. If you have number selected, it will appear to work--you'll be able to see years in the field, but it won't work in the relationship as it's index won't have the correct values.
Here's a demo file: https://dl.dropboxusercontent.com/u/78737945/YearListDemo.fmp12