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.
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.
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.
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).
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.
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. 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.
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)
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?
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 )
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.
Do you make two fields? One calculation field called 'cThisYear' and one field 'cYear'?
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.
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.
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.)
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?