7 Replies Latest reply on Jan 17, 2014 12:54 PM by philmodjunk

    Field with comma separated text



      Field with comma separated text


           I'm new to FMP so go easy on me here.  I've imported an excel file where the "years" field contains multiple years separated by commas so one record in the years field might read like this "2001, 2002, 2003".  Some records have one year and some have as many as 10 or more.  I'm using a portal and trying to filter it based on year search but FMP won't return any fields that have the multiple years in it.  Only if it has the one year I'm looking for by itself.  How can I set this up to filter it the way I want?



        • 1. Re: Field with comma separated text

               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 ) )

          • 2. Re: Field with comma separated text

                 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.

            • 3. Re: Field with comma separated text

                   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

              • 4. Re: Field with comma separated text



                     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

                • 5. Re: Field with comma separated text

                       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.

                  • 6. Re: Field with comma separated text


                         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?

                         Thanks again.

                    • 7. Re: Field with comma separated text

                           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