13 Replies Latest reply on Nov 26, 2013 3:16 PM by KimLim

    Decimal to Fractions on Filemaker Pro 12.0V2

    KimLim

      Title

      Decimal to Fractions on Filemaker Pro 12.0V2

      Post

           I've looked everywhere on the internet to change a text/calculated fields from decimal input to fractions. Can someone help with this? Filemaker Pro 12 version does not have the custom function for custom inputs that are available in other posts in this forum. Is there any other way to do this?

           Example. Field 3 Calculation Index (50) = Field 1 Index (50) - Field 2 index (50). I want all fields to automatically change the input of decimals to fractions. Please also see attached image.

           Thank you very much.

            

      FilemakerExample.jpg

        • 1. Re: Decimal to Fractions on Filemaker Pro 12.0V2
          philmodjunk

               But I only see decimals in your post. Field 1 is apparently the sum of field 3 and Field 2.

               Does Text (50) mean that you have repeating fields with 50 repetitions? That's very rarely a good design approach...

               Did you want the first row to display like this?

               16 1/4  |   3/4 |   1

          • 2. Re: Decimal to Fractions on Filemaker Pro 12.0V2
            KimLim

                 Hi PhilModJunk,

                 Thanks for your prompt reply. cheeky

                 Yes, Text (50) is a repeating field with 50 repetitions. It saves time of making 100's fields. smiley

                 The display should look like you have illustrated.

                 16 1/4  |   3/4 |   17

                 Thanks!

            • 4. Re: Decimal to Fractions on Filemaker Pro 12.0V2
              philmodjunk
                   

                        It saves time of making 100's fields.

                   But you shouldn't have 100's of fields. You should have 100's of records and such is much more flexible to deal with than repeating fields.

                   You'll need two fields, one for entering or computing the value and a calculation field for displaying the value in fraction format.

                   Getting the correct fractions, properly simplified if such is necessary could be simple or very complex depending on what requirements you have to meet in terms of what fractions should be displayed.

                   Your examples only show a few possible fractions: whole numbers and Fourths. If the only possible decimal values are .0, .25, 0.5, and .75, this is simple. If many other fractions are possible and the fractions need to be reduced to simplest terms, then this can be very complex.

              • 5. Re: Decimal to Fractions on Filemaker Pro 12.0V2
                KimLim

                     I'm flexible, I can change the repeating fields to be 1 single field with no repeats. I'll go with anything that will make it change to fractions easier. :)
                     The lowest values needed to be are 1/8th.   Ex: .0, .125, .25, .375, .5, .625, .75, .875.

                     When I type in 1.25 in any field, it should automatically change to 1 1/4. This is so simply in excel but yet so complicated in filemaker.

                     In the image.  Size Medium will remain the same, when size any number in size M column changes all the other sizes will change. Size small is the calculation of medium - the grade. Size XS is the result of size small - the grade.

                     Do you have any recommendations?

                • 6. Re: Decimal to Fractions on Filemaker Pro 12.0V2
                  KimLim

                       Hi DavidAnders,

                       I cannot use the custom function from brianduning.com because I have filemaker pro 12 and FMpro 12 does not have the custom functions in it.

                  • 7. Re: Decimal to Fractions on Filemaker Pro 12.0V2
                    philmodjunk

                         Each application has it's own strengths and weaknesses. A "fractions" display format is not an option in FileMaker so we have to use an alternative method. The reference to Excel, BTW, probably explains your preference for repeating fields, something that became largely obsolete with the release of FileMaker 3 a few years back. wink But please note that we aren't  just suggesting that you use single fields but that you set up a table of records for such single fields so that you can still get the tabular format that you show in your screen shot.

                         I think you are going to need two fields as you are likely to need to retain access to the value of the field. I can set up an auto-enter calculation that turns 1.25 into 1 1/4 but then I can't easily use that value in searches, sorts and calculations as the value 1.25. There's a layout trick, however, that can make the two fields look like a single field.

                         First the calculation, define your calculation field to change the data in your data entry field into a fraction with this expression:

                         Let ( [ N = YourDataEntryFieldHere ;
                                   I = Int ( N ) ;
                                   d = N - I
                                  ] ;
                                    I Case ( d = 0.125 ; " 1/8" ;
                                                     d = 0.25 ; " 1/4" ;
                                                     d = 0.375 ; " 3/8 ;
                                                     // and so forth...
                                                    ) // Case
                                ) // Let

                         The Layout trick is to place the calculation field, with an opaque fill color on top of the data entry field. Use behavior settings to deny access to the calculation field when in browse mode. When you click the top field, the focus goes to the data entry field underneath and it pops to the front where you can see and edit/enter the decimal. When you exit the field, it disappears back behind the calculation field that then displays the fraction.

                         If you want to try this with a single field for both data entry and conversion into a fraction, define a text field and give it the following version of the same calculation as an auto-enter calculation:

                         Let ( [ N = Self ;
                                   I = Int ( N ) ;
                                   d = N - I
                                  ] ;
                                    I & Case ( d = 0.125 ; " 1/8" ;
                                                     d = 0.25 ; " 1/4" ;
                                                     d = 0.375 ; " 3/8 ;
                                                     // and so forth...
                                                    ) // Case
                                ) // Let

                         And then clear the "do not replace existing value..." check box.

                    • 8. Re: Decimal to Fractions on Filemaker Pro 12.0V2
                      raybaudi

                      "I cannot use the custom function from brianduning.com because I have filemaker pro 12"

                           ... but you can use that calculation, because it isn't recursive.

                      • 9. Re: Decimal to Fractions on Filemaker Pro 12.0V2
                        KimLim
                             

                                  Thank you so much for helping, I'm an absolute beginner at this. wink

                             

                                  I used

                             

                                  Let ( [ N = Self ;
                                            I = Int ( N ) ;
                                            d = N - I
                                           ] ;
                                             I & Case ( d = 0.125 ; " 1/8" ;
                                                              d = 0.25 ; " 1/4" ;
                                                              d = 0.375 ; " 3/8" ;
                                                              // and so forth...
                                                             ) // Case
                                         ) // Let

                             

                                  This calculation on a single field for data entry and fraction conversion and it worked wonderful, except there is a "0" in front of anything that is less than 1.    Ex: .25 = 0 1/4". How can I get rid of the 0?

                             

                                  Also how do I change the fractions to a calculated value? Ex 25 1/4 minus 1/4 should equal 25. But the current value equals 2504. See Image.

                        • 10. Re: Decimal to Fractions on Filemaker Pro 12.0V2
                          philmodjunk
                               

                                    Also how do I change the fractions to a calculated value? Ex 25 1/4 minus 1/4 should equal 25. But the current value equals 2504. See Image.

                               That's why I recommended that you use two fields instead of one. You'd refer to the number field used to enter 1.25 instead of the text field returning 1 1/4 text when you need to use the value in a calculation.

                               Let ( [ N = YourDataEntryFieldHere ;
                                         I = Int ( N ) ;
                                         d = N - I
                                        ] ;
                                          If ( I ; I ) & Case ( d = 0.125 ; " 1/8" ;
                                                           d = 0.25 ; " 1/4" ;
                                                           d = 0.375 ; " 3/8 ;
                                                           // and so forth...
                                                          ) // Case
                                      ) // Let

                          • 11. Re: Decimal to Fractions on Filemaker Pro 12.0V2
                            KimLim

                                 Hi Phil,

                                 I was trying to avoid doing 2 layers of 520 fields. But I guess there no way around.

                                 I really appreciate your help. I will use your calculations and 2 layer technique to make the table. I did not use the code above because it had an error. See image. 24.875 = 24. It was also missing a " after the 3/8.

                                 

                                      Let ( [ N = YourDataEntryFieldHere ;
                                                I = Int ( N ) ;
                                                d = N - I
                                               ] ;
                                                 If ( I ; I ) & Case ( d = 0.125 ; " 1/8" ;
                                                                  d = 0.25 ; " 1/4" ;
                                                                  d = 0.375 ; " 3/8 ;
                                                                  // and so forth...
                                                                 ) // Case
                                             ) // Let

                                  

                            • 12. Re: Decimal to Fractions on Filemaker Pro 12.0V2
                              philmodjunk
                                   

                                        I was trying to avoid doing 2 layers of 520 fields. But I guess there no way around.

                                   You shouldn't have anything close to 500+ fields. Each row of fields should be it's own record so that you end up with multiple records instead of multiple fields.

                                   

                                        24.875 = 24

                                   It didn't fully spell out the details for case. you were supposed to replace "//and so forth..." with the remaining code using the preceding lines as an example. 0.875 is one of the values left for you to add.

                              • 13. Re: Decimal to Fractions on Filemaker Pro 12.0V2
                                KimLim

                                     I understand now. :)... I forgot about that.. Thanks so much.cool