AnsweredAssumed Answered

Basic SQL - aggregateing (sum) fields

Question asked by MaxEh on Jan 1, 2017
Latest reply on Jan 6, 2017 by fmpdude

Let me preface by asking to be excused for the long windedness.  (FMP 14A on a Mac 10.11)

 

I am trying to implement some of my first SQL calculations. I have 3 tables Projects, Invoices and Budgets. Each Project is assigned to one Budget; each Project can have multiple Invoices, currently invoices are only assigned to one Budget, there are multiple Budgets.

 

Budgets are assigned each year, when I started I was told there would only ever be 3 budgets - well we all know that story. Currently at 9. the Budget key is based on a year e.g 1516 or 1617 etc plus a Budget bucket for description sake A, B, C etc. so the key looks like 1516A or 1516B or 1617A etc..

 

Invoices are added in Projects through a portal which sets both a fk_ProjectID and fk_Budget (fkBYT)

 

What I need to do is track all invoices (aggregate sum) based on a Budget year bucket e.g. all 1516 Invoices in Budget bucket A or 1516A  to use in reports so Project coordinators/planners and financial coordinators can see how much is remaining in a given budget bucket as the year progresses.

 

I have implemented something similar in my Invoices table using summary and getsummarry calculations but those are for relatively static needs ( originally 3 items currently 5 and this hasn't changed for over 10 years). The number and type of  Budget buckets will tend to change by year and by how the client wants data reported so I need something less onerous to implement.

 

I thought I could do this in my Budgets table where I create one record for each year and bucket combination (that was the 3 and now 9   and that I could use ExecuteSQL to gather dynamically a total into a YTD field.

 

This is what I have come up with but obviously I am not understanding the syntax (my forehead is considerably flatter since the holidays started).  I have read many of the references found in this form to help me get started but I am missing something (likely obvious). [I did create a successful Select Distinct query to trim an over bloated drop down list which was a timesaver]

 

Basic relationships are:

 

Budgets::pkBYT --< Inv|BYT::fkBYT (BYT is Budget Year Type 1516A or B or C etc)

 

Budgets::pkBYT --< Proj|BYT::fkBYT

 

Projects:pk_ProjectID --< Invoices::fk_ProjectID

 

A Project ID is based on the year and if a second project is required a B, C, D etc is added to the ID and can then be assigned a different budget buck if required. Part of the project ID is a building number so an id would look like 1516-01A-1055 for the first project 1516-01B-1055 for a second project at the same building that could be assigned to the same or different budget. The same building could have another project 1516-35A-1055 which could also be assigned into the same or different budget bucket. The 01 is based on standard codes for the type of work the project entails. This system works very well. My problem is the budget allocation.

 

The following is set up in my Budgets table in a calculation field, unstored. Obviously I am not understanding the syntax (my forehead is considerably flatter since the holidays started).  I have read many of the references found in this form to help me get started but I am missing something (as mentioned likely obvious). I did create a successful Select Distinct query to trim an over bloated drop down list which was a timesaver. (Power to the user, headache to the developer!!)

 

ExecuteSQL (

 

"SELECT SUM Inv|BYT::Invoice Amount

FROM  Inv|BYT

WHERE Inv|BYT::fkBYT = pkBYT"

 

; "" ; "")

 

I have tried adding GROUP BY but all I end up with is the same "?", I have also tried using the Invoices table instead of Inv|BYT which if I understood from my reading should not matter (or is that where I am obviously going wrong?) Am I missing some "" or ; ...  I looked at the examples and saw use of the Let function - haven't gone down that path yet.

 

Some of the Budget bucket names may have spaces in them e.g. 1516Some Bucket if that could be an issue.

 

Am I on the right track or is there another way to do this? ( and I know with FMP there is always another way!!)

 

Help!

 

Max

Outcomes