Calculation in setting a Planfor a System

[Mark Gimpaya]

I have four categories in my Year Level portal in FileMaker: Enrollment Fee, Miscellaneous Fee, Tuition Fee, and Other Fee. Each one of them has a dedicated amount: Enrollment Fee has an amount of 695, Miscellaneous Fee has 2,194, Tuition Fee has 11,349.20, and Other Fee has 1,870.

What I want to happen is to create a plan where the Total Fee of the four categories, which is 16,108.20, will be subtracted from the Entrance Fee with an amount of 9,808.20, and from the Scholarship with a dedicated amount as well. Then, the total will be divided by 9. 

What calculation or function in FileMaker should I use to achieve this process?

[Søren Dyhr]

14 hours ago, Mark Gimpaya said:

What calculation or function in FileMaker should I use to achieve this process?

Portalize in one liners - each with different filters attached, by showing only the related summary field through:

https://filemakerhacks.com/2012/09/13/aggregates-in-filtered-portals/

--sd

[comment]

I am afraid your question is not clear enough.

You say you have a portal showing multiple fees, but you don't tell us what is the parent table or where are the other amounts that you mention ("Entrance Fee with an amount of 9,808.20, and from the Scholarship with a dedicated amount") coming from.

You probably just need a calculation field in the parent table that does something like:

( EntranceFee + Scholarship - Sum ( Fees::Amount ) ) / 9

 

[Mark Gimpaya]

Let me revise and clarify my situation. 

Total School Fees = 16,108.20

Enrollment Fee      =  695

Scholarship           =  9,050   

              Total        = 6,363.2

The total will be divided by 9 

6,363.2 / 9 = 707.02

SEPTEMBER  = 707.02

OCTOBER       = 707.02

NOVEMBER    = 707.02

DECEMBER     =  707.02

JANUARY       = 707.02

FEBRUARY     = 707.02

MARCH        = 707.02

APRIL        =  707.02

MAY          = 707.02

The amount of month October to May should be exact 700 only. So, what i want to happen is the excess amount from month of October to May is will always be added to the first month, which is September.

 

What calculation or function in FileMaker should I use to achieve this process?

[Søren Dyhr]

Define a repeating calc field with 9 repeatings, containing this:

Let(
   [
      tsf = Extend( TotalSchoolFees );
      ef = Extend( EnrolmentFee );
      ss = Extend( Scholarship );
      rg = Round( Div( tsf - ef - ss; 9 ); -2 )
   ];
   Case(
      Get( CalculationRepetitionNumber ) - 1;
      rg;
      tsf - 8 * rg - ef - ss
   )
)

Imports from internal tables allows then to split each repeater out into individual records, in a related table, but today with the fm20 "Layout calculation" would I perhaps use the portal row number instead of  Get( CalculationRepetitionNumber ) and then get rid of the Extend( shenanigans. Then would only data in the portalized just have one single boolean field, with a checkmark....

--sd

 

[Mark Gimpaya]

48 minutes ago, Søren Dyhr said:

Define a repeating calc field with 9 repeatings, containing this:

Let(
   [
      tsf = Extend( TotalSchoolFees );
      ef = Extend( EnrolmentFee );
      ss = Extend( Scholarship );
      rg = Round( Div( tsf - ef - ss; 9 ); -2 )
   ];
   Case(
      Get( CalculationRepetitionNumber ) - 1;
      rg;
      tsf - 8 * rg - ef - ss
   )
)

Imports from internal tables allows then to split each repeater out into individual records, in a related table, but today with the fm20 "Layout calculation" would I perhaps use the portal row number instead of  Get( CalculationRepetitionNumber ) and then get rid of the Extend( shenanigans. Then would only data in the portalized just have one single boolean field, with a checkmark....

--sd

 

Apologies, but I'm not using repeating fields. Are there any other functions that can be used instead of Extend?

[Søren Dyhr]

Well, see what you can get from the temple I used for getting my thoughts straightened:

--sd

Untitled.fmp12

Edited May 13 by Søren Dyhr

[comment]

1 hour ago, Mark Gimpaya said:

Are there any other functions that can be used

Well, the calculation itself is quite simple (as Søren has shown)*. The only problem is that you want to get 9 separate results (or at least 2 - one for the first month and one for the remaining eight) and you are not telling us what do you want to use to hold these results: separate fields, a repeating field (why not?), a single field holding a list of all amounts, or separate records in a related table (I believe that lists all options but maybe not).

---
(*) That is assuming the logic is that the "regular" payments should be rounded to the nearest hundred. Or perhaps they should always be equal to exactly 700? Then it would be even simpler.

 

Edited May 13 by comment

[Søren Dyhr]

58 minutes ago, comment said:

a repeating field (why not?)

Next will be "I'm not using event triggers" 👿

--sd

[comment]

2 hours ago, Søren Dyhr said:

rg = Round( Div( tsf - ef - ss; 9 ); -2 )

Just note that when you are rounding up or down, the excess amount can be negative. For example, if the total is 5,850 then the regular payment will be:

5850 / 9 = 650 ==> 700

and the excess amount is:

5850 - 9 * 700 = -450

With smaller total amounts this could lead to an absurd result where the first payment itself is negative (first you get $100 from us, then you pay us 8 payments of $100 each, for a total of $700).

 

[Mark Gimpaya]

2 hours ago, Søren Dyhr said:

Well, see what you can get from the temple I used for getting my thoughts straightened:

--sd

Untitled.fmp12 292 kB · 5 downloads

Thanks buddy. We'll try that later

[Søren Dyhr]

1 hour ago, comment said:

With smaller total amounts this could lead to an absurd

Indeed, some sort of validation of the entry is required to prevent such😵‍💫

 

—sd

[comment]

27 minutes ago, Søren Dyhr said:

some sort of validation of the entry is required

Uhm, no. This is not a problem of invalid entry. Those are the business rules that we are given. Or more correctly, the rules that you have surmised from the single example we were given.

My own interpretation would be that the base payment needs to be rounded down to the nearest hundred or ten (the example allows for both options). Even then the results can be somewhat weird, esp. if it is hundred we are rounding to. For example, if the total is 5,350 then the regular payment will be:

100 * Div ( total ; 900 ) = 500

and the excess is: 

Mod ( total ; 900 ) = 850

so the first payment would be $1,350 - more than twice the regular payment!

 

[Mark Gimpaya]

On 5/13/2024 at 11:29 AM, Søren Dyhr said:

Well, see what you can get from the temple I used for getting my thoughts straightened:

--sd

Untitled.fmp12 292 kB · 6 downloads

Is there an alternative way that does not use repeating fields but still functions the same as the example you provided?

 

2 minutes ago, Mark Gimpaya said:

Is there an alternative way that does not use repeating fields but still functions the same as the example you provided?

 

"Because we have two different kinds of plans in our system. Plan A is used for full payment, which means the calculation is done as a whole amount. Meanwhile, Plan B is divided into 9 months, but we don't use repeating fields to split the payment into 9. Is there an alternative way that does not use repeating fields but still functions the same as the example you provided?"

[Søren Dyhr]

37 minutes ago, Mark Gimpaya said:

Is there an alternative way that does not use repeating fields but still functions the same as the example you provided?

Like this - although I think Michael is right about his calc's

sansrepeaters.fmp12

[Søren Dyhr]

I found an opportunity to make the same template with Michaels correction to the calc:

sansrounds.fmp12

[Mark Gimpaya]

There is a change in the process that we need to achieve.
Please help me I'm struggling with the process I want to achieve.
Here is the process for setting the amounts in my Plan B:

I have a field for the Enrolment Fee with an amount of 715, which I will subtract from the Total School Fee of 21,605.  Which is the total difference is 20,890 then it will be divided by 8, resulting in 2,611.25. Let's assume this amount is 2,620. Now, this 2,620 needs to be set for the month of September. 
Like this:

The next step, to set the amount for the month of October, is to subtract the amount of September ( 2,620 ) from the Miscellaneous Fee that has an amount of 3,400. So the total difference is 780. So here's what i want to happen,  I want to set an amount of 2,610 for the months of October to May. Therefore, I need to subtract an amount from the Tuition Fee that has an amount of 16,440 and add it to the 780 to make it 2,610. 
Then repeat the process of subtracting the amount from the Tuition Fee until the amount of 2,610 is set for each month from October to May.

What calculation or script that I need to get this process perfectly? Thanks in advance.

Edited May 24 by Mark Gimpaya

[Søren Dyhr]

Take a look at this funktion:

https://help.claris.com/en/pro-help/content/choose.html?cshid=HID_Calc_Choose

...then is it up to you if the sworn oath of not using repeating fields, should be broken - You could if unwillingness strikes, use a Cartesian portal to usher out each line, if need be?

--sd