1 2 Previous Next 24 Replies Latest reply on Jun 9, 2014 6:32 PM by PrisonProfessor

    Portal Calculations

    PrisonProfessor

      Title

      Portal Calculations

      Post

           I have two tables Donators & Donations. There are multiple donations to a donator.

           I'd like to calculate the following based on each donator and store result in donator table

           Fields are as follows

           Donator::SumTotal, Donator::SumAvg, Donator::DonationTotal, Donator::LastDonation

           Donations:DateDonation, Donations::AmtDonation

           Donations is a portal within the Donators Layout

           Total Number Donations

           Smu total of all donations

           Average donation

           Date of last donation

        • 1. Re: Portal Calculations
          davidanders

               There is a free open template about donations from RCC, it may give some hints.

          http://www.filemakerdonations.com/
                

          • 2. Re: Portal Calculations
            PrisonProfessor

                 Thanks, I've downloaded and have briefly looked through some of the fields that may be doing the calculations I'm looking for but seems like there is a lot to review, but will give it a try

            • 3. Re: Portal Calculations
              PrisonProfessor

                   I must be getting wise as I've figured out, that could be dangerous

                   Total Number Donations

                   Sum total of all donations

                   Avg Donation

                   Last donation date is still an unknown and I'm seeing it be nice to know the YTD Number donations and Sum all YTD Donations

                    
              • 4. Re: Portal Calculations
                PrisonProfessor

                     Stuck on getting YTD calculations

                     Using

                     sum (Donations::AmtDonation (Year (Donations::DateDonation = (year (Donations::gCurrentDate))))

                     Error Msg

                     A number, text constant, field name or "(" is expected here

                     sum (Donations::AmtDonation (Year (Donations::DateDonation = (year (Donations::gCurrentDate))))

                     I'm thinking I'm way off but hopefully close enough some can prove correct syntax 

                     Thanks

                      
                • 5. Re: Portal Calculations
                  mleering
                  If what you're trying to do is to always show the current YTD's donations, then here's one way you can accomplish that. 1) create a new calc field in the donator table. It will return a number result, should be unstored, and its formula can be Year(Get(CurrentDate)). 2)Create a calculation field in the donation table. It will also have a number result, but its formula will be Year (Donations::DateDonation). 3) create a new relationship between donators and donations using the two calcs we just created as your match fields. 4) create one new calc in the donator table. It will need to evaluate through our new relationship, and should be something like sum (NewDonations::AmtDonation)
                  • 6. Re: Portal Calculations
                    PrisonProfessor

                         Thanks for replying Matthew;

                         I believe I've correctly did as you suggested but apparently not as I'm not getting intended results

                         I've tried my best to reflect what I did for your 4 suggestions below. In the relationship I tried a single occurrence and a second occurrence of the Donators table to avoid possible conflicts of the relationship _fkDonatorId >>-----< _pkDonatorId but it appears not to make a difference.

                         My results in Donator::cYTDDonationTotal appears to be the latest entry of Donation::AmtDonation

                         To confirm the results of fields 1 & 2 I placed an object for each on the Donator layout and both are 2014

                         My guess is I'm doing something wrong on step 3 and/or 4 

                         Thanks again for your help

                         1 Field = Donators::cDonationYear
                            Type = Calculation
                            Calculation is Donators::cDonationYear = Year(Get (CurrentDate))
                            Calculation Result is Number
                            Do Not evaluate if all reference empty = Unchecked

                         2 Field = Donations::cDonationYear
                            Type = Calculation
                            Calculation is Donations::cDonationYear = (Year (DateDonation)
                            Calculation Result is Number
                            Do Not evaluate if all reference empty = Unchecked

                         3 Donations::cDonationYear >>————< Donators::cDoantionYear
                            Donations::cDonationYear = Donators::cDonationYear
                            Donations Allow creation of records
                            Donators Allow creation of records

                         4 Field = Donator::cYTDDonationTotal
                            Type = Number
                            Calculated Value is Sum(Donations::AmtDonation)
                            Do Not replace & Do not evalaute

                    • 7. Re: Portal Calculations
                      mleering

                           No problem PrisonProfessor.

                           Looks like you did everything right, but I Ieft something out (accidentally).

                           In the relationship you created, you're also going to want a second predicate defined.
                           The 2nd predicate should be _fkDonatorId >>-----< _pkDonatorId.

                            

                           Hopefully this gives you the results you're looking for!

                            

                      • 8. Re: Portal Calculations
                        PrisonProfessor

                             UGH; Your explanation seems clear enough but I must be missing something or have a misconfiguration 

                             I added a second 'join' what I think you referred to as a second predicate _fkDonatorId >>-------< _pkDonatorId but I'm getting same results.

                             I need to prepare for a lesson I'm teaching tomorrow and need to put this on the back burner but I'd greatly appreciate once I can put it back on front burner if you would still be willing to help me resolve.

                             Hard for me to believe that at one time I was much more sharp when it came to things computing but the statement once's mind goes south once over 50 is proving true for me I guess sad

                             Thanks

                        • 9. Re: Portal Calculations
                          mleering

                               Okay.

                               I can certainly help out tomorrow.
                               Here are a couple of cleanup items for you in the interim.
                               I don't suspect that they're going to solve the problem for you, but they're important none-the-less.

                                 
                          •           For Item#1, please make sure the calculation is unstored.
                          •      
                          •           For Item#3, you won't need "Allow Creation" turned on --> I'd also like to clarify that you created a new tableOccurrence and relationship here instead of repurposing the one you already had for your portals
                          •      
                          •           For Item#4, it looks as if you might have used a number field with an auto-enter calculation instead of a calculation field

                               If after making these adjustments, you're still troubled, then maybe you could post some screenshots of how it's set up?
                               Not sure if this site lets you upload multiple images, but if not, then you could always use http://snag.gy or something similar

                          • 10. Re: Portal Calculations
                            philmodjunk
                                 

                                      Not sure if this site lets you upload multiple images,

                                 You can always post more than one reply here and upload a different image with each one.

                            • 11. Re: Portal Calculations
                              PrisonProfessor

                                   I've taken several screen shots. I won't upload all of them initially, hopefully the issue can be resolved with the first series.

                                   The first one is a screen shot of the relationships 

                              • 12. Re: Portal Calculations
                                PrisonProfessor

                                     Here is a screen shot of the field Donations::cDonationYear and the associated calculation

                                • 13. Re: Portal Calculations
                                  PrisonProfessor

                                       Screen shot of Donators::cDonationYear and associated calculation

                                  • 14. Re: Portal Calculations
                                    PrisonProfessor

                                         Last one for now is screen shot Donators::cYTDDonationTotal the target  field

                                         As FYI all three fields are not stored

                                    1 2 Previous Next