Get value from previous created record
After a couple of days trying to find out myself, I realized I need some help. I'm trying to build a simple database with two tables: 'projects' and 'clients'. Every project and every cliënt has an ID, which is a unique filemaker serial number.
Besides the project ID, I want a project number, which will reset at the beginning of each new year. For example, the project number can be '13.056', which means project number 56 of the year 2013.
What I want is this: when a new record is created, a calculation needs to find the value of the previous records project number and do + 1. When the previous project number is 13.056, the new project number has to be 13.057.
It sounds really simple, but I got stuck on getting the value of 'project number' from the previous record. I do not want to use Filemakers RecordNumber, because that number is not unique. When records get sorted or deleted, my project numbering will get screwed up.
I have a calculation field that calculates the unique Project ID of the previously made record. But I don't know how to use this Project ID to get the value of 'project number' of this record. I tried ExecuteSQL, but it seems that the sql query needs a given value. In other words: i cannot do something like: ExecuteSQL ( " SELECT ProjectNumber FROM Projects WHERE ProjectID = PreviousProjectID " ; " " ; " " ). The reason for this is that the query probably doesn't know what PreviousProjectID i mean, but I don't know how to fix this problem.
Any help or other ideas is very much appreciated.