AnsweredAssumed Answered

Get value from previous created record

Question asked by JoostvanBreukelen on Nov 8, 2013
Latest reply on Nov 24, 2013 by JoostvanBreukelen


Get value from previous created record


     Hi Guys,

     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.