Serial Number Field based on the current year and a serial no.

[fmow]

We have the following convention in my company of naming our project starting with the last two digits of a year (i.e. 2013, becomes 13) followed by a three digit serial project number as projects come in, so project no. 10 this year, would be project_id = 13010.

 

Initially I was thinking of having the user input the project no and using a hidden filamaker serial as primary key. but I 've decided against this and I am thinking of keeping the current convention as the primary key in the projects table too.

 

But how do I have filemaker input this YYXXX format automatically?

[jbante]

Stick with your initial thought. With any kind of "intelligent" key like you're describing, you're better off having an additional hidden field acting as the relational primary key, in my experience — Get ( UUID ) and a straight native serial number are both reasonable for that.

 

You can probably get away with using a straight auto-enter serial number for your project numbers, too. At the start of the year, use the Set Next Serial Value script step to set what the project number for the first project of the year will be:

 

Right ( Year ( Get ( CurrentDate ) ) ; 2 ) & "001"

 

Then the project numbers will auto-enter in order. You'll want to find a way to validate that you don't push the numbers over to 14000 for your thousandth project for 2013, though. 

[fmow]

Thanks J. Much appreciated. 

 

My problem is how do I autopopulate a a foreign key with a primary serial key if I have it hidden.

 

That is if I use our custom serial for the projects as a primary key, then from a list with the project title next to it, I can populate the foreign key inputted from a user. If I keep it hidden how does each new related record get the id automatically so that per every new record in a child table I set primary key = foreign key without any involvement for the user. Some if function that when a user selects the custom company project serial, the foreign key is automatically populated with the corresponding auto serial number from the parent's primary key.

 

Sorry if this sounds dumb and/or really rudimentary but I am on my second week learning fm. 

[steigrafx]

See if this helps.  Run the script.

testing.zip

[jbante]

My problem is how do I autopopulate a a foreign key with a primary serial key if I have it hidden.

 

That depends just how automatic you want it to be.

 

At one end of the spectrum where you put the foreign key field on a layout and enable direct user entry, you might use a pop-up menu control for the field, using a value list of the project primary key and project name, showing only the project name. This way, users will see only the project name, but the key is what will be set in the field. This works best if you expect to have at most a few dozen projects in the list. You can search the forums for any number of value list filtering techniques to, for example, limit the list to projects from the current year. If you want to show the user-visible project number in front of the project name in the value list, you can create a calculation field, I'll call it projectValueListName = projectNumber & " - " & projectName, then use that as the second field in your value list instead of the projectName field.

 

If your users will know the project number off the top of their heads already, you can create a utility global field for them to enter that number, then a utility relationship to a Project table occurrence matching that utility field to the user-facing projectNumber field. Put a button next to that field calling a script that sets the Table::foreignKey field to the Project_utilityTO::primaryKey. (It would be good if that script also cleared the utility global field at the end.)

 

At the other end of the spectrum, you can create a scripted "pop-up picker" process that takes users to a searchable list of projects, each with a "select" button that saves the project primary key and sets it to the original foreign key field. If you expect users to be selecting from more than a couple dozen projects, you were probably going to have to build something like this to have a good user interface anyway. Start another thread if you need help with this approach.

[fmow]

That depends just how automatic you want it to be.

 

At one end of the spectrum where you put the foreign key field on a layout and enable direct user entry, you might use a pop-up menu control for the field, using a value list of the project primary key and project name, showing only the project name. This way, users will see only the project name, but the key is what will be set in the field. This works best if you expect to have at most a few dozen projects in the list. You can search the forums for any number of value list filtering techniques to, for example, limit the list to projects from the current year. If you want to show the user-visible project number in front of the project name in the value list, you can create a calculation field, I'll call it projectValueListName = projectNumber & " - " & projectName, then use that as the second field in your value list instead of the projectName field.

 

If your users will know the project number off the top of their heads already, you can create a utility global field for them to enter that number, then a utility relationship to a Project table occurrence matching that utility field to the user-facing projectNumber field. Put a button next to that field calling a script that sets the Table::foreignKey field to the Project_utilityTO::primaryKey. (It would be good if that script also cleared the utility global field at the end.)

 

At the other end of the spectrum, you can create a scripted "pop-up picker" process that takes users to a searchable list of projects, each with a "select" button that saves the project primary key and sets it to the original foreign key field. If you expect users to be selecting from more than a couple dozen projects, you were probably going to have to build something like this to have a good user interface anyway. Start another thread if you need help with this approach.

 

Many thanks again J. First way to do it is straightforward, I just select show values only from second field, i.e. the project name and I have it enter the values of the first field, i.e. the primary key. 

 

Value list filtering techniques will sure come in handy, it's what I 'll be searching next. 

 

Yeap, I 'd like to be able to show the user visible project number and project name and I was thinking of another field with both included, so I 'll go with what you are suggesting here with that calculation field. 

 

I 'll be reading about the rest of your suggestions because at the current stage of my learning head they are harder to understand, and I 'll get back to you. 

 

(update: ok, in terms of the third suggestion I will have to read quite a bit, so it will be later today that I 'll respond. In terms of global fields and utility relationships I am just starting to figure them out, so I might soon get back to you on that).

 

Many thanks again J.

 

Thanks to steigrafx too for the script, I 've not had a chance to look into it yet, but 'll do so soon. 

[Vaughan]

We have the following convention in my company of naming our project starting with the last two digits of a year (i.e. 2013, becomes 13) followed by a three digit serial project number as projects come in, so project no. 10 this year, would be project_id = 13010.

 

Initially I was thinking of having the user input the project no and using a hidden filamaker serial as primary key. but I 've decided against this and I am thinking of keeping the current convention as the primary key in the projects table too.

 

But how do I have filemaker input this YYXXX format automatically?

 

 

The challenge comes when on 1 January the year rolls over and the serial number resets to 1, several projects are created AND THEN somebody wants to create a project and back-date it to the previous year.

 

It can be done if you are smart and use a table to store the project numbers: each record is a year, with a field to store the next project number. As projects are created the serial number is incremented.

 

When a new year is started, a new Year record is created and the serial numbers for this year are tracked. The previous year's serial numbers are still remembered.

 

If somebody wants to create a project for last year, just look up that year's record and increment that serial number.

[fmow]

That's a really great work around. Thanks!