more fraction help needed

[agtjazz]

Greetings:

I am having a hard time with fractions, that are lookups and then calculations

User inputs in a field in contract table, then a decimal field and fraction field are lookup (based upon a related table named WTU conversion). I would like the Total_fraction to calculate... but it's not seeing the lookup fraction fields as fractions (I am guessing)

Also there is a salary formula.... that also pulls in the fraction that is not working properly.

Summer_salary = job:salary_base * contract:tb_fraction_summer* 4

I have attached a sample file of what I am trying to accomplish with text boxes of notes (on the contract layout).

Your assistance is really really appreciated

Thanks in advance

HelpwithFractions.zip

[comment]

I have no idea what your file is about, but here is a file showing two very simple methods to deal with fractions.

fract.fp7.zip

[agtjazz]

Thank you for your reply.... can I do---

2/16 + 1/16 and have the calculated field be 3/16

or do I have to convert to decimals and then how to I convert back to fractions?

thanks in advance

[comment]

You can do either - I thought we have covered this here.

[agtjazz]

I will keep working at it..... I can't seem to get it to work for what it needs to do.

So if field a is a lookup field and it is 1/16 and field b is a lookup field and it is 2/16.... how do I do a calculated field and come up with 3/16?

I know this may seem simple and over-discussed, but I am completely stumped. I tried all suggestions except the custom function from the previous post.

Thanks for your help.

[Ender]

Here's a consolidation of Mikhail's technique of evaluating the fractions (turning them into decimals), with my amendment for allowing whole numbers, plus John's technique of turning the decimal back into a fraction. If you have two fields Fraction1 and Fraction2, the sum will be:

FractionSum (calculation, text result) =

Let(

[

decimal = Evaluate(Substitute(Fraction1;" ";"+") & "+" & Substitute(Fraction2;" ";"+") );

remainder = Mod(decimal; 1);

whole = Int(decimal)

];

Case(whole>0 ; whole & " ") &

Round(remainder *

(Case(

Mod(remainder * 2; 1) = 0; 2;

Mod(remainder * 3; 1) = 0; 3;

Mod(remainder * 4; 1) = 0; 4;

Mod(remainder * 5; 1) = 0; 5;

Mod(remainder * 6; 1) = 0; 6;

Mod(remainder * 7; 1) = 0; 7;

Mod(remainder * 8; 1) = 0; 8;

Mod(remainder * 9; 1) = 0; 9;

Mod(remainder * 10; 1) = 0; 10;

Mod(remainder * 11; 1) = 0; 11;

Mod(remainder * 12; 1) = 0; 12;

Mod(remainder * 13; 1) = 0; 13;

Mod(remainder * 14; 1) = 0; 14;

Mod(remainder * 15; 1) = 0; 15;

Mod(remainder * 16; 1) = 0; 16;

Mod(remainder * 17; 1) = 0; 17;

Mod(remainder * 18; 1) = 0; 18;

Mod(remainder * 19; 1) = 0; 19;

Mod(remainder * 20; 1) = 0; 20;

Mod(remainder * 21; 1) = 0; 21;

Mod(remainder * 22; 1) = 0; 22;

Mod(remainder * 23; 1) = 0; 23;

Mod(remainder * 24; 1) = 0; 24;

Mod(remainder * 25; 1) = 0; 25;

Mod(remainder * 26; 1) = 0; 26;

Mod(remainder * 27; 1) = 0; 27;

Mod(remainder * 28; 1) = 0; 28;

Mod(remainder * 29; 1) = 0; 29;

Mod(remainder * 30; 1) = 0; 30;

Mod(remainder * 31; 1) = 0; 31; 32));0) & "/" &

(Case(Mod(remainder * 2; 1) = 0; 2;

Mod(remainder * 3; 1) = 0; 3;

Mod(remainder * 4; 1) = 0; 4;

Mod(remainder * 5; 1) = 0; 5;

Mod(remainder * 6; 1) = 0; 6;

Mod(remainder * 7; 1) = 0; 7;

Mod(remainder * 8; 1) = 0; 8;

Mod(remainder * 9; 1) = 0; 9;

Mod(remainder * 10; 1) = 0; 10;

Mod(remainder * 11; 1) = 0; 11;

Mod(remainder * 12; 1) = 0; 12;

Mod(remainder * 13; 1) = 0; 13;

Mod(remainder * 14; 1) = 0; 14;

Mod(remainder * 15; 1) = 0; 15;

Mod(remainder * 16; 1) = 0; 16;

Mod(remainder * 17; 1) = 0; 17;

Mod(remainder * 18; 1) = 0; 18;

Mod(remainder * 19; 1) = 0; 19;

Mod(remainder * 20; 1) = 0; 20;

Mod(remainder * 21; 1) = 0; 21;

Mod(remainder * 22; 1) = 0; 22;

Mod(remainder * 23; 1) = 0; 23;

Mod(remainder * 24; 1) = 0; 24;

Mod(remainder * 25; 1) = 0; 25;

Mod(remainder * 26; 1) = 0; 26;

Mod(remainder * 27; 1) = 0; 27;

Mod(remainder * 28; 1) = 0; 28;

Mod(remainder * 29; 1) = 0; 29;

Mod(remainder * 30; 1) = 0; 30;

Mod(remainder * 31; 1) = 0; 31; 32))

)

[comment]

I'm afraid you do need a custom function (or another method of recursive calculation) to do this efficiently. With a simple example like 1/16 + 2/16, where the denominator is 16 in both, you could simply add the numerators. But if the same problem is given as 1/16 + 1/8, you need to do:

(1*8 + 1*16) / 8*16 = 24/128

To get 3/16 from 24/128, you need to compute the greatest common divisor of 24 and 128. This requires a recursive calculation.

[agtjazz]

Comment & Ender,

Thank you for your patience and assistance and expertise and everything... I will continue to work on it... if I run into issues, I may post again today

Thank you again- yesterday was so frustrating to me, but today is a brand new day with my coffee cup full and I am hoping to comprehend with more clarity.

[agtjazz]

Just to clarify (my particular situation).... if the fraction is 4/16, I can't have it reduce..... and also if the fraction is 17/16, it should not read 1 1/16.... for some strange reason (even though we know it's the same thing---the University prefers it as such)

And the conversion chart that I am working with is by 60ths or 15ths..... ie 1/15, 7/60, 2/15 etc.

[agtjazz]

Uh Oh... Help... I think it's me--- this happened to me yesterday... it should work but it doesn't for me

I copied Ender's calculation for my sum of fraction field (name of field is tb_fraction_total) and changed fraction 1 & fraction 2 to fields in my database (tb_fraction_fall and tb_fraction_winter)

here is the calculation

Let(

[

decimal = Evaluate(Substitute(tb_fraction_fall;" ";"+") & "+" & Substitute(tb_fraction_winter;" ";"+") );

remainder = Mod(decimal; 1);

whole = Int(decimal)

];

Case(whole>0 ; whole & " ") &

Round(remainder *

(Case(

Mod(remainder * 2; 1) = 0; 2;

Mod(remainder * 3; 1) = 0; 3;

Mod(remainder * 4; 1) = 0; 4;

Mod(remainder * 5; 1) = 0; 5;

Mod(remainder * 6; 1) = 0; 6;

Mod(remainder * 7; 1) = 0; 7;

Mod(remainder * 8; 1) = 0; 8;

Mod(remainder * 9; 1) = 0; 9;

Mod(remainder * 10; 1) = 0; 10;

Mod(remainder * 11; 1) = 0; 11;

Mod(remainder * 12; 1) = 0; 12;

Mod(remainder * 13; 1) = 0; 13;

Mod(remainder * 14; 1) = 0; 14;

Mod(remainder * 15; 1) = 0; 15;

Mod(remainder * 16; 1) = 0; 16;

Mod(remainder * 17; 1) = 0; 17;

Mod(remainder * 18; 1) = 0; 18;

Mod(remainder * 19; 1) = 0; 19;

Mod(remainder * 20; 1) = 0; 20;

Mod(remainder * 21; 1) = 0; 21;

Mod(remainder * 22; 1) = 0; 22;

Mod(remainder * 23; 1) = 0; 23;

Mod(remainder * 24; 1) = 0; 24;

Mod(remainder * 25; 1) = 0; 25;

Mod(remainder * 26; 1) = 0; 26;

Mod(remainder * 27; 1) = 0; 27;

Mod(remainder * 28; 1) = 0; 28;

Mod(remainder * 29; 1) = 0; 29;

Mod(remainder * 30; 1) = 0; 30;

Mod(remainder * 31; 1) = 0; 31; 32));0) & "/" &

(Case(Mod(remainder * 2; 1) = 0; 2;

Mod(remainder * 3; 1) = 0; 3;

Mod(remainder * 4; 1) = 0; 4;

Mod(remainder * 5; 1) = 0; 5;

Mod(remainder * 6; 1) = 0; 6;

Mod(remainder * 7; 1) = 0; 7;

Mod(remainder * 8; 1) = 0; 8;

Mod(remainder * 9; 1) = 0; 9;

Mod(remainder * 10; 1) = 0; 10;

Mod(remainder * 11; 1) = 0; 11;

Mod(remainder * 12; 1) = 0; 12;

Mod(remainder * 13; 1) = 0; 13;

Mod(remainder * 14; 1) = 0; 14;

Mod(remainder * 15; 1) = 0; 15;

Mod(remainder * 16; 1) = 0; 16;

Mod(remainder * 17; 1) = 0; 17;

Mod(remainder * 18; 1) = 0; 18;

Mod(remainder * 19; 1) = 0; 19;

Mod(remainder * 20; 1) = 0; 20;

Mod(remainder * 21; 1) = 0; 21;

Mod(remainder * 22; 1) = 0; 22;

Mod(remainder * 23; 1) = 0; 23;

Mod(remainder * 24; 1) = 0; 24;

Mod(remainder * 25; 1) = 0; 25;

Mod(remainder * 26; 1) = 0; 26;

Mod(remainder * 27; 1) = 0; 27;

Mod(remainder * 28; 1) = 0; 28;

Mod(remainder * 29; 1) = 0; 29;

Mod(remainder * 30; 1) = 0; 30;

Mod(remainder * 31; 1) = 0; 31; 32))

)

I type in 2 for Fall_wtu (the lookup fields insert 2/15 for tb_fraction_fall) and 2 for Winter_WTU (the lookup fields insert 2/15 for tb_fraction_winter).... the total comes up to 9/32 (but it should be 4/15)

I am lost and really appreciate your help.

[comment]

I am really confused regarding what are you really trying to accomplish here. There seem to be two issues here, not necessarily related. One is the issue of data entry, the other is a calculation.

If users are expected to enter decimal data, I don't understand why you need to convert it to fractions BEFORE using it in a calculation. Even less clear is the lookup issue. I think a computer is capable of dividing two numbers without consulting a lookup table. Even a human has been known to do so ocassionally.

The calculation is another issue. If you know that you always want the result presented as x/15 or x/60, then things can be much simpler.

Please restate your problem in these terms:

1. My overall purpose is ...

2. I want user to input data in the form of ...

3. I want to perform this calculation on the input ...

4. I want to present the output in the form of ....

Be very specific. An example is not enough: state the rule by which the example is produced. It also might help knowing what WTU means (see point 1 above).

[agtjazz]

I am sorry for the confusion.

1- My overall purpose is to automate information in the contract table. There are 2 areas of help that I am having problems with (both are similar because they deal with calculated fields and fractions) Currently the user is inputting all the information and doing the math on a calculator and then inputting data into the fields.

2- The user inputs into a field (named WTU-"Weighted Teaching Units") and then 3 other fields are populated based on the value entered

IE

Summer

WTU 2 (user inputs)

Decimal lookup field

Fraction lookup field

Salary calculated field

The totals are calculated fields too

So the user inputs 2 Summer WTU... and the database lookups on a related table (WTU conversion) to fill out the decimal equivalent and the fraction equivalent to the 2 WTU.

3a- The first calculation I want to perform is the sum of the Looked up fraction fields (so summer fraction , fall fraction, winter fraction, spring fraction = total fraction). When I use formula I pasted earlier today, the math is not correct. I had 2/15 in summer fraction and 2/15 in fall fraction and the calculated total said 9/32.

3B- The second calculation is for salary. The summer salary should be base salary*summer fraction * 4 =summer salary (with some degrees of rounding- but I can do that. It's the fractions that really are blowing my mind)

4a- For the fraction-sum- I want to present the output raw fractions (not reduced and not converted to whole numbers)

I actually want it to stay 16/15 (not 1 1/15) and 3/15 should not go to 1/5. The contracts are based on 15ths or 60ths and have to read as such.

4b- For the salary for each quarter, I want it as a number formatted with currency information.

Thank you again for your time and advice

[Ender]

Still pretty vague. What are the rules? Do the fractions always have the same denominators? If not, how is it supposed to be evaluated. If so, then just parse the fractions, add the numerators, and append the denominator.

BTW: That calc I provided earlier approximates a fraction from a decimal. But since some fractions result in repeating decimals (like .333333...), their sum ends up being a number that doesn't divide evenly with the approximation algorithm. Unless that approximation algorithm can be tweeked, the alternative for turning a decimal into a fraction is to go with comment's suggestion, using a recursive solution to find the GCD.

[agtjazz]

The fraction should always have either 15 or 60 as a denominator

1/60 2/60 3/60 5/60 6/60 7/60 9/60 10/60 11/60

1/15 2/15 3/15 4/15 5/15 6/15 7/15 8/15

9/15 10/15 11/15 12/15 13/15 13/60 14/15 14/60 15/15 15/60 17/60 18/60 19/60

21/60 22/60 23/60 25/60 26/60 27/60 29/60

30/60 31/60 33/60 34/60 35/60 37/60 38/60

39/60 41/60 42/60 43/60 45/60 46/60 47/60

49/60 50/60 51/60 53/60 54/60 55/60 57/60

58/60 59/60

I will try Comment's suggestion.... hopefully that'll work and my last post will be just saying it works!!!

Thanks I really appreciate all of the great folks on this forum.

[comment]

I am not sure I follow this entirely, but take a look at the attached file - perhaps it's a step in the right direction?

fract2.fp7.zip

[agtjazz]

:thankyou:

I am sooo very very grateful. Thank you for your patience and it works perfectly

[comment]

Good. It just occured to me that you could eliminate quite a few fields here by using repeating fields.

fractR.fp7.zip

[agtjazz]

I took a look at the repeating fields sample and will probably go that route..... .thank you thank you thank you!

I am amazed at how helpful you guys are... and hopefully someday I will be able to return the favor !

Thanks bunches!!!