5 Replies Latest reply on Dec 15, 2010 10:12 AM by philmodjunk

    Script help

    mcoslo

      Title

      Script help

      Post

      Hey all,

      I've been using FMP for years, but I'm stumped on this one - and I think by this time it is a mental block and I'll probably feel pretty stupid once it is figured out.

      I'm trying to make up a script to do the following:

      I have contact logs that start out life in Excel format. I bring each log into FMP 11, and from there I want to:

      check the county entries.

      There are 67 different counties.

      Each county is a three letter acronym. Typical is ADA for Adams county. These are in a field named "his_exchange"

      There may be multiple instances of any county in that field for different contacts.

      If a county is found, I want a numerical value entered in another field, named for the County. The value is always 1. If not find th ecounty in any of the "his_exchange" field  I want a return of 0 to be placed in the county  field.

      The maximum value for the field named for the county is never greater than one - multiple contacts in that county don't count after the first contact.

      After getting the 67 counties either with a 1 or a 0 in the field named for the county, I want to sum up the total counties contacted. last part is easy, but the interim stuff has me stumped.

        • 1. Re: Script help
          philmodjunk

          There may be multiple instances of any county in that field for different contacts.

          Multiple instances in what format? I'm guessing you have multiple values in one field, but separated by what character?

          Do you have a table of county acronyms? If not, do you have a value list of county acronyms? Either could be used with a calculation field defined in County to return a 1 if one of the listed acronyms matches one in your list of 67 counties, but the details depend on the format of your his_exchange field.

          • 2. Re: Script help
            mcoslo

            I should give a little more background on the files:

            The background is that it is an Amateur Radio contest, where people  try to contact as many people as possible during the contest. The  individual counties serve as multipliers. The participants send in a textfile log in a mostly consistent format. I import that into Excel (open office Calc actually) then into Filemaker, and occasionally mess with the import order to get everything consistent.

            Each record has a field for the Callsign of the person, the callsign of  the person he contacted, then a numerical exchange, a county exchange,  time, and frequency. Typical are hundreds of contacts.

            The field "his_exchange" has any one of 67 possible counties in it. That particular field will have the same acronym in several records, because some counties are rare, and some very common.

            As for a table or list, that would be simple to make up if needed. I had originally tried multiple finds, and that just didn't work too well.

            • 3. Re: Script help
              philmodjunk

              So his_exchange only has one value in it?

              With a table where you have 67 records, with a different county acronym in each record, you can create this relationship:

              ContestData::His_Exchange = CountyAcronyms::Acronym

              Then Your other field, defined in ContestData can use this expression to return one if the acronym in His_Exchange matches an acronym in CountyAcronyms:

              Not IsEmpty ( CountyAcronyms::Acronym )

              I suspect that might not be sufficient here as you keep referring to multiple instances so it may be that you have multiple records for the same contestant and you want a maximum of 1 returned for all of the records for that contestant. If that's the case let me know and also tell me more about the data being entered.

              • 4. Re: Script help
                mcoslo

                I suspect that might not be sufficient here as you keep referring to  multiple instances so it may be that you have multiple records for the  same contestant and you want a maximum of 1 returned for all of the  records for that contestant

                That's it. He might have say 100 contacts total, and perhaps 10 would be from one county. Maybe only one from another. The value total is the same for either, just 1.

                I'll get one of the files and let you know how they are arranged - The computer I do the soring on is at home. Thanks much!

                • 5. Re: Script help
                  philmodjunk

                  If he has 10 from one county AND 1 from another ,wouldn't you want a result of 2? (This would count the number of counties contacted and I thought that was the point of the contest.)