10 Replies Latest reply on Jun 2, 2014 1:26 PM by philmodjunk

    inserting values in field by find

    MichaelCoslo

      Title

      inserting values in field by find

      Post

           Bit of a noob alert here.

           I have a FM database in which one of the fields contains county information. It consists of three letter abbreviations for any particular county. Typical would be CEN for "Center" County.

           What I want to do is to have a script search for the counties in that field, then if that particular value is found, to cause the value of another field to equal 1. If not found, the related field is 0. Then after the search for all of the counties is complete, the total number of counties is added from the related fields, then summed.

           In this case, there would be around 70 finds.

            

        • 1. Re: inserting values in field by find
          philmodjunk

               Please describe your "around 70 finds" in more detail. How will the user specify (what I assume is) the 70 different counties?

               This sounds like a task to do with relationships rather than "finds". The user can create one record for each county in a different table linked to your table of counties by the abbreviation field. A simple calculation field can return the value 1 if there is a matching record with that abbreviation and a summary field can then compute the total matches.

               But a number of different approaches are possible besides that one.

          • 2. Re: inserting values in field by find
            SamRembert

                 It sounds to me like you want to do the following:

                 - Dialog asks for county name

                 - Script finds records which contain that name in the "County" field

                 - Any record which contains the entered county gets a "1" in the "Count" field

                  

                 My first question is, will there be multiple entries with the same county name?  Should those multiple entries with the same county name add up to 1, or add up to the number of entries, for the purpose of the sum at the end?  

                 A pretty simple script can take a value, perform a find, and insert a 1 into the count field of records containing that value.

                 A summary field summing the count field could be placed in a trailing grand summary to keep live track of the total count. 

                 Sam

            • 3. Re: inserting values in field by find
              MichaelCoslo

                   Thanks all, I'll try to answer both in one post. The records are part of Amateur Radio contact logs for a contest.

                   The records come to me with 10 fields:

                   Frequency, Mode, Date, Time, Mycall, Mynumber, MyCounty, Hiscall, Hisnumber, Hissec

                   Each database can have hundreds or thousands of records.

                   There will be other functions performed on the database, but the ones in the "Hissec" field are of interest here.

                   There are awards for having all of the counties contacted, and there may be several contacts in one county, but none in another, so I need to find how many total counties have been entered.

                   Things that would be nice, but not critical would be notification of which counties were contacted on the layout, but even a simple summing is sufficient at this time.

              • 4. Re: inserting values in field by find
                philmodjunk

                     Does this mean that you have a table where each contact is logged in a different record?

                     A summary report could easily list all the contacts made by a specific operator such that there is one row for each county. And while it's a bit of an odd set up, this same report can then show the total counties contacted. In fact, there is more than one option for how to get that count if you use FileMaker 12 or newer.

                • 5. Re: inserting values in field by find
                  MichaelCoslo

                       Hi Phil,

                       Yep, each contact has all those fields. and then is entered as a separate record.  Cabrillo format , they call it.

                  • 6. Re: inserting values in field by find
                    philmodjunk

                         Then set up this summary report:

                         Make a list view layout based on this table.

                         Replace the body layout part with a sub summary layout part "when sorted by" your county code field.

                         Put the fields you want for name and code of the county in this sub summary layout part.

                         Perform a find for the records that you want (such as all the records logged by a single operator or those logged over a specified range of dates) and sort the found set by the county code field.

                         You'll get a report of one row for each unique county that was logged.

                         Here's a tutorial on summary reports that describes this technique and others: Creating Filemaker Pro summary reports--Tutorial

                         If you want to get a count of all the counties thus logged with each county counted over once:

                         For FileMaker versions older than FMP 12 and for developers who don't want to use SQL: How to count the number of unique occurences in field.

                         For those with FileMaker 12 and newer and that are willing to do a bit of SQL (This option is simpler and less "arcane" than the previous option): A new way to count unique values in FileMaker 12

                    • 7. Re: inserting values in field by find
                      MichaelCoslo

                           Thanks Phil,

                           subsummary part worked well. It shows a listing of one individual county for each county operated, no matter how many times it was operated, which is just what I want. Now what I would like to do is to take that information, and in other fields which represent the individual counties, to give those a value of 1 so that they can be summed.

                           Is is possible to do this by a find in a sub summary field?

                      • 8. Re: inserting values in field by find
                        philmodjunk

                             It would seem that I have already answered that need:

                             

                                  If you want to get a count of all the counties thus logged with each county counted over once:

                             

                                  For FileMaker versions older than FMP 12 and for developers who don't want to use SQL: How to count the number of unique occurences in field.

                             

                                  For those with FileMaker 12 and newer and that are willing to do a bit of SQL (This option is simpler and less "arcane" than the previous option): A new way to count unique values in FileMaker 12

                             If that is not working for you, please explain how/why one of those options does not serve for what you need here.

                        • 9. Re: inserting values in field by find
                          MichaelCoslo

                               My bad.

                               There is other data in the field, which is "sections", which correspond roughly to states - some states have more than one section depending on population.

                               That method does indeed give me a total count of everything, but I'd like the sections and counties split up. Because the results are split between counties and sections. It would be pretty simple if they were in separate fields, but I can't control the format.

                               Let's just call it a day, and I'll do it by hand like I have for years.

                          • 10. Re: inserting values in field by find
                            philmodjunk
                                 

                                      It would be pretty simple if they were in separate fields, but I can't control the format.

                                 And that's not a format that you've described here. wink

                                 I don't think you need to do this by hand, but it may take some creative use of calculation fields to get that data to separate into different fields so that you can set up two sets of sub summary layout parts nested one inside the other. The outer/upper sub summary part would be set to group by section and the inner/lower sub summary part would be set to group by county.

                                 And then you can get counts for both counties and sections or just by sections, but grouped by section...