6 Replies Latest reply on Dec 6, 2011 9:06 AM by weedonpaul

    Find is including records that it shouldn't

    weedonpaul

      Title

      Find is including records that it shouldn't

      Post

      Hi

      I am assuming that this is something i'm doing wrong, but I am preforming a find on wether something is selected in a checkbox set. the only trouble is that I get some records that didn't have that box selected. 

      I have 98 records of events (both past and present). In each record i have a checkbox with all the clients in. I can then select each client that would have an interest in the even. I am now doing a search of event for a particular client. I have selected find from the top I then select the company from the checkbox, and click perform find. I then get the list down to 82. the only trouble is that about 10 of those haven't got that partyicular company selected.

      What am I doing wrong?

        • 1. Re: Find is including records that it shouldn't
          philmodjunk

          Check to see what data is really stored in the check box field for these 10 records. Make a copy of the check box field and place it next to the original on your layout. Return the field format to "edit box" so that you see the actual data in the field, not just the list of check box values from the value list.

          Do you see any data there that you didn't expect to see?

          • 2. Re: Find is including records that it shouldn't
            weedonpaul

            Ok 

            it looks a mess, (I have things that don't even have checkbox's) what causes that and how can i correct it?

            • 3. Re: Find is including records that it shouldn't
              philmodjunk

              each time you click a checkbox to select a value, Filemaker appends the selected value to the end of any text already present in the field with a return character as a delimitter to separate it from the other data. Thus, if you have a checkbox group of Apples | Kiwi | Strawberry, and click Strawberry, then Apples, you get this list:

              Strawberry <Return>
              Apples

              If you should choose to update the value list used for these check boxes, the values listed as check boxes change, but the data stored in the field does not change. In the above example, if I remove Apples, and add Apple, the field displays:

              [  ] Kiwi
              [x] Strawberry
              [  ] Apple

              But if you use your edit box copy to "peak under the hood", you find that it still stores:

              Strawberry <Return>
              Apples

              If I enter Find mode and click the Apple check box, Filemaker searches the actual data and pulls up this record even though the "Apple" check box is not selected as it matched it to the hidden data: Apples.

              This is only one possible explanation as to why you are getting unexpected find results. Other possibilities include partial matches with another item in the check box's value list or a corrupted field index.

              • 4. Re: Find is including records that it shouldn't
                weedonpaul

                Thank you very much. Your explenation is probably the most likely as i have recently changed the company in questions name. is there a way of updating any checkboxes and data stored if i ever change something like this again? (you know what customers are like, always rebranding them selves.)

                • 5. Re: Find is including records that it shouldn't
                  philmodjunk

                  Best option is not to link records by names (of companies or people). Name are not unique, subject to change, and can be entered incorrectly due to data entry errors.

                  It's much better to link by an ID number instead of a name, that way you can just pull up the record for that company, change the name and all the ID number linked records that refer to it automatically update to show the new name... This cannot be done with a check box group so you'd have to change field formats to use a drop down list or pop up menu.

                  With your current check box, you'd perform a find for all records with the current company name. If it's just a few records, you can flip through each one editing them manually. If it's a large number of records, you can, if careful, use Replace Field Contents with the calculation option to remove the old name and replace it with the new.

                  Note that if you are using a check box group so that you can select more than one company for each record, you may want to replace this checkbox field with a portal to a related table where you create one related record for each company that you select for that record in order to link things up by ID numbers instead of names--the details of how you implement that depend on the structure of your database and what you need to do with these records.