SUM'ing within Portals

General support questions.
Posts: 13
Joined: Sun Jan 14, 2007 4:46 pm
Location: Hong Kong
PostPosted: Sun Jan 14, 2007 4:56 pm
Greetings,
First, I'ld like to say I love your calendar and contacts templates - You people do great work. I am fairly new to Filemaker but am learning fast with your free files.

Anyhow, on to my question (on my knees begging) I am a Private English Tutor, and book my students when they pay their tuition fee - usually for 1, 4 or even 10 lessons in advance. I wanted to put this info into your free-contacts database to keep track of who paid what and when. After beating my head against the wall for a while I finally managed to pull that off. However, then I decided to get ‘slick’ (maybe my head got too big) but I wanted to put a running total of hours, amount-paid, etc on each entry in the portal for my payments-table…
These TOTALs would show the total tuition (for example) paid so far, calculated by SUM'ing all the "THIS_PAYMENT"-amounts from all the portal rows - the problem is that it does not work that way.

What I do get is a SUM of the figures for that particular portal row - not a total of all portal rows - I hope that is clear enough - I don't know how else to say it. :?:

Any help would be appreciated. Thanks in advance. :D
Jon H. Atkinson
SeedCode Staff
SeedCode Staff
Posts: 2760
Joined: Thu Nov 20, 2003 11:01 am
PostPosted: Sun Jan 14, 2007 5:07 pm
Thanks for your kid words about our work!

I'm not completely sure I follow the question, but a field at the Contacts Level which calculates the total paid for that contact would be a calc like...

sum(ContactPayments::PaymentAmount)

...where "ContactPayments" is the table occurrence your portal is showing. If you're trying to get at something else, post some more details and we'll check it out.
John Sindelar
SeedCode
Posts: 13
Joined: Sun Jan 14, 2007 4:46 pm
Location: Hong Kong
PostPosted: Sun Jan 14, 2007 5:23 pm
Ok - Here is an example from my file - In each portal row should appear a payment amount, # of lessons purchased with that payment, # of hours, and a date for each lesson - this worked fine and I have that solution working...
but then I wanted to add a running total for "Tuition Paid" and "Lessons Purchased" that would show up in the portal rows also - because when I print a receipt, I would like it to show the last entered portal row and all it's contents - this way the client gets a receipt for what THAT payment paid for, as well as how much they have spent on tuition to date, and how many lessons their child has attended.
with being able to edit the portal rows, I can also (this way) easily move an appointment if a student needs to re-schedule either a single appointment or all of them covered in that payment.
I tried using SUM in the form you posted but that only gives me a SUM of the values in that portal row - not for all portal rows in that record.
Please forgive me - I am new at this and my terminology may be lacking.
Thanks, John, for the very speedy reply.
Jon H. Atkinson
Posts: 13
Joined: Sun Jan 14, 2007 4:46 pm
Location: Hong Kong
PostPosted: Sun Jan 14, 2007 5:24 pm
Sorry - I'm not sure how to post a picture to this forum or I could show you exactly what I have going.
Thanks again.
Jon H. Atkinson
SeedCode Staff
SeedCode Staff
Posts: 2760
Joined: Thu Nov 20, 2003 11:01 am
PostPosted: Sun Jan 14, 2007 5:28 pm
Cybr Knyf wrote:I tried using SUM in the form you posted but that only gives me a SUM of the values in that portal row - not for all portal rows in that record.


The sum calc that I posted would be a calc in the Contact's table.

Does that get you there?
John Sindelar
SeedCode
Posts: 13
Joined: Sun Jan 14, 2007 4:46 pm
Location: Hong Kong
PostPosted: Sun Jan 14, 2007 5:31 pm
Greets - again, let me try to be MORE clear about what I have working;
If a student has made 3 payments for 4, 4 & 5 lessons, then the last (top) portal row should have "5" lessons paid for, and a total of "13" lessons to date - is that better? :oP I feel so stoopid...

The second portal row should have "4" lessons paid for (this payment) and "8" lessons to date total - you follow that?

As it stands now - I have a sum function for "Payment Amount", "Lessons To Date", "Hours To Date", but they all seem to do the same exact thing - they only calc the values for a single portal row - so in the above example, the top portal row (last one entered) would have "5" lessons paid for and it indicates "5" Lessons To Date also...
Jon H. Atkinson
Posts: 13
Joined: Sun Jan 14, 2007 4:46 pm
Location: Hong Kong
PostPosted: Sun Jan 14, 2007 5:34 pm
Damn - so stoopid - No, the calc is NOT for the contacts table - I had to add a table - actually I duplicated the table for Activity - and added fields to it - you see? Then I just put it on a Tab on the bottom half of the layout - not one of the blank tabs near the top... - Since I duplicated the table, it kept the same relations, and I only had to change the names of the fields to reflect it was for payments.
Jon H. Atkinson
Posts: 13
Joined: Sun Jan 14, 2007 4:46 pm
Location: Hong Kong
PostPosted: Sun Jan 14, 2007 5:36 pm
Again - apologies - I was not calling you stupid - but, myself... It seems I don't learn as fast as when I was younger - when I was 15 I could have grasped this is about 3 minutes flat - or so it seems from my current vantage point. Thanks again for your help, John - you're the best.
Jon H. Atkinson
SeedCode Staff
SeedCode Staff
Posts: 2760
Joined: Thu Nov 20, 2003 11:01 am
PostPosted: Sun Jan 14, 2007 5:44 pm
No Worries Jon. So reading your last post again I can't tell if you're there yet or there are still some open questions. Just let me know.
John Sindelar
SeedCode
Posts: 13
Joined: Sun Jan 14, 2007 4:46 pm
Location: Hong Kong
PostPosted: Sun Jan 14, 2007 5:48 pm
I have re-named the duplicated table "Payment Activity" and it holds these fields;

_C_CommonOne
PaymentActivityID_kprime
PaymentActivityDate
PaymentActivityTime
PaymentActivityComments
PaymentActivityContactID_kf
Payment_Amount - (Number)
Payment_Lessons - (number)
Payment_Hours - (number)
Payment_Lesson_Date_01 - (Date)
Payment_Lesson_Date_02 - (Date)
Payment_Lesson_Date_03 - (Date)
Payment_Lesson_Date_04 - (Date)
Payment_Lesson_Date_05 - (Date)
Payment_Lesson_Date_06 - (Date)
Payment_Lesson_Date_07 - (Date)
Payment_Lesson_Date_08 - (Date)
Payment_Lesson_Date_09 - (Date)
Payment_Lesson_Date_10 - (Date)
Payment_TOTAL_TO_DATE - Sum ( Payment_Amount )
Payment_LESSONS_TO_DATE - Sum ( Payment_Lessons )
Payment_HOURS_TO_DATE - Sum ( Payment_Hours )
Payment_PAYMENTS_TO_DATE - Count ( Payment_Amount )

Does that make sense at all? I mean, it seems to work for everything except the "TO_DATE"-totals...
Jon H. Atkinson
SeedCode Staff
SeedCode Staff
Posts: 2760
Joined: Thu Nov 20, 2003 11:01 am
PostPosted: Sun Jan 14, 2007 5:55 pm
There is a check box on the summary fields for "running total" if you check that for your To Date summary fields then you should be fine. If that doesn't work feel free to email me the file and I'll take a look. You can email it to john at seedcode.com
John Sindelar
SeedCode
Posts: 13
Joined: Sun Jan 14, 2007 4:46 pm
Location: Hong Kong
PostPosted: Sun Jan 14, 2007 5:55 pm
So, still the same problem - it only seems to give me totals (TO_DATE) for that single portal row, instead of for all portal rows in that record.
maybe I will need to write a script of some kind that goes thru all record numbers in that table "Payment_Activity" and adds them? At this point I have no hair left and no idea how to coninue either. At least it keeps track of payments... haha - Thanks again.
Jon H. Atkinson
Posts: 13
Joined: Sun Jan 14, 2007 4:46 pm
Location: Hong Kong
PostPosted: Sun Jan 14, 2007 5:59 pm
Summary Fields? The Field Type for the "TO_DATE" fields are CALULATIONS - should I change that to SUMMARY and enter the calulation from there??
Jon H. Atkinson
Posts: 13
Joined: Sun Jan 14, 2007 4:46 pm
Location: Hong Kong
PostPosted: Sun Jan 14, 2007 6:01 pm
i'm using FM Pro 8.5 - and I'm too lame to find the "Running total" checkbox... LOL - people reading this will probably stay up late talking about how slim-witted I am... I'm trying, my friend.
Jon H. Atkinson
Posts: 13
Joined: Sun Jan 14, 2007 4:46 pm
Location: Hong Kong
PostPosted: Sun Jan 14, 2007 6:10 pm
Holy crap!!! That worked - but now I have one single last question, my new friend and father-figure - I have the portal sorted by date and time (descending) - This way it will show the last payment in the top row of the portal - However, it seems to run the calculation the other direction - is there a way to fix this?
Jon H. Atkinson
Next

Return to General Support

Who is online

Users browsing this forum: No registered users and 2 guests

(855) SEEDCODE
support@seedcode.com
Follow us: