7 Replies Latest reply on May 14, 2011 12:15 AM by happyez

    Finding a range of numbers between two fields, and populating each number within that range into a...

    happyez

      Title

      Finding a range of numbers between two fields, and populating each number within that range into a third field

      Post

      Hello Filemaker people

      Hopefully someone may help with this predicament of mine.

      I have two fields which list demographic ages - <min> and <max>. If someone is marketing towards 17-25 year olds, the number '17' goes into <min> and the number '25' goes into <max>.

      What I would like to do is find the full list of number between and including <min> and <max>, and then populate each number into a third field <range>. In the above case, the record in <range> would have been populated with the numbers "17 18 19 20 21 22 23 24 25" that would appear as a string of text.

      My idea was possibly:

      - set up a counter in a new field called <counter>, which adds '1' to <min>. When it reaches the value in <max>, it stops.

      - do some sort of paste action into <range>, which allows it to append onto the end of the field, the number in <counter>

      But I ended up going around in circles.

      I want to put each number in that range in the one field, rather than creating a new record for each number, so I don't have to deal with 100,000s of new records!

      I've looked through different forums, and haven't had much luck in regards to this particular example.

      I would love to get some feedback.

      Thanks for your time (in advance!)

      Eric Vigo

        • 1. Re: Finding a range of numbers between two fields, and populating each number within that range into a...
          philmodjunk

          In a script:

          Set Field [YourTable::Ranges ; "" ] //clear the field of any existing data
          Set Variable [$I ; Value: YourTable::Min ]
          Loop
             Exit Loop If [ $I > YourTable::Max ]
             Set Field [YourTable::Ranges ; YourTable::Ranges & " " & $I ]
             Set Variable [$I ; value: $I + 1 ]
          End Loop
          Set Field [ YourTable::Ranges ; Trim ( YourTable::Ranges ) ] //removes trailing space

          You might use the OnRecordCommit trigger to run this script to update the field after every time it is edited.

          • 2. Re: Finding a range of numbers between two fields, and populating each number within that range into a...
            happyez

            Thanks for responding Phil

            Weird problem arises. 

               Exit Loop If [ $I > YourTable::Max ]

            Seems logical to use, and I can't think of an alternative, but even though $I is < than <max>, it still exits the script. eg for <min>=8 and <max>=11, on the data viewer I see $I become '9' when +1, but it still exits. Don't get it. It's just not greater than, but it still exits.

            Any ideas why?

            • 3. Re: Finding a range of numbers between two fields, and populating each number within that range into a...
              LaRetta_1

              "want to put each number in that range in the one field, rather than creating a new record for each number, so I don't have to deal with 100,000s of new records!"

              Can you explain why you are doing this, Eric?  I ask because it honestly doesn't make sense to me.  Is this for an export?  A range indicator as 17-25 or even 17...25 means the same thing.  And is a different value going to be in each Min Max field?

              "set up a counter in a new field called <counter>, which adds '1' to <min>. When it reaches the value in <max>, it stops."

              Depending upon the purpose and exactly what/why this is happening, you could use a recursive custom function to create a calculation without need of ever looping through any records.  But the purpose still puzzles me because if you need the individual years listed out for a find, it can be handled in different ways.  If you need each year as a relationship then that too can be more easily handled in other ways.

              It truly sounds like you want GLOBAL counters and that has not been mentioned in this thread at all.

              • 4. Re: Finding a range of numbers between two fields, and populating each number within that range into a...
                happyez

                "want to put each number in that range in the one field, rather than creating a new record for each number, so I don't have to deal with 100,000s of new records!"

                Can you explain why you are doing this, Eric?  I ask because it honestly doesn't make sense to me.  Is this for an export?  A range indicator as 17-25 or even 17...25 means the same thing.  And is a different value going to be in each Min Max field?

                > Hi LaRetta

                I have a list of thousands of organisations, and I am listing the ages of their demographics. Some, like radio stations, will aim towards 17 to 25 year olds, whereas senior citizen orgs will target the over 55s - 55 to 95 say.

                I want to be able to give others the options of choosing a range (17 to 25) of ages, or to key in a number (say, 22) and get any range that includes 22 (eg 17..25; 18..28; 21..35).

                My thought was that if each age is listed individually, then I can call up that field (call it, to ease sakes, <ages_individual)) and key in a number and see what we get.

                <min> and <max> will be include different numbers. This COULD include the same age <min>=21 and <max>=21, but very few will target one age only.

                ----------------------------------------------

                "set up a counter in a new field called <counter>, which adds '1' to <min>. When it reaches the value in <max>, it stops."

                Depending upon the purpose and exactly what/why this is happening, you could use a recursive custom function to create a calculation without need of ever looping through any records.  But the purpose still puzzles me because if you need the individual years listed out for a find, it can be handled in different ways.  If you need each year as a relationship then that too can be more easily handled in other ways.

                It truly sounds like you want GLOBAL counters and that has not been mentioned in this thread at all.

                > I think I see what you mean. 

                Could you do, say with the fields

                <min>=17

                <max>=25

                <ages_individual> as a global

                a fourth field <successful_search_records>

                with a calculation searching through the relevant records at hand, that says something like:

                If (ages_individual) ≥ (min) and (ages_individual) ≤ (max)

                Insert calc (successful_search_records) = "Yes"

                Else If (ages_individual) ≤ (min) and (ages_individual) ≤ (max)

                Go to Next Record

                Love to know if I am on your track...

                Eric

                • 5. Re: Finding a range of numbers between two fields, and populating each number within that range into a...
                  LaRetta_1

                  Well, for example, I would have two fields (both number) called AgesMin and AgesMax.  This is where I would enter 17 into first and 25 into second to indicate the demographic range for that particular record and it would be done when the record is created.  I would require that something be in both fields even if same number (you can handle this at the field validation level) and I would also validate in the real AgesMax field that the end is not less than the start.  I wouldn't ever use Min and Max for field names or variables because those are reserved characters to FileMaker.

                  Then I would create two global number fields which parallel these start and end fields, maybe called gAgesMin and gAgesMax.  This is what you present to Users to find their range.

                  I would then create a custom function. I don't know if you are using Advanced or not ... if you can't create the custom function, you can can ask someone else to create it or you can fudge and create a recursive regular calculation or even use repetitions.  Here is a custom function which can be created:

                  Name it: expandRange
                  Parameter: start
                  Parameter: end

                  Case (
                  start  ≤  end ;
                  start & ¶ & expandRange (start  + 1 ; end ) ; end )

                  You then create a regular calculation called CAgesRange and enter this:

                  expandRange ( AgesMin ; AgesMax )

                  This will produce a range as a multiline which can easily be searched so now write your script which will fire after User enters desired range into globals:

                  Enter Find Mode [ uncheck pause ]
                  If [ not gAgesMin and not gAgesMax ]
                  Show Custom Dialog [ "No values entered.  Please try again." ]
                  Halt Script
                  End If
                  Set Field [ cAgesRange ; gAgesMin & "..." & gAgesMax ]
                  Set Error Capture [ On ]
                  Perform Find[]
                  If [ Get ( LastError )  ]
                  Show Custom Dialog [ "No Records Found" ]
                  ... do whatever you wish if no records - probably nothing
                  Else
                  ... go to your list view or whatever to display the found records.

                  Note that I am not trapping for empty gAgesMin or gAgesMax.  This is because if either are empty, FM will still work properly and include it as if the start was 1 and the end was whatever, i.e. if User entered 45 in gAgesMax but no min, FM would generate it as ...45 and would include all ages prior to and including 45 (or written another way, result would be <= 45.  But I DID add in blue a trap if both fields are empty.

                  Instead of a custom function, you can also use repeitions to expand the range.  You simply include 100 repetitions.

                  By using a calculation, there is never a need to loop or set records ever ... setting records in any way is known as marking and it is very bad idea particularly when in multi-user mode because one User would get the other Users records. 

                  • 6. Re: Finding a range of numbers between two fields, and populating each number within that range into a...
                    philmodjunk

                    BTW,

                    If you use the script, make sure that YourTable::Max is of type number when you check its type in Manage | Database | Fields. That's the most likely reason the script halts early.

                    • 7. Re: Finding a range of numbers between two fields, and populating each number within that range into a...
                      happyez

                      Heya guys

                      Tried both versions. They both work beautifully. Leant a lot of little things (like putting 'not' at the beginning of a field in an If - very handy!), and developing my custom function usability. I find interesting that someone can see a way to cut out a whole process that you can't see, and that allows you to be able to then apply that to your other problems...and I liked the ability for the script to calculate the numbers, even if one field is left blank. Very cool.

                      Thanks so much for your time and energy. That has unblocked a doozy of an issue that was laying around for about a year!