There are three tables:
Services:materialName::ServiceTransactionsLineItems:Material_FK and Services:MaterialYear::ServiceTransactionLineitems:TransactionLineItemYear
(Services may change from year to year, and within one transaction you might line items for both current and future years.)
:Services:MaterialBudgetAllocation is a user entered number.
What I am hoping to do is: Get the yearly total amounts of each material (Material_FK) and subtract that from Services:MaterialBudgetAllocation to get a remaining amount.
I'm trying to find the Client that has the most Job time over the last 30 days, divided by the number of seats that they have. The tables are:
Client_IDF, Job_time, Job_Date
The best I've managed so far is to find the distinct Client_IDF from all Jobs:
ExecuteSQL ( "SELECT DISTINCT j.log_client_IDF FROM Jobs J WHERE j.Log_date >= ?"; ""; ""; $date_30_days_ago) And then loop through the list and perform the 'divide by seats' calculation for each line
Or to do something similar but from the Clients table:
ExecuteSQL ( "SELECT SUM (j.labour_units), c.Name FROM Clients C JOIN Jobs J ON j.log_client_IDF = c.Client_ID WHERE l.Log_date >= ?"; ""; ""; $date_30_days_ago) But no joy in either case when it comes to actually performing calculations within SQL.
Is this possible in FM SQL?
By Marinos Kangas
I am new to FileMaker and still trying to find my way around the scripting language. Even though the scripting language is quite rich, I don't feel as comfortable and flexible as I do in lets say VB.
To my query now; I have designed a solution for my company which processes invoices. These invoice forms input numerical amounts tied on budget codes for each department and in turn are stored in a different table. As you can see on the image attached, I cannot find a calculation function that will sum all the amounts for each BudgetCode into AmountAllocated. AmountAssigned is the yearly department budget predefined.
I will be grateful for any suggestions or ideas.
Trying to figure something for a fellow lister. I am tring to run a query in FileMaker using ExecuteSQL() that works perfectly when I run a similar query in a normal SQL editor:
SUM(i.Quantity) AS sQTY
FROM invoice_invoicelines_Product p
JOIN invoice_InvoiceLines i ON p.pk_ProductID = i.fk_ProductID AND i.fk_InvoiceID IN (1,2,3)
WHERE p.pk_ProductID IN (1,2,3)
GROUP BY p.pk_ProductID
It produces a "?" (which is very informative).
I guess need to know if this is too much for FileMaker's SQL engine or if FileMaker is going to force me to use something that is not tradition SQL protocol/syntax.
Any feedback would be appreciated.
Hi Guys, I'm stuck to say I wonder if it has with brain power to do...
Table "Pencils" fields:
State Text | Has one of two values: "Activated" or "Deactivated"
Brand Text |Can be one out of many brands......
Pricetag Numerical |the price of each pencil
Sum_Pricetags_samebranded_and_activated pencils Calculation |?????
This is what I can't figure out for the last calculation field in the table "Pencils", How do I make a calculation out of the following:
In the calculation I want the Pencils from the same brand and with the same state "Activated" have their pricetags summed up and only if the two conditions are met.
2 pencils are of same brand and they are both in the state "Activated". The one pencil have a pricetag of 18£ and the second one have a pricetag of 10£
Because they are of same brand and both have the same state, I want their price to sum up, 18+10=28£
It does not matter what the field say if the conditions are not met for other records, the field then can stay blank.