Jump to content

Help with Select Sum


This topic is 2278 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

Hi all,

I'm relatively new to the ExecuteSQL statement but I want to learn more about it in order to create a dashboard of charts without having to worry about the underlying layout's base table.  I am a lab manager who oversees 4 funds each with different fiscal years.  I'd like to see real time totals for each fund.   I'm stuck with how to incorporate the various fiscal years to the total spend for each fund.  Do I need a statement for each fund?

I have tables for Orders, Funds and a Dashboard.

I was able to successfully write an SQL statement that allows  me to chart how much each lab user is spending each calendar year (via a User table).  But this has me stumped!

Let me know what information would be helpful for those out there who might help me!

Many thanks in advance!

 

 

Link to comment
Share on other sites

You can use greater than / less than symbols with the fiscal years (or BETWEEN but I've found that to be slow) and GROUP BY.

Not sure if that's what you're asking about.

Maybe this would help: seedcode.com/sql-subqueries-in-filemaker/

Also look for ExecuteSQL articles at filemakerhacks.com

Welcome to the forums!

Link to comment
Share on other sites

can you explain (in steps) how you would get the necessary data (to write down on a piece of paper for a report)? using finds/sorts even in this must be done multiple times. often clearly defining what's needed and how you might do "native" fmp to get values can help create any queries. If you are using relationships and/or filtered portals, please list those as well with relavent matches and calculations.

thnx

beverly

Link to comment
Share on other sites

  • Newbies
On 3/25/2016 at 4:25 AM, beverly said:

can you explain (in steps) how you would get the necessary data (to write down on a piece of paper for a report)? using finds/sorts even in this must be done multiple times. often clearly defining what's needed and how you might do "native" fmp to get values can help create any queries. If you are using relationships and/or filtered portals, please list those as well with relavent matches and calculations.

thnx

beverly

Sure!  I currently I do a find for each fund.

Find:  

Fund  -  ABC

Date Ordered - 6/1/2015...5/31/2016

Total - calculates the total of line items for ABC between June 1, 2015 and May 31, 2016 (the fiscal year for ABC) 

I've attached a file that shows my set up.  Log in as Guest for full access.

LAB_orders_Copy.fmp12

Link to comment
Share on other sites

This topic is 2278 days old. Please don't post here. Open a new topic instead.

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.