record difference by group calculation problem

[The Gardener]

Hello,

My objective in the attached file is to be able to enter security data [will become various mutual funds] and have the price change between each successive record for a particular security calculated.

I thought it would be great to be able to just enter the data on one interface [layout]

and have serial ids for each security. ie. if the security is 'Apple', each time one entered a new record the [calculated] serial was set in a sequence specific to 'Apple', if the security was 'golden' then it would have its own [calculated] serial sequence. So I found an example file, serial by category, and have used it here.

On another forum I also located an answer to the question of calculating the difference between records, the answer being the use of Get(nth record) function.

This works as it should, however it calculates the price difference over all records.

Using a filtered portal, I could then see the price records and price changes for each security.

I am a beginner and have not used filmmaker for some time, several versions of the program earlier being the last time.

The Get[nth record] function is new to me.

I have tried a few ideas in the serial by category file, see the various price change test fields which do not work.

But I really am out of my depth understanding a solution.

Do I need to restructure the calculation?

Do I need a different file striation in addition?

Or do I need to adopt a totally different approach?

Should this question be put in a different forum category?

Thanks in advance for any help

[The Gardener]

I do not see my attachment so will try again to upload it.

price diff file 2.zip

[Vaughan]

Haven't looked at the attachment. Your description of what you want to do is incomplete, but I'm guessing that there is a Security that changes price (or value) and the change needs to be tracked.

In this case, make a table of Securities. Make a table of Prices. Relate the Prices to Securities. It will easy then to track the changes because each Security has a limited number of related records to calculate against.

[comment]

the price change between each successive record

To calculate a price change, you need to fetch the price from the previous record. In Filemaker, the term "previous record" can mean two different things: (1) "previous" in the current found set and sort order; or (2) "previous" by fixed criteria - for example: the last record in the same category whose date is earlier than the current record.

The GetNthRecord() function is suitable for the first meaning: it follows the current found set and sort order, and if records are not sorted in a meaningful way, the result is equally meaningless. In your example,you can use the GetNthRecord() function if your prices are sorted by stock and by date, AND the calculation is performed only if the previous record is of the same stock.

For the second meaning, you would use a relationship based on matching stock and comparing the date - then get the data from the first related record. If records are entered in chronological order, then the previous price can be looked up into a local field; this can significantly speed up a solution with a large number of records.

[The Gardener]

Hello,

Thank you both for replying. My knowledge has been greatly increased. I see now that I need to redo my file, using stock and date and sorting for starters. I also have just discovered an earlier topic posted about calculating record differences for records in the same table, which I was not aware of until now. This is a help,too. And the use of a Let function, the clarity it brings to the calculation, allows me to start to grasp whats going on.

Question, should the records be sorted when the record is committed in order for the GetNthRecord() function to work properly?

Lots for me to work on.

Thanks

[Vaughan]

GetNthRecord will function "properly" whether the records are sorted or not. The order of the records will determine the result the function returns, you as developer need to think about this.

A solution that returns the wrong result if not sorted correctly will not be reliable. I suggested using related Prices records because the relationship can be defined to sort the related records automatically.

[comment]

Question, should the records be sorted when the record is committed in order for the GetNthRecord() function to work properly?

It makes no difference: the calculation field needs to be unstored, and it will recalculate when the found set and/or sort order are changed.

[The Gardener]

Hello,

Thank you both for replying. I have it working! A start for me. Thank you for the excellent and very clear information to help me out.

I have attached the file and, having just read the last two replies to my query will make a further change to the file.

Thanks again

security price diff.fp7.zip

[Vaughan]

Congratulations. :D

[comment]

http://fmforums.com/forum/topic/80920-calculate-difference-between-records-in-the-same-table/page__view__findpost__p__376188

:confused:

[Vaughan]

I posted my congrats without seeing the file... hmmm...