Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aussieaj
Starting Member
2 Posts |
 Posted - 2010-11-01 : 18:55:15
|
| Hello,I'm new to SQL Server, coming from Filemaker Pro.I have a database in FIlemaker Pro and I'm just trying to get my head around how things work comparatively in SQL Server.First question is to do with the primary key. If we are putting "jobs" into a database, we used a standard integer id in filemaker. To make our job reference for staff, we would add the letter A & the year before the job, ie. A2008/xxxx. Would there be a way to make this the primary key without having to concatenate the information with the integer. (How could we reset to 0001 for the job number as the next year ticks over, instead of cotinuing up).Second and final question relates to the difference between the front end and back end. Ie. we have a section where the staff can write off the jobs they have attended by putting in the date/times of attendance, start time and finish time on individual jobs etc. We use a Filemaker "Portal" (link to related records in another table) to create these so that when the staff put in a staff time, it automatically completes the end time and dates adjusting the duration etc. We use a fairly complex script to do this so that it works even if a job goes over midnight...Is that sort of thing done by the SQL Server, or is that sort of thing done by coding/scripting in whatever front end we put on the database be it web based/visual studio or Access.Any advice would be greatly appreciated!Cheers,Andrew |
|
|
TimSman
Posting Yak Master
127 Posts |
Posted - 2010-11-12 : 09:45:27
|
| So, are you saying that you want the primary key to reset back to 0001 every year? Are you still planning on appending the letter/year combo in front?I would do the second part in the front end. It sounds like a straightforward time entry system. The user enters the start time and end time for a specific job. That could easily be a web page they have access to, and then the front end passes the data to SQL server, which can use stored procedures to do whatever you need after that.High level view of things. But that's the gist of what I would do in this situatino. |
 |
|
|
aussieaj
Starting Member
2 Posts |
Posted - 2010-11-19 : 17:34:24
|
| Hi TimSman, Thanks for replying.You are correct, I'd like to append the year in front (and possibly the letter, however I could add the letter front end in formatiing without any dramas I guess).So 2010/xxxx would be the job number. And then I would somehow need it to reset to 1 at midnight on NYE, so it becomes 2011/0001.And for the second issue I thought that would be the case. Cheers.Andrew |
|
|
|
|
|
|
|
|
Calculations