totals on a filtered portal

[tcoyle777]

I am trying to figure out how to have a calculation field based on a filtered portal. I have the portal being filtered my month and billing status. I want to sum all the invoices that are either billed,not billed, late, or collection. The user selects the status and the month and the portal brings up all the related records. Now I just need those added up for a grand total.

any ideas?

 

[JeanW]

Take as base the relationship of the portal to make your calculation.

 

[tcoyle777]

Not sure what you mean by that. My goal would be for the calc field to change depending on what the user selects. Is this even possible?

 

[JeanW]

It's the relationship where the portal is based that is important.

If you have Field01 in your portal, and your portal is based on RelationA, your calc could be:

RelationA::Field01.

Your portal/relationship will act as a foundset, so the calc will only act on the foundset, foundset = records in the portal.

HTH

 

[tcoyle777]

Thanks,

Its amazing what a weekend away from it will do for you. I figured my error in the code.

thanks

 

[dodjah]

Hi tcoyle777...I have exactly the same problem.Didi you ever get this sorted.
Could really do with some advice.

Thanks

 

[tcoyle777]

dodjah,

Yes I did. I was being stupid and had it calculating from the wrong table occurance. You need to set up a calc field in the table you have the portal set up on. Then set your calc using the information from the related table. for example mine was trying to filter a portal based on if a project was billed, Not Billed, paid,etc. So here is the equation I used.

Case ( Billing Status = "Not Billed" ; Sum(Projects 4::Total Studio Bill) ; Billing Status = "Billed" ; Sum(Projects 4::Total Studio Bill) ; Billing Status = "Paid";Sum(Projects 4::Total Studio Bill);Billing Status = "Late";Sum(Projects 4::Total Studio Bill);Billing Status = "Collection";Sum(Projects 4::Total Studio Bill))

worked fine once I used the correct related table.

 

[dodjah]

Thanks tcoyle777 but I still cant seem to get it to work.

I have a drop down menu that changes status from paid,invocied,cancelled etc

What I would like is as i switch status the I get a total for each companys(customers) cancelled ,paid etc

The portal is shows related tables from table INVOICES and the layout set-up is an instance of the COMPANY table named company_invstatus.

Not sure what table i should be writting your calculation field into.

Would really appreciate any help...
Thanks

 

[tcoyle777]

your calc field needs to be in the table you have your portal on. It needs to reference the table occurance. Your relationship for the two tables needs to be on the key field that you setup. I assume it would be "Status".

 

[dodjah]

Hi Tcoyle777

My table is “COMPANY” and im showing records in the layout set up from COMPANY_invStatus ( a table instance/ occurrence of COMPANY”)

The portal is showing records from table INVOICES.

So I take it your saying put the calc field in INVOICES ??

Status is currently not the KEY field the relationship between COMPANY_invStatus and INVOICES…??

I will try again making Status a key field in both tables.
Does your one adjust the calculation based on the status selected.

Thanks again

 

[tcoyle777]

Yes my portal is filtered by month and status. It then calculates all the invoices that meets those requirements.
so say I have July and Billed. It totals up all my July invoices that have been billed. Is your portal showing the correct data?

 

[dodjah]

Cant work out wot Im doing wrong.
My portal is set up to show all my invoiced clients.
The portal shows related INVOICES and the table layout comes from CUSTOMERS

I have a filter that seperates them by by paid,invoiced,open and cancelled.
This allows me to look at any client and see the whats jobs are "open" ,"invoiced" etc.
Althrough the filter works the calculation is not filtered.
I get a total of all jobs to that client regardless of status.

What I want is to show the correct amount under each status.

Are we allowed to send screen shots etc on this forum ??

ps...thanks for all your help..as you can tell Im a newbie just trying to get my head round filemaker

Dodjah

 

[tcoyle777]

if its allowed you could send me a copy of the file and I could take a look at. If you portal is correct and you have the two tables linked together by the key field which should be status or something like that. Then that should be right. Your calculation should be a case statement with the statuses as your equations. like the one posted earlier. The calculation say its just called Total should be on the Customers table I am assuming. In my "Projects" table I have a field that gets the status from my invoices or what we call Work Orders. This is the field - Billing Status_calc its a text field that is indexed,Auto-enter Cal replaces existing value,evaluate always, Always Validate. Calculation for this field is this
Case ( Work_Order::Billing Status = "Billed" ; "Billed";
Work_Order::Billing Status = "Paid";"Paid";
Work_Order::Billing Status="Late";"Late";
Work_Order::Billing Status = "Collection";"Collection";
"Not Billed"
)
Then in my other table that I set up called Accounting Home page. Which is just a bunch of portals set up to look at any invoices or other expenses I have the Total calc field that I have mentioned before. So when a invoice is changed it changed the status on the projects page. which then triggers the case statement for the Total calc field on my accounting home page.

I am building my first Database with FM so I am fairly new too, so I understand where you are coming from.