Jump to content

Table of Updates Updating Calculations

Recommended Posts

Hi All, 

So my ambitious database is coming on well, and in particular thanks to Comment in this forum!

I'm on to the final chapter of impossibilities and the current one is below, which I don't really know how to start. 

So I have a table of information, which has what I would call an 'original' piece of data, a number.  

Each month, that number gets updated, on a monthly basis, at the end of each month - the idea being that you can see things in a shapshot of the present, and in the past if required. So when you add Record in the Numbers Update Table and select the Company, it replaces the 'old number' with the new number, and on it goes. 

There would be a Calculation which takes the 'new number' to work out the difference from the original number. 

In the ordinary course of things, where there are multiple fields in the same table, I'd use a Get List Values calculation - but because the 'new' number is being added each time a Record is created - I'm a bit stumped how to go about this. 

To give an example of what I'm looking for:

Numbers Update Table

Company Month Number
Company A March 12
Company A April 3
Company A May 5

Companies Table

Company Original Number Particular Month Number Difference Calc
Company A 100 as per reference from Record  
Company B 200 as per reference from Record  


So in essence, the Difference calc would show, for Company A in March, 103 in April and so forth. 

How would I go about this? 



Link to post
Share on other sites

This is somewhat confusing. I think you want to use the Last() function to get values from the most recent related record in the Numbers table. Note that this assumes records are entered in chronological order and that the relationship is not sorted.

P.S. I would recommend storing the year alongside the month. Or simply make the field a Date field and store the date of the first or last (or any) day of the relevant month.



Link to post
Share on other sites

Thank you for this. 

Surprisingly I have put it together and seems to be working well! 

To this more precise, and to avoid any sorting issues, can I change the Last() to add a Time and Date, so it gets the 'true last - how would I amend that calculation to include a Time/Date? 



Link to post
Share on other sites


So at the moment we are using Last() based on the sorted records. 

Is there a calculation I could use along the lines of Last + Date, so if the Records were accidentally sorted,  it will still find the Last be referencing the date as the 'most recent' date? 

Link to post
Share on other sites
43 minutes ago, Neil Scrivener said:

if the Records were accidentally sorted,

You can sort the records in their own table (or in a portal) in any order you like, accidentally or on purpose, without affecting the results returned by Last(). Last() works over a relationship and depends solely on the sort order you have set up in the definition of the relationship. As long as the relationship is not defined to have a sort order, Last() will return the value from the most recently created related record. This is why I said: 

19 hours ago, comment said:

this assumes records are entered in chronological order and that the relationship is not sorted.

Note that if you wanted, you could define the relationship to sort the related records in reverse creation (or chronological) order. In such case, you could get the value from the most recent record by a simple reference to the field. But this takes extra processing which might not be justified if you only need to extract one or two values.



  • Like 1
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 Spidey
      I have two table: Invoice and Customer.  I like to have the total of all the invoice for a customer between certain date in the Customer portal that show all the customers, but I got a error when I try to debug..
      ExecuteSQL("SELECT SUM(I.TotalAmount) FROM Invoice I JOIN Customer C ON I._kf_CustomerID = C.__kp_CustomerID WHERE date(I.InvoiceDate) between date(C.SearchFromDate ) and   date(C.SearchToDate )" ; "" ; "" )
      I have an error and couldn't figure it out.  Thanks...

    • By Todd Dignan
      I have a client that has been using a send email script step  that brings up the outlook email client on the desktop.  This as worked for years no problem.  It has stopped work on 3 of 35 computers within the last two weeks.  I talked with there IT personal and they have assured me that no updates have happened.  The actual error is -
      Microsoft Office Outlook
      Either there is no default mail client or the current mail client cannot
      fulfill the messaging request.  Please run Microsoft Outlook and set it as
      the default mail client.
      I have double checked with system default  and Outlook's settings.  Both are set to default.
      The client is using the latest version of office 360's and the latest version of FileMaker 18 advance. Both 64bit.
      Any suggestions are welcome.

    • By tbcomputerguy
      I have an excel sheet that controls bills of ladings for a forestry company.  In the example you can see that there is lots going on with this Bill.  It has a payperiod, mill, truck that delivered it, etc.
      I would like setup a database to monitor this.  The fields CT1, CT2, Skid1, Skid2. PROC1, PROC2 are all contractor numbers.  There are 6 contactors.  The percentages in each line are the amount of the volume they performed  In the third line there is a value in CT1 only...they get 100% of the volume.  I can figure out most of this, but am stumped on how I can monitor when a contractor does multiple jobs..ie in line one, contractor 5, cuts and skids.  All 6 contractors could be involved in one BOL. Each one of these jobs, cutting, skidding and processing each has their own respective rate of pay as well.   I think i need a way to break down each line so that I can produce pay summaries for each of the contractors.  I had started this years ago, and thought I asked in a forum, but can't remember where.  Nonetheless, they stopped using multiple contractors per load...Now they have returned, so I am back at it.  So if this is a repost from years ago I apologize.  
      Thanks in Advance

    • By dancer5678
      I am using Filemaker Server 18 on Windows Server 2012 R2
      Been using it for years with no issues
      Currently when I log in to the console it is very sluggish.
      When I get to the Dashboard it shows No databases, then it auto refreshes and the database list appears.
      Within 15 seconds of scrolling the database list to open files the screen refreshes. This situations is happening over and over in a loop.
      Any Thoughts on what is causing this issue?
    • By stevaroni
      I get an error 3 when using a script to Export Records via WebDirect. Using FileMaker Server 18 and have tried both Safari and Chrome both with same results. I have tried using the temporary path, desktop path, and documents path. I have tried using with the automatically open and not. I have tried writing a tab delimited and comma delimited file. Does anyone have ideas I haven't yet tried?
  • Who Viewed the Topic

    5 members have viewed this topic:
    Nuri Baba  muzz  Christopher Grant  DR. ALI BAHAR  travstravz 

  • Create New...

Important Information

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