10 Replies Latest reply on Dec 21, 2012 7:17 AM by philmodjunk

    Serial Number



      Serial Number



           I made a Filemaker pro 10 Solution for my invoicing and been using it for 3 years and works really good. The only probleme is that I enter my ID numbers manually. I use the same table for Estimate and Work Orders. The reason is that I could find a way that when I do an estimate to choose a number. Then later on I can convert that estimate and get an work order number that follows the Work Order last number. ( I know about auto entry serial Number, what I'm looking for is a script that would do it like this)


           Record 1 ES 1

           Record 2 WO1

           Record 3 WO2

           Record 4 ES 2

           Record 5 WO3

           Record 6 ES 3 (but would like this Estimate to be converted to WO4)

           Record 7 WO5

           Redord 8 ES4

           Any body have any script solution. I've played with Global that saves last WO number. No luck (probably wrong script.)


        • 1. Re: Serial Number

               I'd use two different fields for this--one for estimates and one for work orders.

               There are also two different approaches for managing estimates and work orders (Or estimates and invoices) in the same table. One method uses a single record for both estimate and work order. To produce a work order, you change the value of a status field to relabel it as a work order instead of an estimate. This works well if there is almost never a change in the details of the work order when compared to the data used to create the estimate.

               The other option duplicates and estimate record to produce a work order record. This allows you to compare the details of the original estimate to the details of the work order it produced--useful when additional charges approved by the client may be added to the work order after the original estimate was presented to the client as it will document those extra charges as not being part of the original estimate.

               The above options can significantly affect how one generates a series of work order and a series of estimate serial numbers.

          • 2. Re: Serial Number

                 Thanks for your quick answer. I like your first idea. As for the second one if you duplicate it will change both ID number witch are now in seperate fields.  So my WO number would jump number and wouldn't follow the sequense, right?

            • 3. Re: Serial Number

                   Not necessarily. There is more than one option and I know of a method that can generate serial numbers "on demand" via a script. This method would not result in the WO or ES serial numbers skipping values in their respective sequences. The method generates related records in a pair of related tables--one for ES one for WO and your WO's and ES's refer to the appropriate related record to display these serial values.

              • 4. Re: Serial Number

                     Do you mean two other tables not the current table witch containes the original WO and ES. What would contains these tables just the ID fields then linked to the main table? I've tried a script with the Set Next Serial number with no luch. Can you give me examples of a script. witch would upgrade my two ID number seperatly with a button or a Script Trigger. Remember I have only one Table that controls all ES and WO. Again, thanks for your help.

                • 5. Re: Serial Number

                       Typical structure for an Estimate, Work Order, Invoice or Purchase Order is:


                       WorkOrderEstimate::__pkWOESID = LIneItems::_fkWOESID

                       And the WO and ES serial numbers that we are discussing are different fields from the __pkWOESID I have referred to in the above information.

                       See the first post of: Common Forum Relationship and Field Notations Explained if my notation is not familiar.

                       To that, you would add two more tables: WOSerialNumbers and ESSerialNumbers. Relate them like this:

                       WOSerialNumbers------WorkOrderEstimate---------ESSerialNumbers   (Note that these are one to one relationships)

                       WorkOrderEstimate::__pkWOESID = WOSerialNumbers::_fkWOESID
                       WorkOrderEstimate::__pkWOESID = ESSerialNumbers::_fkWOESID

                       Enable "allow creation of records via this relationship" for both of these new tables.

                       Then define WOSerial in WOSerialNumbers as an auto-entered serial number. Define ESSerial as an auto-entered serial number in ESSerialNumbers.

                       Then, when you want to generate a WO serial number, use this script step:

                       Set Field [WOSerialNumbers::_fkWOESID ; WorkOrderEstimate::__pkWOESID ]

                       and for a ES serial number:

                       Set Field [ESSerialNumbers::_fkWOESID ; WorkOrderEstimate::__pkWOESID ]

                       To show these numbers, just place WOSerialNumbers::_fkWOESID and ESSerialNumbers::_fkWOESID on your WorkOrderEstimate layout. The fields will be blank until you run a script with one of these set field steps to assign a serial number to it.

                  • 6. Re: Serial Number

                         Thanks again for all the really good and detailed info. I'll try this and let you know.

                    • 7. Re: Serial Number

                           Nope, no luck. Could you draw a dialog box like you did in your

                           "  See the first post of: Common Forum Relationship and Field Notations Explained if my notation is not familiar  "  example. 

                           Also when you do a one to one relationship, how do you get the fork on only one side. When I drag to join the two field there's always forks on both sides. 

                           Aslo when you say to "Enable "allow creation of records via this relationship" for both of these new tables" is it on both side.

                           If you give up I'll understand.


                      • 8. Re: Serial Number

                             Drawing that dialog requires creating a database with those relationships. The text version is intended to be an easy way to document relationships without taking the time to do that. By comparing the screen shot in the thread to the text example you should be able to see how the text documents the same details as shown in the screen shot of the relationships graph.

                             If you are getting a fork on both ends of the relationship line, the __pk fields have not been defined as auto-entered serial numbers. Double click the field definition for each __pk field to open field options, click auto-enter and select the serial number option.

                             "Allow creation" should only be enabled for the two serialNumbers table occurrences, but if you selected both, it should not affect the results you get, you've just enabled more than is truly necessary.

                        • 9. Re: Serial Number

                               I must be stupid.

                          • 10. Re: Serial Number

                                 As shown here from my previous post:

                                 WOSerialNumbers------WorkOrderEstimate---------ESSerialNumbers   (Note that these are one to one relationships)

                                 WorkOrderEstimate::__pkWOESID = WOSerialNumbers::_fkWOESID
                                 WorkOrderEstimate::__pkWOESID = ESSerialNumbers::_fkWOESID

                                 Both WOSerialNumbers and ESSerialNumbers link directly to your WorkOrderEstimate table--not to LineItems.

                                 Also as shown, you need an additional field in each of these tables that you have not yet defined: :_fkWOESID should also be defined in each of these new tables as they are the ones you need to use as Match fields back to the WorkOrderEstimate table occurrence.