Newbies Mark Gimpaya Posted May 10 Newbies Share Posted May 10 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? Link to comment Share on other sites More sharing options...
Søren Dyhr Posted May 10 Share Posted May 10 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 Link to comment Share on other sites More sharing options...
comment Posted May 10 Share Posted May 10 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 Link to comment Share on other sites More sharing options...
Newbies Mark Gimpaya Posted May 13 Author Newbies Share Posted May 13 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? Link to comment Share on other sites More sharing options...
Søren Dyhr Posted May 13 Share Posted May 13 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 1 Link to comment Share on other sites More sharing options...
Newbies Mark Gimpaya Posted May 13 Author Newbies Share Posted May 13 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? Link to comment Share on other sites More sharing options...
Søren Dyhr Posted May 13 Share Posted May 13 (edited) 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 1 Link to comment Share on other sites More sharing options...
comment Posted May 13 Share Posted May 13 (edited) 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 Link to comment Share on other sites More sharing options...
Søren Dyhr Posted May 13 Share Posted May 13 58 minutes ago, comment said: a repeating field (why not?) Next will be "I'm not using event triggers" 👿 --sd Link to comment Share on other sites More sharing options...
comment Posted May 13 Share Posted May 13 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). Link to comment Share on other sites More sharing options...
Newbies Mark Gimpaya Posted May 13 Author Newbies Share Posted May 13 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 Link to comment Share on other sites More sharing options...
Søren Dyhr Posted May 13 Share Posted May 13 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 Link to comment Share on other sites More sharing options...
comment Posted May 13 Share Posted May 13 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! Link to comment Share on other sites More sharing options...
Newbies Mark Gimpaya Posted May 14 Author Newbies Share Posted May 14 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?" Link to comment Share on other sites More sharing options...
Søren Dyhr Posted May 14 Share Posted May 14 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 1 Link to comment Share on other sites More sharing options...
Søren Dyhr Posted May 14 Share Posted May 14 I found an opportunity to make the same template with Michaels correction to the calc: sansrounds.fmp12 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now