Jump to content

Recommended Posts

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

Budgets.png

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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Similar Content

    • By Monarch
      Hi everyone!
      I have a text field, with each sentence separated by ¶ .
      I would like to create buttons for each line in this field, so that I can copy each line individually (and paste manually in different application for example)
      I assume that it's easy to do in calculation with GetValue, and make a script using Get (ScriptParameter) = line number in that field, for each button. ( I ll need only 7-10 lines max).
      1. I can't figure out what script function I need to use to make a selective copy...
      2. If one of the option is Set Selection function - how would it work in my case?
      Thank you!
    • By TaiChi56
      I have always developed with FileMaker for windows. I have bought myself a MacBook Air and love it. So now I am using FileMaker 16 Pro advance for MAC. I know that MAC does not label their directories like windows does. So I am having problems figuring out to get the right coding to access my photos. I have a folder called "Photos" that reside on the C: drive in windows. Then I reference that folder so pictures will populate automatically when I put in a record. Here is what I have on the windows fileMaker:
      ImagePath:
      Let ( [ // Transform the contents of a container field named Images to text ImageRef = GetAsText ( ImagePath); Photos = Position ( ImageRef; "/"; 1; PatternCount ( ImageRef; "/")) ]; Middle ( ImageRef; Photos + 1; Length ( ImageRef ) - Photos ) ) images:
      "imagewin:/C:/Photos/" & Film::title & ".jpg" Then I go into Import folder and point to the folder all the photos are in. The problem I have now is how to point to that folder in "imagewin", is their an alternative called imagemac, then I could put in Macintosh HD/Users/patricks/Documents.  The folder is in the Document folder, called Photos. Thank you for any help.
    • By Richard Carlton
      FileMaker Coaches' Corner - Tip 11 - Improve Performance - FileMaker - FileMaker Experts
      https://youtu.be/5D2qoA_S7u8
      Get up to speed with the FileMaker Pro 17 Video Training Course! 
      Top Rated Course by FileMaker Expert, Richard Carlton.  
      http://learningfilemaker.com/fmpro16.php
      Experience Richard's dynamic and exciting teaching format, while learning both basic, intermediate, and advanced FileMaker development skills. With 27 years of FileMaker experience and a long time speaker at FileMaker's Developer Conference ,Richard will teach you all the ins and outs of building FileMaker Solutions.  The course is 50 hours of video content!
      Richard has been involved with the FileMaker platform since 1990 and has grown RCC into one of the largest top tier FileMaker consultancies worldwide. 
      Richard works closely with RCC's staff: a team of 28 FileMaker 
      developers and supporting web designers. He has offices in California, Nevada, and Texas.

      Richard has been a frequent speaker at the FileMaker Developers Conference on a variety of topics involving 
      FileMaker for Startups and Entrepreneurs, and client server integration.

      Richard is the Product Manager for FM Starting Point, the popular and most downloaded free FileMaker CRM Starter Solution.

      Richard won 2015 Excellence Award from FileMaker Inc (Apple Inc) for outstanding video and product creation, leading to business development.

      RCC and LearningFileMaker.com are headquartered in Santa Clara, CA.

      http://www.rcconsulting.com/
      Please feel free to contact us at support@rcconsulting.com
      If you want to explore building I.O.S apps for I Phone or I Pad and deploying those out to the Apple App Store.
       
      Here is a video introduction to our iOS App Training https://www.youtube.com/watch?v=cVxQe_yAshw
      Looking for FM Starting Point free software download: http://www.fmstartingpoint.com
      For More Free FileMaker Videos Check out Http://www.filemakerfree.com
      Visit http://www.learningfilemaker.com for all facets of FileMaker Award Winning Video Training.
      Please Visit Our Channel: https://www.youtube.com/user/FileMakerVideos Please Subscribe While There.

      Please Comment, Like & Share All of Our Videos.

      Feel Free to Embed any of Our Videos on Your Blog or Website.
      Follow Us on Your Favorite Social Media
      https://www.facebook.com/FileMakerVideos
      https://twitter.com/filemakervideos
      https://plus.google.com/+FileMakerVideos/videos

      Filemaker Pro 17 Training Videos
      FileMaker 17 Videos
      Filemaker Pro 17 Video Course
      #FileMakerVideos
      #FileMakerTrainingVideos
      #WhatisFilemaker17
      #FilemakerPro17Training
      #Filemaker17VideoTutorial
      #FilemakerPro17Videos
      #FileMakerCoaches'CornerTip11
    • By pandsmarine
      Hi guys,
      I’m brand new working with FM and would like your help with a calculation if I may. I want a calculation to display an estimated number of paint tins needed to paint a boat based on it’s length and width. With this information I would also like the cost of the estimated paint. I'm assuming IF can not be used as there are too many variables, can CASE?
      With the boats width, I've got anything over 7 classed as wide, so I've been using Jobs::BoatWidth > 7 to define narrow and wide (only two classes of widths).  I'm using the table below to give our customers a rough idea at the moment but would like information to be displayed in FM specific to boats length and width for each customer after entering that data: Jobs::BoatWidth and Jobs::BoatLength. Cost of each tin is £26.32. Hope I haven't left anything out and thanks in advance.

    • By H
      hi
      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. 
       
×
×
  • Create New...

Important Information

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