1 2 Previous Next 24 Replies Latest reply on Nov 24, 2013 1:19 PM by JoostvanBreukelen

    Get value from previous created record

    JoostvanBreukelen

      Title

      Get value from previous created record

      Post

           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.

           Joost

            

        • 1. Re: Get value from previous created record
          philmodjunk

               Your executeSQL function could be made to work:

                ExecuteSQL ( " SELECT ProjectNumber FROM Projects WHERE ProjectID  = ? " ; " " ; " " ; PreviousProjectID ) + 1

               But it will assign the wrong value at the start of a new year.

               There are two other options that can handle that issue.

               Make the Project number another another auto-entered serial number. Reset the serial number to 1 at the start of each new year--something that can be automated in a script.

               Use a self join relationship--either in Manage | Database or ExecuteSQL that matches records by Year. Assign the Max ( ProjectNumber ) + 1 [with a self join in manage database, use: Max ( RelatedTable::ProjectNumber ) + 1 ] as the new project number.

          • 2. Re: Get value from previous created record
            JoostvanBreukelen

                 Thank you very much for your help. Unfortunately, the function you suggested turned out in a question mark as a result. 

                 EDIT: sorry, I made a typo. The function seems to work. I'm going to try it now, I let you know!

                 Maybe I should tell you that I get the PreviousProjectID by having the following calculation: MAX(projects 2::ProjectID), where projects 2 is a self join with a '<' relationship.

                  

            • 3. Re: Get value from previous created record
              philmodjunk

                   Come first new project in January, the calculation will not automatically return a 1 for the start of the new year. That's handled by the two alternative methods that I've spelled out in my previous comment.

              • 4. Re: Get value from previous created record
                JoostvanBreukelen

                     Phil,

                     Thanks for you help so far. I got the first part to work with your ExecuteSQL calculation. Now there's an automatic numbering of the projects exactly as I want it. 

                     Now I would like an automatic reset at the beginning of a new year. Because I don't want a manually activated script to do this, I followed your second suggestion. Probably I do something wrong, but the only thing happens is that the project number goes one up. The Max ( RelatedTable::ProjectNumber ) + 1, gives the highest project number of all records plus 1. A change of year doesn't change the highest project number. I made RelatedTable to match with the original table on year. 

                     What I probably need is a calculation that gets the amount of the records belonging to a certain year. As soon as this returns '1', there must be a new year, and I can use this the calculation of the projectnumber, right? 

                     I hope you can explain your second suggestion in more detail to me (I'm a quick learner, but new in filemaker). 

                      

                • 5. Re: Get value from previous created record
                  philmodjunk
                       

                            The Max ( RelatedTable::ProjectNumber ) + 1, gives the highest project number of all records plus 1. A change of year doesn't change the highest project number. I made RelatedTable to match with the original table on year.

                       Then I must conclude that either your relationship does not actually match records by year correctly or that your calculation is not referring to the correct table occurrence in order to assign the next value in the series for the new project record.

                  • 6. Re: Get value from previous created record
                    JoostvanBreukelen
                         

                    Then I must conclude that either your relationship does not actually match records by year correctly or that your calculation is not referring to the correct table occurrence in order to assign the next value in the series for the new project record

                         Well, they do.smiley The Max (RelatedTable::ProjectNumber) gives the highest project number of a certain year. Say the last project number of 2013 is 16, then the first project number of 2014 will be 17. The Max will be 17 because it is the highest value in 2014. 

                         What I did to accomplish my mission is the following. I made a new calculation for project number:

                         If(RelatedTable::Project ID = Project ID; 1; ExecuteSQL ( " SELECT Projectnumber FROM Projects  WHERE \"Project ID\"  = ? " ; " " ; " " ; PreviousProjectID ) + 1)

                         This calculation compares the first project ID of the new year with the project ID of the current record. If it is the same, then it is must be the first record in the new year, so it gives '1' as a result. This will be the new projectnummber to be used in another calculation that makes (14.001). If the result is not '1', then it simply uses your calculation to count up the project number of the previous project with 1. To the second record in 2014 will be 2 (14.002 after a second calculation). 

                         Thank you very much for your help. 

                          

                    • 7. Re: Get value from previous created record
                      philmodjunk
                           

                                 The Max (RelatedTable::ProjectNumber) gives the highest project number of a certain year. Say the last project number of 2013 is 16, then the first project number of 2014 will be 17. The Max will be 17 because it is the highest value in 2014.

                           Nope. That is NOT the case.

                           If your relationship, as I specified, matches only to projects of the current year, then Max ( relatedTable::projectNumber ) returns null at the beginning of the year as there are no projects yet to match to. null + 1 produces a value of 1 and your value resets to 1 with the new year. Your own If function shows that this will be the case or it wouldn't work either.

                           Your execute SQL query could also be modified to do this and then you would not need the if function or the self join relationship:

                           ExecuteSQL ( " SELECT Max ( Projectnumber )  FROM Projects  WHERE \"Project ID\"  = ? AND \"year\" = ? " ; " " ; " " ; PreviousProjectID ; Year ( Get ( CurrentDate ) ) ) + 1)

                      • 8. Re: Get value from previous created record
                        JoostvanBreukelen
                             

                        Nope. That is NOT the case.

                        Ok, then my relationship must be set up in another way then you specified. What I have now is a table called 'projects'. Then there a self join table I referred to as 'related table' (actually: Projects 4). Both tables have a field called 'year'. In the relationship graph there is a line between 'year' in table projects and 'year' in table projects 4. The relationship is specified with '='. 

                             Is this the relationship you specified? If not, what exactly do you mean?

                        • 9. Re: Get value from previous created record
                          philmodjunk

                               You would define an unstored calculation field called "cThisYear" with this expression:

                               Year ( get ( CurrentDate ) )

                               Then your relationship is:

                               Projects::cThisYear = Projects|thisyear::cYear

                               Assuming that you have a date field named ProjectDate, cYear would be defined as Year ( ProjectDate )

                          • 10. Re: Get value from previous created record
                            JoostvanBreukelen

                                 Sorry, explain this to me as if I am 4 years old please. Do you make two fields? One calculation field called 'cThisYear' and one field 'cYear'?

                                 Forget about the calculations to get the year (I get that part). What I don't get is the relationship that you suggest. 

                            • 11. Re: Get value from previous created record
                              philmodjunk
                                   

                                        Do you make two fields? One calculation field called 'cThisYear' and one field 'cYear'?

                                   Yes

                                   cThisYear will return the year from the date produced by your computer's system clock. In 2013, this relationship thus matches to all records with a 2013 date. In 2014, it will match to all records with a 2014 date. It thus automatically updates to match to only records of the current year.

                                   Please note that you must select Storage Options in the cThisYear calculation and select the "do not store..." storage option.

                              • 12. Re: Get value from previous created record
                                JoostvanBreukelen

                                     You mean a relation like this?

                                Please just explain your relationship more clearly. What do you mean with the '|thisyear' part in: Projects::cThisYear = Projects|thisyear::cYear

                                     I totally understand what you want as a result and I totally understand how a year is calculated from the system clock. So forget about that part. Please just explain (describe or draw) the tables, the related fields and the relationship between those fields. That's the part I do not understand. 

                                     Thanks for your patience. 

                                      

                                • 13. Re: Get value from previous created record
                                  philmodjunk

                                       That's the relationship I am describing.

                                       "|thisyear" is just text that is part of the name that I specified for the second table occurrence of projects. It's more descriptive than "Projects 2" and that makes it easier to select from lists of table occurrences when working with the databas. (You can rename a table occurrence if you double click it.)

                                  • 14. Re: Get value from previous created record
                                    JoostvanBreukelen

                                         Phil,

                                         I can't get it to work. I've been trying, believe me. I even made a special database to test it. The relationship is exactly as in the picture above. 

                                         cYear is a calculation field with the calculation: Max (Projects 2::Projectnumber) + 1

                                         cThisYear is an unstored calculation field with the calculation to get the system year.

                                         The field cYear remains empty..... If I make the calculation in a separate field...same result. 

                                         What am I doing wrong...must be simple, right?

                                    1 2 Previous Next