Table of Updates Updating Calculations

[Neil Scrivener]

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? 

Thanks!

N

[comment]

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.

 

 

[Neil Scrivener]

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? 

Thanks!

N

[comment]

I am afraid I don't understand the question.

 

[Neil Scrivener]

Sorry,

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? 

[comment]

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.

 

 

[Neil Scrivener]

Makes perfect sense, thank you

Nx