4 Replies Latest reply on Nov 26, 2013 3:41 PM by KylieSwinnerton

    Perform Find issues with Checkbox set



      Perform Find issues with Checkbox set



           I am wondering if someone may be able to help me

           I have two linked tables (Order Sheet and Vncc Form)

           I have a field in the Vncc table that has a checkbox set with the value list being Yes or No

           I am trying to perform a find that will return all line items with checkbox Yes selected.

           It works fine on the layout that is showing records from the Vncc form table, but not on the layout that is showing records from the Order sheet - instead it returns all results whether they are a Yes or a No.


           I hope I have explained myself properly. Is anyone able to offer any advice please?



        • 1. Re: Perform Find issues with Checkbox set

               You are seeing FileMaker finds work the way that they are designed to work. When you perform a find you are finding records in the table specified by "show records from" in Layout setup. So when you perform this find on the Order Sheet layout, you are finding records in Order Sheet, not records from Vncc Form. In such a find, if you specify criteria in a field from a related table, you are directing FileMaker to find all Order Sheet records that have at least one related record with the specified value. Once the find is performed and the Order sheet records with at least one Vncc Form record with Yes selected have been found, you are returned to browse mode and your layout continues to display all related Vncc Form records for each Order Sheet record in your found set whether they have the value Yes or not.

               Thus, finding records on the Vncc Form layout is the simplest way to see only Vncc Form records with the value yes. You can make this a list view layout and include data from your Order Sheet layout in a sub summary layout part if you sort your records correctly.

               If you want to show the same data on an Order Sheet layout with a portal to Vncc Form, you'll need to apply a portal filter that omits the records that do not match your criteria.

          • 2. Re: Perform Find issues with Checkbox set

                 Hi Phil

                 Thank you for the response.

                 I am not too familiar with portals and/or portal filters. This portal had been set up prior to me starting, and I have been thrown in the deep end.

                 Is it possible to script a portal filter so that the user can select whether to omit the "Yes" or "No" as required?

                 If so, is it possible for you to guide me through it at all?


            • 3. Re: Perform Find issues with Checkbox set

                   Yes, but also check your FileMaker version. Portal filters first became possible with FileMaker 11 so the following does not work for older versions.

                   Let's say your existing relationship matches fields like this:

                   OrderSheet::__pkOrderID = VnccForm::_fkOrderID

                   If you find that relationship in Manage | Database | Relationships (Chances are that the field names are different than mine), you can double click the relationship line and set up a second pair of match fields using the X operator instead of = to get this:

                   OrderSheet::__pkOrderID = VnccForm::_fkOrderID AND
                   OrderSheet::gYes  X  VnccForm::anyField--> you can select any field in VnccForm and this still works the way we need it.

                   Define a new text field, gYes for this use. Specify global storage for it. (I start global fields with a 'g' to make them easier to spot in a list of fields)

                   Now you can double click the portal while in layout mode to open portal setup where you can specify a portal filter expression. Portal filters are expressions that evaluate to either True or False for each related record. Only related records for which the expression evaluates as True will appear in the portal. Use this expression:

                   IsEmpty ( OrderSheet::gYes ) or OrderSheet::gYes = VnccForm::CheckBoxField

                   Then, put gYes on your layout. you can format it with a single value, "Yes" for the a check box where you can select only Yes or leave it empty or you can use the same Yes No value list that you use in your portal.

                   Ps: Relationships that match with the cartesian join operator, X, normally are used to match any record in one table to all records in the other table. But with FileMaker, including gYes with this operator in the relationship will force the portal to update smoothly each time you edit the value in gYes to see a different sub set of all the related records.

              • 4. Re: Perform Find issues with Checkbox set


                     Thank you for your assistance!

                     Got it working, really appreciate your help