12 Replies Latest reply on Jul 9, 2013 5:56 AM by PeterPitt

    How do I filter records in a portal by text?

    PeterPitt

      Title

      How do I filter records in a portal by text?

      Post

           I've never used FileMaker before but I am trying to filter records in a portal (linked to another FileMaker DB) to only display those tagged with a particular word (text).

           So far I've been able to display the records alphabetically in the portal but whenever I attempt to filter them my results are empty.  I think I need to use some form of calculation but I have never created one before and I'm stumped as to how to do it.

           I've attached a screenshot of my layout below, I only want to display records with the word 'generic' or 'elec' in the Letter Code field (which are drop down list options in another database) and ignore all other records.  Can anyone offer me some advice, or better yet explain how to write the calculation (or the logic behind it) to only display the records I need?

           Thanks

      FileMaker_Record_Filter.jpg

        • 1. Re: How do I filter records in a portal by text?
          philmodjunk

               Either a portal filter or a modification to the relationship can enable your portal to limit the potal records to the desired sub set.

               What portal filter expression did you try to use?

               What is the relationship between your layout's table occurrence and the portal's table occurrence?

          • 2. Re: How do I filter records in a portal by text?
            PeterPitt

                 I'd prefer not to change the relationship as it links to other databases and I don't have full administration rights.  If I can make the change in the portal that would be preferable.  If I were to change the relationship how would that potentially affect the results?

                 I've tried using an "IF" filter, my thinking behind it being "IF letter code="generic" OR "elec" then display results but I get stuck on defining the results.

                 I'm not sure what you mean my 'table occurance', can you explain?  Sorry I'm a real novice at this and have been throw in at the deep end without much guidance.

            • 3. Re: How do I filter records in a portal by text?
              philmodjunk
                   

                        I'd prefer not to change the relationship as it links to other databases and I don't have full administration rights

                   You are not limited to a single relationship between a given pair of tables. By creating new Tutorial: What are Table Occurrences?, you can create as many different relationships as you need.

                   If you can name the table occurrence and match field names used in the portal's relationship, I can specify a portal filter expression that should produce the results that you want.

              • 4. Re: How do I filter records in a portal by text?
                PeterPitt

                     Okay, I've read the TO explanation and hopefully I have a better understanding of the situation. I have a relationship between two databases (ERDB & Autlet) and both are matching on field ISBN13 (see screenshot).

                     Autlet::ISBN13 = ERDB::ISBN13

                     This displays the relevant fields from the Autlet db in the ERDB db screen (letter code, name, author letter etc) via a portal.  I want to display a mixture of the fields from both ERDB and Autlet in my new layout but the additional matching field 'letter code' that I want to apply my filter to only exists in Autlet and not ERDB.

                     Should I be looking at this from a different angle, should I be creating the TO relationship in Autlet?

                • 5. Re: How do I filter records in a portal by text?
                  philmodjunk

                       You have two options for your portal:

                       1) filter the portal with this expression:

                       //Evaluates as true if either 'generic' or 'elec' are contained in the Letter Code field

                       PatternCount ( legal autlet::Letter Code ; "generic" ) or PatternCount ( legal autlet::Letter Code ; "elec" )

                       If the "generic" or "elec" is the exact text in the letter code field when you want to see it in the portal:

                       legal autlet::Letter Code = "generic" or legal autlet::Letter Code = "elec"

                       Make sure that Letter Code is a field of type text or a field of type calculation with text selected as the result type.

                       2) define a field in the Electronic Rights Dbase table that always contains "elec" and "generic" as a return separated list of values. You could define a calculation field, cElecOrGeneric:

                       List ( "elec" ; "generic" ) // this will only work if Letter code is exactly "elec" or "generic"

                       Select Text as the Result type And then you can set up a modified relationship like this:

                       Electronic Rights Dbase::ISBN13 = Legal Autlet|ElecGeneric::ISBN13   AND
                       Electronic Rights Dbase::cElecOrGeneric = Legal Autlet|ElecGeneric::Letter Code

                       Legal Autlet|ElecGeneric is the name I've specified for a new table occurrence for Legal Autlet.

                       The second option requires that you have design level access to the Electronic Rights dbase table, the first option does not.

                  • 6. Re: How do I filter records in a portal by text?
                    PeterPitt

                         Thanks Phil, I'll tried applying the 1st option to my portal and it does filter out the results but I'm seeing a lot of records with blank fields alongside with those matching the text of my filter.  I'm not sure why this is as I only want it to only show records with the "generic" or "elec" wording in the field list and exclude all others including those with blank letter codes.  I suspect itmay be because the letter code text is originally from a drop down list in Legal Autlet and our specific letter codes "generic" and "elec" are not always on the first row of the portal.  Is this right?  Thanks for your help so far.

                    • 7. Re: How do I filter records in a portal by text?
                      philmodjunk

                           Option 1 had more than one possible expression. Which did you use?

                           It should filter out records where the letter code field is empty. Try this, change the field format to edit box if you have it formatted with checkboxes or radio buttons. Then click into the field for one of the records where the letter code appears empty and see if text appears that meets the requirements of the filter expression that you used.

                           This sentence:

                           

                                I suspect itmay be because the letter code text is originally from a drop down list in Legal Autlet and our specific letter codes "generic" and "elec" are not always on the first row of the portal.

                           does not make sense to me. First row of what portal?

                      • 8. Re: How do I filter records in a portal by text?
                        PeterPitt

                             I used the PatternCount ( legal autlet::Letter Code ; "generic" ) or PatternCount ( legal autlet::Letter Code ; "elec" ) expression from the first option, the other alternative only displayed empty field results.

                             The letter code field has always been an edit box but it still displays empty fields along with the ones I actually want.

                             

                                  This sentence:

                             

                                  I suspect it may be because the letter code text is originally from a drop down list in Legal Autlet and our specific letter codes "generic" and "elec" are not always on the first row of the portal.

                             

                                  does not make sense to me. First row of what portal?

                             When I open the Portal Setup of the portal in my layout it shows the format as 1 of 10 rows, so I assume it's only displaying the first found record on the first row of the portal list.

                        • 9. Re: How do I filter records in a portal by text?
                          philmodjunk

                               That setting in portal setup simply determines the maximum number of rows for your portal and whether this will start with the first related record in your portal that passes the filter or not. Yours is set for the 1st 10 records. If there are more that are related and pass the filter, they will not be visible unless you enable the scroll bar opton and scroll the portal. The order of what records appear in each row is not affected by this setting.

                               Some more details to check.

                               In layout mode, click the Letter Code field and check to see what appears in the DIsplay Data from box on the data tab. It should read: "Legal autlet::Letter Code". Check to be sure that this is the case.

                               Please confirm if this is the exact portal filter expression. If yours differs in even the slightest way, please copy and post your version to this thread:

                               PatternCount ( legal autlet::Letter Code ; "generic" ) or PatternCount ( legal autlet::Letter Code ; "elec" )

                               The fact that the other filter expression did not show any records indicates that you have one of the two text patterns: "generic", "elec" in legal autlet::Letter Code ; "elec" but that you also have additional text in that field as well. Otherwise the other expression would have resulted in records being displayed. That additional text could be invisible, it could be tabs, spaces, returns, etc. and it could keep the "elec" or "generic" text from being visibl in your portal row.

                               If the Display Data From box shows the correct table occurrence::Field as I have specified, then please click into the letter code field on one where it appears empty and let me know what you see when you do that.

                          • 10. Re: How do I filter records in a portal by text?
                            PeterPitt
                                 

                                      In layout mode, click the Letter Code field and check to see what appears in the DIsplay Data from box on the data tab. It should read: "Legal autlet::Letter Code". Check to be sure that this is the case.

                                 It does.

                                 

                                      Please confirm if this is the exact portal filter expression. If yours differs in even the slightest way, please copy and post your version to this thread:

                                 

                                      PatternCount ( legal autlet::Letter Code ; "generic" ) or PatternCount ( legal autlet::Letter Code ; "elec" )

                                 Yes this is exactly as it is displayed in my filter expression.

                                 

                                      The fact that the other filter expression did not show any records indicates that you have one of the two text patterns: "generic", "elec" in legal autlet::Letter Code ; "elec" but that you also have additional text in that field as well. Otherwise the other expression would have resulted in records being displayed. That additional text could be invisible, it could be tabs, spaces, returns, etc. and it could keep the "elec" or "generic" text from being visibl in your portal row.

                                 The Display Data box on the data tab in legal autlet for the Letter Code field reads as "Legal autlet::Letter Code" and the Control Style is 'Drop down list'.

                                 The Display Data box on the data tab in ERDB for the Letter Code field reads as "Legal autlet::Letter Code" and the Control Style is 'Edit box'.

                                 Could this be the cause of the missing records, is there maybe a conflict bewteen the two Control Style options?  The drop down list in legal autlet contains multiple letter templates identified by specific text which are used depending on the type of letter being written.  The only letters I want to display are those with the word "generic" or "elec" in them, eg. New Generic ePub, 2011 Generic Epub, and ebrary & elec pub.  I thought all records with either of these words in the Letter Code field would be displayed but does the text have to match exactly, and is the field case sensitive?

                                 

                                      If the Display Data From box shows the correct table occurrence::Field as I have specified, then please click into the letter code field on one where it appears empty and let me know what you see when you do that.

                                 I do not have a second table occurance for either database.  Looking at your tutorial above it appears that I need to be able to match 2 fields between the original table and the secondary one but in my case the only field I am able to match between them is ISBN13 as all other fields are unique to each database and no other fields match.  Is this also part of the problem?

                            • 11. Re: How do I filter records in a portal by text?
                              philmodjunk

                                   Field affect the field's value so that drop down list format is not a factor here.

                                   to repeat (and do this in Browse mode): please click into the letter code field on one where it appears empty and let me know what you see when you do that.

                                   And if Letter code is not formatted as an edit box, temporarily change it to an edit box before making this test to inspect the actual data in this field. You may see the field "pop out" to a larger size when you click into it due to text being present in more than one line of text due to returns in the field.

                                    

                              • 12. Re: How do I filter records in a portal by text?
                                PeterPitt

                                     Hi Phil

                                     Thanks for your help with this, I've been having some discussions with a colleague between our last posts and I now think that the PatternCount expression has created the right results after all.  It turns out that some of our records are actually blank as we do not have corresponding letters for each record. 

                                     Previously I was working on the assumption that only records with a "generic" or "elec" letter code should be displayed but that was incorrect as some records do not have any letter codes and some, whilst they do have letters attributed to them, are neither "generic" or "elec" letters hence the reason why I can see the record but the letter code fields are blank.  Ultimately I can now view the desired letters I want if they are attached to the record and know to ignore any that aren't.

                                     I think this may have finally answered my query and perhaps I was overthinking the results I was expecting, but thanks again for all your help and advice with this.