Jump to content

Search the Community

Showing results for tags 'sum'.

More search options

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type

Community Forums

  • The New FileMaker Platform
  • Community Resources
    • Community Articles, Tips, & Techniques
    • FileMaker Marketplace Discussions
  • FileMaker Platform
    • FileMaker Interface Features
    • FileMaker Schema & Logical Functions
    • FileMaker Go for iPad and iPhone
    • FileMaker and the Internet
    • FileMaker Pro Advanced 18
    • FileMaker Pro Advanced 17
    • Legacy FileMaker Platform Discussions
  • FileMaker Server Administration
    • FileMaker Server 18
    • Zabbix Server Monitoring
    • FileMaker Server 17
    • FileMaker Cloud
    • FileMaker Custom SSL Certificates
    • oAuth and External Server Authentication
  • Brain Food
    • Security Concepts
    • The Left Brain
    • Upgrading & Migration
    • Data Analysis
    • Development Standards
    • The Separation Model
    • Relational Database Theory
    • Damaged / Corrupt File Problems
    • Bar Codes (Printer, Scanners, Software)
    • Hardware & Networking
    • OS Level Database Automation
    • Product Ideas
  • JavaScript Integration
  • FMForums Affiliates & Sponsors
  • FileMaker Classifieds
  • FM Forums Operations
  • FileMaker Friday Night Chat's Topics


There are no results to display.

There are no results to display.


  • White Papers
  • Infographics
  • Samples
  • Solutions
  • FMGo
  • Plug-Ins
  • Tutorials

Product Groups

  • Workplace Innovation Platform
  • Site Advertising
  • Development & Hosting

Find results in...

Find results that contain...

Date Created

  • Start


Last Updated

  • Start


Filter by number of...


  • Start







Website URL




OS Version

Found 14 results

  1. Well, I'm new to SQL and have used it in it's basic select mode. Now I have been asked too create a dashboard using data from different tables to display. Which on the most part is OK and working great. I have had a request for a top customer list for the month and previous month (say best 5 ranked from 1-5.) and not together so 2 seperate views I am able to pull customers and SUM totals (seperately)for the month using SELECT DISTINCT I am able to get a list of the customers for the month. How would you then get a total or SUM of the invoice amounts for the given month with the customer name and their total ? also is there a similar function to FRACTION in filemaker? Listed is my SQL Statement that just returns a ? Which I understand to mean a syntax error however, being a SQL "Virgin" any halp would be appreciated. I know I am able to do this natively through filemaker however, as I said this is for a dashboard that uses data from a number of tables. Here is my calc that is obviously wrong. Currently working with Filemaker 15 Advanced. Let ( [ ReturnSub = "\n" ; SQLResult = ExecuteSQL ( "SELECT DISTINCT (a.\"Customer Name\"), SUM(a.\"InvoiceTotal\") FROM \"Invoices \" a WHERE a.\"Invoice Sub Total\" > ? AND a.\"InvoiceMonth\" = ? AND a.\"InvoiceYear\" = ? AND a.\"Job Status\" <> ?" GROUP ON a.\"Customer Name\" ASC; " " ; "|*|" ; "0" ; Month(Get(CurrentDate))-1 ; Year(Get(CurrentDate)); "Giveaway" ) ] ; Substitute ( SQLResult ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ] ) )
  2. I have portal with related records.In portal row record I need find specific text (patterncount) from a field and sum up that records cost field. How do i do that? Do I need to do a loop every portal row and use patterncount to check correct record and sum that record cost field($costs = $costs + table::cost)? Thank you for your help.
  3. There are three tables: Services ServiceTransactions ServiceTransactionLineItems Relationships: 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.) ServiceTransaction:TransactionNumber::ServiceTransactionLineItems:TransactionFK :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.
  4. This is doing my headache now.. When paying £££ for FM and reading all this stuff on FM website 'how simple to use it is' I was under impression I'll have this pretty much sorted out in no time, especially that I am not trying to design anything fancy here, just stuff which I was able to do in Excel.. All I want to do is this: My table (GeneralLedgerTable) have i.e the following Fields: Unique ID Invoice Date Total Value Supplier Name Expense Type 00001 1 Jan 2013 $100 xxx Materials 00002 15 Feb 2013 $150 yyyy Tools 00003 18 Feb 2013 $200 zzzzz Tools 00004 2 Mar 2013 $100 aaaa Tools What I am trying to achieve is to have a separate Layout which displays the cash flow; for the purpose of this exercise the new layout would have (for start) just one simple Text Box which will calculate the total of Invoice Values in i.e February which match the condition of Tools. In excel I would use DCOUNTA - job done. I thought this will be as easy (or easier) with FM. My weeks research shows that there is a lack of this feature in FM for a very long time, a lot of people has this problem, new features in FM13 don't cover this subject and the only resources I found on the web were Custom Functions which didn't apply to my issue as they could not read Dates (Date Ranges) just separate conditions (formatted as text). I know I am probably going one step ahead now but I also would like to have a Drop List where I can choose a year and my initial Text Box would then still show values for February with matching condition for Tools but for another Year. Again in Excel I would use simple Visual Basic function with the combination of the function DCOUNTA on the spreadsheet. I understand I may think in VB terms all the time, but 'it was meant to be simpler' as the say on the FM home page. This is killing me Any help would be greatly appreciated Nicole x
  5. Hi all 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: Clients Client_ID, Seats Jobs 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? Many thanks
  6. Hi all, I'm trying to use execute sql to populate a field with the sum of all the values in a field of related records. All I get is a ? so obviously something is wrong with my syntax but I cannot figure it out. Any help much appreciated! This calculation is occurring within the 'Collections' table. Let ( [ query = " SELECT SUM(S.Volume.used) FROM \"Straws_for.entry\" AS S JOIN \"Animals.to.Collections_join\" AS A ON \"S.fk_a.to.c.join\" = \"A.pk_join\" WHERE \"A.fk_collection\" = ? " ; collection = Collections::pk_collection ; result = ExecuteSQL ( query ; "" ; "" ; collection) ] ; result ) Also, after some reading on similar threads I see people suggest not using this function in a calc. field... if there's any better way of doing it I'd love to hear it. Thanks for the help, really appreciate it.
  7. Hello, 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. Thanks
  8. Good Day, I am trying to add several records from one table in another record, in another table. This DB is based on a process that has two steps. From Step 1 to step 2, some records need to be merged. Table1 is 50 records, one field in particular is ItemID, one is ItemAmount. On Table 2, I have 0 records and I have a some fields similar to Table1, so I am populating them with lookup. The relationship is based on ItemID. So far so good. But some records on Table1 now need to be merged to one record in Table2, in particular, the ItemAmounts need to be summed to generate Table2::ItemAmount... I want the user populating Table 2 to specify the ItemID's from Table1 to be merged (summed), they can need to merge 1, 2, 3 or up to 10 Items from Table1. I have tried TypeSumField, scripts, multiples relationships, functions, but... I am completely lost to even what feature of FM should be used here... portals ? beuh... thanks for your precious help, JP
  9. I have a set of order forms, in which each order form has X amounts of orders from X amounts of clients. I can summarize with a Summary field the number of orders each client has ordered on one order form (weekly order form) and now I want to make a report that tells me the breakdown from week to week, and quarter to quarter. I can get the report to break apart the weeks by quarter, and it displays each week's correct total, but it won't do a total sum of each quarter, let alone for the entire database. I've tried summarizing the weekly summary field to get the quarterly amount, either in a new field, or on a trailing sub-summary part. The new field just copies the number from the weekly summary field from the record that is active, and the other way just shows the last above record. Wanna pull my hair out! I have a feeling that the problem is in how I set up the original weekly summary field, but I just can't see how, or how to make it work.
  10. I have run into a snag. I have two tables, one is called project and the other is called sales. The relationship between the two tables is a field called salesrep. The project table contains information on projects, specifically three important pieces of information that I need to sum up and display information in the sales table. For example. A Sales Rep will own a project and he/she will put a dollar value in the 'project_amount' field, identify which quarter the project will book in the 'billing_cycle' field and what the status is in the 'sales_projection' field. I can run manual reports all day long by searching criteria in these fields and performing a find and have that output of that find go to a layout with the search results. Pretty basic. Here is my challenge. I want to have a layout that lists all of my Sales Reps, and by their billing_cycle, have it automatically sum up all of the projects that they own with a pre-defined value, lets say the predefined value is forecast, and display that result in my sales table. Remember, the project table contains the dollar amounts and the sales table contains the account reps. An Account Rep can have many projects, however, a project can only have one Account Rep. What type of script could I write to have this done ?
  11. 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. Example: 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. Cheers
  12. I came across a bug today when using Set Field [] where you sum up the first rep of a repeating field without explicitly stating it is the first rep and the target being another repeating field regardless of if it is in the same field or not. In other words, if you do not explicitly state the field with rep 1 such as MyField[1] or GetRepetition (MyField; 1) but rather just refer to it as MyField it will ignore the value. However if you use it in a calculation field or the target field is non repeating field it works as I would expect. I made a test file and I tested it both with FM11 and 12. Whether you use the + sign or the Sum function the results are the same: 1. If you do not explicitly set the repetition for rep 1 then A. It will ignore the value in rep 1 B. Set the target rep with evaluation of the rest of the calculation without the rep 1 value C. If you hit the button subsequent times the value will keep adding the same amount without overriding the existing value 2. Everything works fine when using a calculation or another field as the target repeating_field_sum_bug.zip
  13. I am currently working on a database that will score our company's suppliers. I have one table that contains all contact information for the suppliers (Suppliers), and another related table that contains the actual score entries (Scorecards). Suppliers are scored on pieces shipped on time and quality reports. We only ever send a scorecard to suppliers based on information from the current year, so I have a field in the Scorecards table for year. The layout I am working with is based on the Suppliers table, with a portal displaying the related entries from Scorecards in the specified year. Now, where I'm running into trouble is my attempt to retrieve a total for Shipped pieces for a specific Supplier in a Specific year. In other words, how do I get FMP to take many related records, and sum only related records based on data in a third field?
  14. Hey gang, 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: SELECT p.pk_ProductID, p.Description, 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.
  • Create New...

Important Information

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