Jump to content

Recommended Posts


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.



Edited by Marinos Kangas

Share this post

Link to post
Share on other sites

Care all of these 'budget' Table Occurances based on the same base table?

Same question for the 'budget code' TOs.


Share this post

Link to post
Share on other sites

Yes Wim, those are all connected to the main Invoice page and the only common link is the Invoice number.  As for the Budget Code tables, at first I thought of not linking them and calculating them via script but I cannot figure out a command that will give me the flexibility of an SQL query.

Share this post

Link to post
Share on other sites

I don't think I understand your answer in relation to my question...  If 'budget' and 'budget codes' are just one table each then it should be fairly easy to set the 'amount allocated' to the right value by using any of your existing relationships.  sum(personnelBudget::amount) for instance will do if you execute the Sum() from the context of the 'PersonnelBudgetCode' TO.  Given that all of the relationships between budget code and budget are duplicates you can pick any one of them as long as start from the TO used in the relationship.

Remember that you can do a SQL query in FM with the ExecuteSQL() function if you find that easier.

  • Like 1

Share this post

Link to post
Share on other sites

I don't think you need a new table occurrence for a different budget type (budget code) you just need a type/code field.

Also you should use an ID (primary and foreign keys) to connect table occurrences.

It always helps if people explain in layman's terms what they are trying to achieve so we can recommend a good approach.

Sent from my iPhone using Tapatalk

Share this post

Link to post
Share on other sites

Well...silly me, I confused myself for the wrong reasons.  Wim, the Sum calculation worked like a charm in the AmountAllocated field due to the relationship I have created.

The ExecuteSQL looks like a godsend command I will investigate.  I am sure it will come handy when the going gets tough :)

Thank you both for your insight.  Sometimes the solution is right in front of your nose, you just can't see it.

Share this post

Link to post
Share on other sites

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

  • Similar Content

    • By H
      i have a table with 4  fields
      field 1 =  number result 1
      field 2 =number result 2
      field 3= percentage result
      field 4 = calculation by taking in whichever is lower field 1 or 2 and multiplying by field 3
      the question now is how do i script the calculation it should pick up which ever is lower but ignore if field is empty.
      i hope i am clear , i would appreciate if someone could help me out with this calculation. 
    • By Eli Walker
      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.
    • By Ponderosa
      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.
    • By userfmp
      I have the following:
      Table 1 – Expenses
      Field A: Date
      Field B: Concept
      Field C: Amount
      Table 2 – Flight Time
      Field D: Date
      Field E: Flight Time (in hours)
      I need a report that will give me the expense cost per hour, i.e. the total of Field C divided by the total of Field E.
      Table 1 and Table 2 are not related. What relationship must I establish between them to obtain my desired result?
      If establishing a relationship between the tables is not the answer, how else can I achieve the figure I need?
      Thank you for any help that can be provided. 
    • By NewBoard
      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?

Important Information

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