pasternik Posted October 4, 2006 Share Posted October 4, 2006 Hi, I really need some help here, I have imported an excel spreadsheet into my current DB, and I have a column that in excel is shown as a fraction, but when imported is shown as a decimal. I have bought the Refer file also thru Filemaker and it gives an example as: Int(Number) & " " & Round((Number - Int(Number)) * (Case( Mod((Number - Int(Number)) * 2; 1) = 0; 2; Mod((Number - Int(Number)) * 3; 1) = 0; 3; Mod((Number - Int(Number)) * 4; 1) = 0; 4; Mod((Number - Int(Number)) * 5; 1) = 0; 5; Mod((Number - Int(Number)) * 6; 1) = 0; 6; Mod((Number - Int(Number)) * 7; 1) = 0; 7; Mod((Number - Int(Number)) * 8; 1) = 0; 8; Mod((Number - Int(Number)) * 9; 1) = 0; 9; Mod((Number - Int(Number)) * 10; 1) = 0; 10; Mod((Number - Int(Number)) * 11; 1) = 0; 11; Mod((Number - Int(Number)) * 12; 1) = 0; 12; Mod((Number - Int(Number)) * 13; 1) = 0; 13; Mod((Number - Int(Number)) * 14; 1) = 0; 14; Mod((Number - Int(Number)) * 15; 1) = 0; 15; Mod((Number - Int(Number)) * 16; 1) = 0; 16; Mod((Number - Int(Number)) * 17; 1) = 0; 17; Mod((Number - Int(Number)) * 18; 1) = 0; 18; Mod((Number - Int(Number)) * 19; 1) = 0; 19; Mod((Number - Int(Number)) * 20; 1) = 0; 20; Mod((Number - Int(Number)) * 21; 1) = 0; 21; Mod((Number - Int(Number)) * 22; 1) = 0; 22; Mod((Number - Int(Number)) * 23; 1) = 0; 23; Mod((Number - Int(Number)) * 24; 1) = 0; 24; Mod((Number - Int(Number)) * 25; 1) = 0; 25; Mod((Number - Int(Number)) * 26; 1) = 0; 26; Mod((Number - Int(Number)) * 27; 1) = 0; 27; Mod((Number - Int(Number)) * 28; 1) = 0; 28; Mod((Number - Int(Number)) * 29; 1) = 0; 29; Mod((Number - Int(Number)) * 30; 1) = 0; 30; Mod((Number - Int(Number)) * 31; 1) = 0; 31; 32));0) & "/" & (Case(Mod((Number - Int(Number)) * 2; 1) = 0; 2; Mod((Number - Int(Number)) * 3; 1) = 0; 3; Mod((Number - Int(Number)) * 4; 1) = 0; 4; Mod((Number - Int(Number)) * 5; 1) = 0; 5; Mod((Number - Int(Number)) * 6; 1) = 0; 6; Mod((Number - Int(Number)) * 7; 1) = 0; 7; Mod((Number - Int(Number)) * 8; 1) = 0; 8; Mod((Number - Int(Number)) * 9; 1) = 0; 9; Mod((Number - Int(Number)) * 10; 1) = 0; 10; Mod((Number - Int(Number)) * 11; 1) = 0; 11; Mod((Number - Int(Number)) * 12; 1) = 0; 12; Mod((Number - Int(Number)) * 13; 1) = 0; 13; Mod((Number - Int(Number)) * 14; 1) = 0; 14; Mod((Number - Int(Number)) * 15; 1) = 0; 15; Mod((Number - Int(Number)) * 16; 1) = 0; 16; Mod((Number - Int(Number)) * 17; 1) = 0; 17; Mod((Number - Int(Number)) * 18; 1) = 0; 18; Mod((Number - Int(Number)) * 19; 1) = 0; 19; Mod((Number - Int(Number)) * 20; 1) = 0; 20; Mod((Number - Int(Number)) * 21; 1) = 0; 21; Mod((Number - Int(Number)) * 22; 1) = 0; 22; Mod((Number - Int(Number)) * 23; 1) = 0; 23; Mod((Number - Int(Number)) * 24; 1) = 0; 24; Mod((Number - Int(Number)) * 25; 1) = 0; 25; Mod((Number - Int(Number)) * 26; 1) = 0; 26; Mod((Number - Int(Number)) * 27; 1) = 0; 27; Mod((Number - Int(Number)) * 28; 1) = 0; 28; Mod((Number - Int(Number)) * 29; 1) = 0; 29; Mod((Number - Int(Number)) * 30; 1) = 0; 30; Mod((Number - Int(Number)) * 31; 1) = 0; 31; 32)) My problem is I don't know how to use it!! I have the field in the DB set up as a text field, but when I set it as a calculation field, and use the above and press ok, I get errors!! Can anyone help me to use this function? Jenni P.S. I can upload a sample of my DB if anyone can help me with this problem, please let me know. Quote Link to comment Share on other sites More sharing options...

Ender Posted October 4, 2006 Share Posted October 4, 2006 In the calc above, it's looking for a field called "Number", which would be a field of type Number too. The calc code will need to reside in a Calculation field with text result. If you still get errors, let us know what they are. Quote Link to comment Share on other sites More sharing options...

pasternik Posted October 4, 2006 Author Share Posted October 4, 2006 THANK YOU SO MUCH!!! I can't believe it was that simple!! But I have more questions, why does it have a 0 in front of the fraction? and is there any way to carry out the Int. to 6 places along with the decimal to 6 places? I am calculating the fractional interest of mineral owners and some of them own as an example 2598/697520 or it could be 1/764321 (just an example) how do I get filemaker to represent these fraction formats correctly? Thank you so much for your help!!! smiley-laughing Quote Link to comment Share on other sites More sharing options...

Ender Posted October 5, 2006 Share Posted October 5, 2006 It's important to remember that that calc above is an approximation of a fraction to within 1/32 of the actual number. Although greater accuracy could be achieved by extending the function out, the numbers you seem to be after would require this calc to be unreasonably large. A better solution is to use a recursive technique to arrive at the fraction within whatever degree of accuracy you desire. Fortunately, some smart people have already worked out the algorithm for this, and indeed have built FileMaker examples for us to examine. The techniques I like use Custom Functions. Custom Functions are defined by the developer and can be recursive. Unfortunately, you'd need the Advanced version of FileMaker pro to install them in your files. Here is a very detailed description of the algorithm and a Custom Function that converts a decimal to a fraction, from our good friend Mikhail: http://edoshin.skeletonkey.com/2006/01/fraction_approx.html And some other techniques are discussed in this recent thread: http://www.fmforums.com/forum/showtopic.php?tid/161229 Quote Link to comment Share on other sites More sharing options...

pasternik Posted October 7, 2006 Author Share Posted October 7, 2006 Ender, I wanted to thank you for all your help. I am new to Filemaker but very enthusiastic about what it can actually do. I did how ever come upon another forum, that gave me the exact calculation I was looking for.. Let ( [precision=10000; p0=Abs(Fraction_M);n0=Mod(p0;1); p1=1/Case(n0 ;n0;1);n1=Mod(p1;1); p2=1/Case(n1 and (p1/n1p3=1/Case(n2 and (p1*p2/n2p4=1/Case(n3 and (p1*p2*p3/n3p5=1/Case(n4 and (p1*p2*p3*p4/n4p6=1/Case(n5 and (p1*p2*p3*p4*p5/n5p7=1/Case(n6 and (p1*p2*p3*p4*p5*p6/n6d=Round(p1*p2*p3*p4*p5*p6*P7;0); n=Round(p0*d;0);nf=Round(n0*d;0); sgn=Choose(Sign(Fraction_M)+1;"-";"";""); whole=Div(n;d); fraction=Case(d=1;"";nf;nf&"/"&d;"")]; Trim(sgn&Case(not p0;0;whole;whole&" ";"")&fraction)) Precision was actually set to 100, originally but I set it to 1000 for testing purposes, and it works perfectly!! smiley_cool I work in the Oil & Gas Business, and I am setting up a very complex DB, calculating fractional interests of Mineral Owners, I have come along way, but I am sure I will have many more questions in the near future, hopefully you and others like you, will be there to help me along the way. smiley-laughing Thank You Again!! Jenni Quote Link to comment Share on other sites More sharing options...

## Recommended Posts

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.