13 Replies Latest reply on Jan 14, 2017 10:51 PM by user16545

    Convert Alphanumeric to Numeric

    user16545

      Hi all,

       

      we want to count all records in a file with NameFields starting with A, B, C, etc.

      We can identify the A's using :      Left (NameField; 1).

       

      We could ofcourse create 26 unstored calculation fields: CountA, CountB, CountC, etc?

      It would work but is not very sophisticated.

       

       

      Can we count them all in one single command, single script step, single variable or field definition?

       

      Thanks in advance!

        • 1. Re: Covert Alphanumeric to Numeric
          ErikWegweiser

          You could create a calculated field,

           

          Index = Left( NameField; 1 )

           

          Create a report, sorted and summarized by Index, with a summary total to count how many records appear in each.

          • 2. Re: Covert Alphanumeric to Numeric
            erolst

            Create a calc field

             

            Filter ( Left ( YourField ; 1 ) ; "ABCDEFGHIJKLMNOPQRSTUVWXYZ" )

             

            and count that.

            • 3. Re: Covert Alphanumeric to Numeric
              fmpdude

              This is super easy...I would create a calculated field for each LEFT(NameValue, 1) and then you can do counts on the groupings of each one using SQL.

               

              HOPE THIS HELPS.

              1 of 1 people found this helpful
              • 4. Re: Covert Alphanumeric to Numeric
                user16545

                Thanks guys,

                 

                I agree that both solutions can count the A's or C's separately.
                We want to indicate the # items per character of the Alphabet and print that # above the A, B, C line.
                Is that possible without creating 26 calculated fields or without a script which calculates the # records 26 times?

                 

                 

                 

                 

                Picture1.pngsignal to users which

                • 5. Re: Covert Alphanumeric to Numeric
                  erolst

                  user16545 wrote:

                  I agree that both solutions can count the A's or C's separately.

                  We want to indicate the # items per character of the Alphabet and print that # above the A, B, C line.
                  Is that possible without creating 26 calculated fields or without a script which calculates the # records 26 times?

                  You just need 1 field that separates out the initial so you can use it as a break field, and 1 summary field that counts it and can be displayed in the sub-summary part.

                  • 6. Re: Covert Alphanumeric to Numeric
                    fmpdude

                    Yep, that would work.

                     

                    -------

                     

                    A quick update to what I posted above ...although I don't believe FMP allows functions in the GROUP BY, in most databases' SQL implementation, you wouldn't even need an extra calculated field as the query below works fine:

                    1 of 1 people found this helpful
                    • 7. Re: Covert Alphanumeric to Numeric
                      philmodjunk

                      One additional detail:

                       

                      Using the one calculation field plus one summary field recommended earlier, you can set up a report layout that has a sub summary part (when sorted by this calculation field) and you put the summary field into this sub summary part. To avoid listing all the individual records and list just these counts, you can remove the body layout part.

                       

                      To get the results you want, you will wan to pull up a found set of the desired records (can be all records) and then sort them by the calculation field.

                      1 of 1 people found this helpful
                      • 8. Re: Covert Alphanumeric to Numeric
                        ghobel_fm

                        Something like this?

                        Screen Shot 2017-01-13 at 8.05.49 PM.png

                        This uses 3 components:

                        - a static value list "az" having A B C etc.

                        - a calculation "label" showing the letters

                        - a calculation "count" aggregating the count of records where the first letter in "name" matches the the label.

                         

                        The calculations are defined as repeating fields with 26 repetitions.

                        label ( text, global storage ) =

                        Let(

                        v = ValueListItems ( Get( FileName ) ; "az" );

                        GetValue ( v ; Get( CalculationRepetitionNumber ) )

                        )

                         

                        count( number, unstored ) =

                        Let(

                        l = Lower( GetRepetition ( label ; Get( CalculationRepetitionNumber ) ) ) ;

                        ExecuteSQL (

                        "select count( * )

                        from app

                        where lower( left( name , 1 ) ) = ?" ; "" ; "" ; l )

                        )

                         

                        Only drawback: when creating a new record, the window needs a refresh to update the count values.

                        If record creation is scripted, a RefreshPortal step on the count field refreshes the values.

                        1 of 1 people found this helpful
                        • 9. Re: Covert Alphanumeric to Numeric
                          siplus

                          Please see attachment, this is how I would implement it. No SQL, no scripts, no subsummary parts. Just plain old FM 6 technology.

                           

                          From time to time it's nice to change the summary popup (summarize repetitions) to "individually", something probably many people have been wondering "what is this good for" for ages

                           

                          For the sake of completeness, there is a rescue script, should you save a clone of the database.

                          And some fancy, non-FM 6, totally unnecessary, conditional formatting to show which letter is at the top.

                          1 of 1 people found this helpful
                          • 10. Re: Covert Alphanumeric to Numeric
                            fmpdude

                            If I were going to do this, I would create a graph showing the frequency distribution, like this:

                             

                             

                            It's not useful, IMHO, to have a count for each letter over a bunch of records. Although it might seem visually appealing initially, the success of being able to display all those counts in FMP, I believe that approach makes the user do too much work. What I mean is that with lots of character counts  at the top of the layout, the user really can't really see/understand the data's variability.

                             

                            The point: Users (people) do best with aggregated data -- presented visually.

                             

                            You could fold this graph onto your layout so it's visible or have it as a separate report. And, you could re-order it simply to show different sorts - say, highest to lowest frequency distribution by file name's first letter, for example. That sort could be independent of the layout you put the graph on.

                             

                            FMP's graphing capabilities are great. A simple SQL for the X-AXIS and Y-AXIS is all you need.

                            1 of 1 people found this helpful
                            • 11. Re: Covert Alphanumeric to Numeric
                              siplus

                              fmpdude wrote:

                               

                               

                               

                              FMP's graphing capabilities are great. A simple SQL for the X-AXIS and Y-AXIS is all you need.

                               

                              ROFLMAO

                               

                              A letter won't ever occur 3.5 times. Try to change that in the "great filemaker charting abilities," you only want 3 instead of 3.0 and you don't want a 3.5 occurrence.

                               

                              And yes, you can have the chart without SQL inside my example, but it sux.

                              • 12. Re: Covert Alphanumeric to Numeric
                                fmpdude

                                My posting was an EXAMPLE (for the OP) of showing data in a chart, vs just having data on a layout in, say, a header, but it was not an example showing the intricacies of y-axis labeling. You can see that, right?  I don't know how I could have been more clear in my posting.

                                 

                                You told me about xmchart as a better charting tool so that might be a better solution to the built-in charting. However, that wasn't the point anyway.

                                 

                                Enjoy.

                                • 13. Re: Convert Alphanumeric to Numeric
                                  user16545

                                  Wow, isn't it great fun to design and see all these elegant solutions?
                                  I can´t determine yet which of all your suggestions we will use, but thanks to ALL!!