Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Calculating with Filtered Portals Summaries


alexh90
 Share

Recommended Posts

Dear reader,

 

Currently I have an issue with my Filemaker Database. So far I have built a simple database for my photography business. I have created several tables, such as Customers, Invoices, Expenses, Product en Overview.

In the Overview Layout, there are currently two portals. One displaying records from the Invoices table and one from the Expenses table. I can sort both by year. In the bottom of the layout there are two Summary fields (both in a single line portal), that display the total of my Invoices and Expenses and respond to the sorting by year. When sorted, these summary fields differ from the value in the database (since only a single year is visible).

 

My problem is that I'd like to do some calculations with these summary values (compare them), but since these (sorted) values are only available in the layout and not in the database, I can't. Anyone got a solution?

 

I uploaded my Database here, since I find it hard to explain it correctly.

 

Can anyone help me out?

Thanks in advance.

Link to comment
Share on other sites

Confused ... Do you want these portals to compare values to previous/future years or just a total of all years?

 

First up ... Sorry my Non English programming isnt what it used to be. That said. I think you are going about

this differently than I would have.

 

Here what I would do. Your fields SortJaar and DateJaar should be the relationship (Not sure if your are trying to separate by Customer. If so include it with the DataJaar.

 

Next I would have an auto entry that takes DateJaar and subtracts on year from it. tho be used in another relationship for previous year.

 

In your Overzicht table you should have sum field for the relationship . I.E. Sum(Facturen::BedragTotaal)

 

Rather than showing the summary field in another copy of the Facturen Portal, you will place this field outside the portal, Likewise with any other field you add to the Overzicht table.

 

Now to get the total for the previous year a new relationship using DateJaarMinus1 rather than DateJaar. This will get the record from last year.

 

Finally a field that compares current to previous giving you a plus minus comparison.

 

Hope this makes sense. Not to good at German or Dutch behind the scenes. Maybe Maartin can add to it.

Link to comment
Share on other sites

Sorry for the confusion. I translated the database to English and uploaded it again here. Let me rephrase my problem:

I'd like to have an overview of my Invoices and the Expenses I make for each fiscal year (2011, 2012 etc.) and for each fiscal quarter (2011-1, 2011-2, etc.).

In the Overview layout I created one tab for the overview by year and one for the overview by quarter. Through a dropdownlist I'd like to be able to select a year/quarter. On selecting this, I'd like to have an overview of the invoices and expenses I made during that period, with a total of both. Finally I'd like to see the difference between the total of my invoices and the total of my expenses (so I can easily read out what I have to fill in for my tax return).

 

I thought that it would be easy to do this through portals to both tables, but I'm very new to Filemaker. Perhaps it is easier to do this in a report, but I don't know whether is possible to have multiple tables inside one report and to compare values.

 

Thanks again for helping me out!

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share



×
×
  • Create New...

Important Information

Terms of Use