Serial Number Issues

[fargo3]

Hi all,

 

First let me say that I am an architect (for buildings) and am trying my hand at creating a database for my comany.

 

I have been using FileMaker for a few years, but it really has been a one-off sort of thing. I am trying to link many separate FM files together.

 

Now to my question:

 

I have serveral files (contacts, projects, transmittals, change orders, etc.) I have a main menu where my staff can look at a project and see all of the activities. ie a letter or memo.

 

What I would like to do is have the activities have a sequential serial number for each project. ie for job number 0515 change order 2. But still have job number 0423 have a change order 2.

 

Is there a way to do this?

 

Shawn

[David Head]

Hi Shawn

 

First let me say I am a database developer and I would never try my hand at designing a building

 

What you want is possible. There are a number of ways to do it. One way would be to script the process of creating change orders from a job number such that it:

1. goes to all related records (change orders)

2. goes to the last record (highest number)

3. duplicate that record, increment the number and clear the details

 

You also need to account for creating the first change order given there will be no existing related records.

 

If you want to pursue this, perhaps you can provide more structural details and I can run up a small demo for you.

[Inky Phil]

Hello David and Shawn,

 

Might I first say that I am a printer (owning my own business) and with the help of this forum I HAVE developed an MIS for our own use.

I think I can safely say that I have been fairly successful at both.

 

Whether this information supports David or Shawns approach is best left to the reader but let's just say that if I consider the effort that went into both projects I have no intention of attempting to design a building!

 

Many thanks for your help David & Good Luck Shawn

 

Phil

[fargo3]

Thanks for the quick reply David,

 

Structural details (I hope I get this right and my house of cards does not fall down ;-)

 

I am using FileMaker 5.0 on Macs with a combination of OSX, OS9.2 and OS9.1

 

The data bases are set up with relationships between them.

db1 - contacts holds names and address information with a portal for phone numbers that is related to...

 

db2 - phone numbers

 

db3 - Companies holds information about individual companies with a portal for the phone numbers and contacts.

 

db4 - projects has project information, address, lot numbers, design information, etc.

 

db5 - holds letters, memos, fax, transmittal information on various layouts.

 

What I have set up so far is that I have a "Main Menu" layout for projects. On this layout, there are buttons for different activities, ie Transmittals, Change Orders, Meeting Notes, etc... I have been successful in getting it to open db5 and create a new record and even give it a serial number. But right now it numbers them continuously, ie if there are 200 records they are numbered 1 - 200.

 

What I would like it to do is this:

 

Say we had a meeting with a client and a contractor. We took notes and need to publish the Meeting Notes. I create a new Meeting Notes record, it would number it Meeting #1 for the Smith Residence.

 

After the second meeting it would number the next one #2.

 

Meanwhile, for the Jones Residence we could still have Meeting #1.

 

I would like this to happen for any of the activities we are tracking.

 

Is that enough info? Too confusing?

 

Shawn

 

First let me say I am a database developer and I would never try my hand at designing a building

 

What you want is possible. There are a number of ways to do it. One way would be to script the process of creating change orders from a job number such that it:

1. goes to all related records (change orders)

2. goes to the last record (highest number)

3. duplicate that record, increment the number and clear the details

 

You also need to account for creating the first change order given there will be no existing related records.

 

If you want to pursue this, perhaps you can provide more structural details and I can run up a small demo for you.

 

[/ QUOTE ]

[J Wenmeekers]

Say that the ‘Smith Residence’ is in the projectNumber field, and you want a notesNumber field.

Here is a rough sketch of one approach:

You have to script the creation of the new records.

You need a field notesSequence (to hold your 1, 2, 3, ...) make it a simple number field.

Make a self relationship, ProjectFinal, between projectNumber = projectNumber.

What you would like is making notesSequence the max of the related projectNumbers + 1.

 

Your creation script needs to hold something along these lines:

set notesNumber = Set field(notesSequence; Case(ProjectFinal::notesSequence > 0;

ProjectFinal::notesSequence + 1; 1).

 

Play around with your field by concatenate them until you have your desired result, something like : Project number & “ – “ & notesSequence.

[Ugo DI LUCA]

Hi all,

 

There is an alternative approach involving relationships only, keeping your current recordSerial and using another one, I'd call orderSerial.

 

1. Create a Self Relationship from the ProjectID to the ProjectID in your MeetingNotes File (db5 here).

2. Create a number orderSerial set to auto enter an incrementing serial

3. Create a calculation cnextOrder = Max(SelfJoinProject::orderSerial)+1

4. Go back to the orderSerial and change it to be a lookup field (keep the serial structure as well), use the SelfJoin and cnextOrder as the source for the lookup. In the box on the right, where it says "If not match", enter a "1".

 

Now you should get an incrementing serial number tied to the Project the letter is related to.

When it's the first, it gets the "1" we entered, if not it gets the value of the next serial for the Project.

 

HTH

[Ugo DI LUCA]

Oops Jan...got caught with a few phone calls and didn't see your answer, which is quite the same.

[FileMakin' Tom]

I have a similar solution for contractors where the new change order script is called and then a "Go to Related Records" command isolates the change orders. Then the script creates a new record, sorts by creation date, goes to the last record and sets a field with the current record number.

 

This field is then appended to the CO number so that the final product is a combo of the Job No. and the CO No. as in

 

CO 12345-1

CO 12345-2

CO 12345-3

 

etc.

 

This works very quickly and requires no special calcs.

 

Be well . . .

 

Tom

[J Wenmeekers]

Ugo...it's something like:

"Les grand esprits...etc..."

[mike2wheelsports]

I'm trying to create a way to reset a serial and it increment both with the same number. I've created a field for data input and it works for the serial number but I can't figure out how to change the incremental number as well.

[Maarten Witberg]

see if the demo here is any use for you