8 Replies Latest reply on Jul 6, 2016 5:15 AM by mikebeargie

    Leading zeros

    Stu412

      Hi there

       

      Bit of a dumb one, but it's late in the day....

       

      I have data which I'm looking at in table view for a particular field:

       

      .71

      .45

      2.71

      .38

       

      What I need to have happen is for it to be held (rather than simply formatted) in the DB as a complete number:

       

      0.71

      0.45

      2.71

      0.38

       

      These numbers then are used wholly further downstream by ExecuteSQL which otherwise would use the decimal point as a delimiter.  However, if I have the zero in place first, this is no longer a problem.

       

      Only problem is that I have 21,000 records to fix.....

       

      Any ideas gratefully received.

       

      Thanks

        • 1. Re: Leading zeros
          DamianKelly

          Replace with calculated result and

           

          case(

          yourFieldHere < 1 and yourFieldHere > -1; 0 & s;

          yourFieldHere

          )

           

           

          I tried getasnumber(getastext("0" & ".25")) with no joy

           

          As a slight aside a custom function formatting numbers correctly for SQL might be a way to go too

          • 2. Re: Leading zeros
            mikebeargie

            decimal storage is a valid numeric format, so stating:

            These numbers then are used wholly further downstream by ExecuteSQL which otherwise would use the decimal point as a delimiter.  However, if I have the zero in place first, this is no longer a problem.

            would indicate to me that what you're trying to do with ExecuteSQL() downstream is invalid. FileMaker operating as an ODBC data source for external SQL, or just in the ExecuteSQL() function itself knows how to handle numeric values and decimals.

             

            What exactly are you trying to do later that is not working?

            • 3. Re: Leading zeros
              Stu412

              Hi Mike

               

              I'm working on a sub summary report which uses say, 32 records out of 20,000 based on those held for a particular customer.  These 32 records can be summarised normally using standard FM functions. 

               

              However, I need to compare these 32 records against the  average and 3rd quartiles of the 20,000 data set as a whole.  I think I'm correct in saying because the remaining 19,000 records are not part of the found set, they're not subject to standard summary formulas.  However, ExecuteSQL is agnostic about tables and found sets and you're able to specify in the parameters exactly what's required, so I do this for the calculations across the board.  This gives me the overall average and in turn, I can use a CF for the 3rd quartile. 

               

              When I select as SQL, the decimal point acts as a delimeter, so instead of say, 0.56, it calculates against 56, giving incorrect results.

               

              Further information on this community thread here: Select full decimals using ExecuteSQL

               

              This is where I need anything lower than 1 to be appended with "0.", and anything higher than 1 to be left as normal.

               

              Thanks

              • 4. Re: Leading zeros
                okramis

                Have you tried the version of the CF from user19752?

                You really only have to replace "Words" through "Values" like:

                 

                WordCount --> ValueCount

                RightWords --> RightValues

                MiddleWords --> MiddleValues

                a.s.o.

                 

                then the values get treated right

                 

                regards

                Otmar

                • 5. Re: Leading zeros
                  DamianKelly

                  If this is only going into to your SQL then how about using the CAST function? something like CAST( yourField AS DOUBLE) Although you might want to try and find some documentation on CAST so you understand the full ramifications of using it. I have never used it in FileMaker but in MySQL there are a couple of little gotchas......

                  • 6. Re: Leading zeros
                    mikebeargie

                    In that article though you state it’s returning .52, NOT 52.

                     

                    Also Beverly states that ExecuteSQL() returns a result as text. You would need to parse the resulting list back to individual numeric records, or run it through a custom function to alter each value to display as you need. If you do the former you can obviously use filemaker’s built in decimal field styling.

                    • 7. Re: Leading zeros
                      greatgrey

                      Is the field/calculation type text instead of number?

                      • 8. Re: Leading zeros
                        mikebeargie

                        If he’s returning a LIST of results, the result will be text.

                         

                        If he is returning a single result, then he can return it as a number.