14 Replies Latest reply on Apr 3, 2012 2:40 PM by philmodjunk

    auto enter serial number



      auto enter serial number



      I am working on a database offering services to individuals.

      At the end of the month I make a summaries by individuals ( the result of a query ) of the services;  which I then use as an invoice.

      Each service given has an ID number but I have been asked for an invoice number.

      I have an invoice number field on the layout and I have tried different ways of auto entering a serial number in this field but no luck yet. Any help would be greatly appreciated.


      Thank you

        • 1. Re: auto enter serial number

          How have you set up your tables and relationships?

          Typical invoicing systems have these tables:


          LineItems is were you list each item to be billed on a given invoice. Products_Services(optional) is your "catalog" of available items and/or services for which you might bill a client. Your Invoice number is an auto-entered serial number field in the Invoices table while your services for which you are billing people would be listed in LineItiems.

          Here's a simple demo file that uses this setup: http://fmforums.com/forum/showpost.php?post/309136/

          • 2. Re: auto enter serial number

            Thank You for your response. 

            I do have the kind of set up you mentionned; however my situation is a little different.

            We  are a non profit organization offering medical transportation services to clients. Each client request many services throughout the month and each service has it's own ID.  These services are then billed to a third party at the end of the month.

            So what I have been doing, is making a summary of the transports (services) each person received; and on this summary (invoice) I have the information about the services. The ID number , date , etc (line Item details).  The third party has requested that this summary (invoice) has and ID number.

            So, in essence I would like to generate and ID number on this summary. Since these summaries are not new records; I have not found a way to do this.


            I have also run into another problem. When i try to upload a database to a Windows server 2008 , it shows a following message " one or more of your destination folders those not have enough space for the specified Upload or it does not exist" .This has never occured before and I still have a lot of space avaiable. I checked the database folder and tried to change the permission but it keeps going back to read only. I don't know what to do other than restart ther server.

            Thanks in advance for the help


            Can I restart the server and see if this resolves the problem?

            • 3. Re: auto enter serial number

              The ID number needs to be on a sort of Ivcoice.
              That invoice lists all of the services to one single client.
              That invoice can have an Invoice number or ID.

              So basically, when you want to note a service you could have a listview that shows:

              - Client: John Doe  -  Month: April
              - Client: Jane Doe  -  Month: March

              You click on the clients 'invoice' and add a service.

              At the end of the month you make your reports.

              Does this sound about right?

              Let me know if you need any help setting this up.
              If you want I'll make you a litthe example file.

              • 4. Re: auto enter serial number

                I suggest you add an invoice table as shown in my previous post. Each time a service is provided, you add another row to the invoice. Once you bill an invoice to the "third party", you create a new invoice record and start listing services in its portal.

                • 5. Re: auto enter serial number

                  Thanks for the response,

                  Sorry but I am still not clear on this.

                  I understand that I need to create an invoice table, with a portal for the services.

                  I proceed to choose the client but then how do I get a list of the services this person received in the last month without entering then all one at a time?

                  Thanks again,


                  • 6. Re: auto enter serial number

                    You have to enter these services now don't you?

                    It's my understanding that you are already creating one record each time the service is provided to a single client. The main requirement is that you identify the agency or insurance company to be billed for that service--which can be a field in the client's record so that selecting a client, identifying the details of the service provided (date, type of service, ect.) is sufficient to log the event you then need to appear on the invoice.

                    You can either make those entries from a layout based on an invoice table via a portal, or as you currently do via a layout based directly on the portal's table. (In my example setup, this would be the lineItems table.)

                    You can use this relationship for linking invoices to services provided if you enter the data without using a portal:

                    Invoices::BillingPartyID = LineItems::BillingPartyID AND
                    Invoices::cMonth = LIneItems::cMonth

                    LineItems::BIllingPartyID would be a looked up value from the clients table.

                    cMonth, in both tables would use a calculation that returns the date of the first day of the same month:

                    DateField - Day ( DateField ) + 1  //select date as the field's return type.

                    With this relationship, you can log the services first, and then, when you create an invoice for a given "billingPartyID" and month, the appropriate records will automatically link to it. You can use a portal to review this list of billed items, but probably would not use the layout for printing the invoice. (See the demo file I posted a link for earlier that shows a more flexible way to print the invoice.)

                    • 7. Re: auto enter serial number

                      Ok, Thanks

                      I understand your demo file. i use that setup to enter the meal on wheels records.

                      Now for the invoice part, I am still confused. As per your suggestions I have created all the tables. As per the image I created a service table and a lines table so I now have a lines portal on the services layout so when I add a service record, I do it through the portal.

                      ( I realized that the lines table was required so I do not have a live link for my price field)

                      Now I have linked all of tables  except the invoice table because I do not understand which tables it should be linked to. 

                      Now if you could tell me how to link all of these; and what layout I put the lines portal on to create the invoice.

                      Also with regards to the billing party; the customer could have more than one. So when I create a service record I choose

                      1" The customer

                      2. the volunteer (who does the driving)

                      3. the billing party ( which could be the customer himself or a social organization or insurance company.

                      4. the product ( which is a set route ..point A to point B ...with a set price. 


                      Thanks for the help it is greatly appreciated.

                      • 8. Re: auto enter serial number

                        In which table do you create a new record each time a service is provided to a customer? Services or LInes?

                        It looks like you have two tables where you should have one but that could just be a lack of understanding on my part.

                        • 9. Re: auto enter serial number

                          I had been creating records in the services table but I realized now that we have a price increase (May 1 ) that

                          if  I make a change to my price field it affects all of the previous service records. so I created the  lines tables so my price field is not live.


                          Since I am creating a new databse for 2012 I can do this but it is in case I have another price increase I do not want the same problem I have now.


                          so now  I create a record through a lines portal on the service table layout;      similar to your demo

                          You have an invoice table         In my case       it is a service table

                          You create records through the lines portal   and now I do the same.


                          Hope this helps,  Thanks

                          • 10. Re: auto enter serial number

                            That's not how it's set up in the demo. Wink

                            If one record in the lines table represents service provided to a customer, what does one record in the services table represent?

                            • 11. Re: auto enter serial number

                              Sorry my mistake....

                              I create a record through the services table and through the portal I chose the product


                              • 12. Re: auto enter serial number

                                I'm not sure that I understand the link between a "product" and a "service".

                                Is there only one "product" per service record?

                                If so, I don't really see the need for a Lines table here. (there are simpler ways to manage price changes.)

                                You would link invoices to the Services table.

                                Invoices::BillingPartyID = Services::BillingPartyID AND
                                Invoices::cMonth = Services::cMonth

                                With this set up an auto-entered serial number generates your invoice number, but this sets up one invoice for each biling party for each month. You'd create a new record in Invoices, select a billing party, then enter a date so that cMonth can compute the date of the first day of the same month. Then this record matches to all records in services for that month and that billing party.

                                • 13. Re: auto enter serial number

                                  A service is:   a volunteer driving a client from their home to the hospital ( for example)

                                  and a second service would be driving them from the hospital to their home.

                                  a third driving them from their house to the doctor  etc..

                                  a product is :  ID 1     point A to point B     20 miles    price $20.00     

                                                     ID 2     point C to point D     15 miles    price $15.00

                                                     ID 3     point F to point G      35 miles   price $35.00

                                  coud be two points in the same town     or     from one town to another.



                                  In a given month client Mr Jones can have been driving 20 times to the doctor or hospital. and Mrs Thomas was driven 12 times to her doctor.  Each time the service can be paid by himself, an insurance company or a social organization.

                                  so at the end of each month I do a search by company. and  by client   

                                  I see company A will be paying for services for Mr Jones and Mrs Thomas I generate a search   Company A and Mr Jones from April 1 to April 30

                                  Mr jones received 8 were charged to Company A.  I print this summary out on a layout that looks like an invoice ;where it describes each service and all the info recquired , the service id nunber, the date the product description ( point A to point B ) and the price of each. At the bottom I have a total.  Company A owes $263.00 for Mr Jones.

                                  I repeat the same procedure for Mrs Thomas   They owe me  $126.00 for her services.

                                  I thought this was working great but the companies have requested that my summaries ( what I have been using as an invoice) has an invoice number of their own so they can pay me by invoice number instead of indicating each service id number.


                                  So this is where my problem started. since these summaries are not 'created records'  than I  don't know how to generate a serial number.

                                  Hence my request for help..  

                                    Thanks for your patience !



                                  • 14. Re: auto enter serial number

                                    I realize it makes sense to you, but it seems to me that your products are services so I don't see why you have a table for both--let alone a join table linking the two. Do you list multiple "products" for a given service? If you do, the following still works, but I'd generate that invoice report from a layout based on Lines rather than Services if that's the case.

                                    You still need the invoices table, but the requirement that services provided must match by client, billing party and date require an additional pair of fields for the relationship:

                                    Invoices::ClientID = Services::ClientID AND
                                    Invoices::BillingPartyID = Services::BillingPartyID AND
                                    Invoices::Month = Services::cMonth

                                    I've also changed Invoices::cMonth to just Month--a simple date field. Here's why:

                                    First enable "allow creation of records via this relationship" for Invoices in the above relationship.

                                    On the layout were you log a new services record, use the OnRecordCommit trigger to perform this one line script:

                                    If [ Not IsEmpty ( Services::ClientID ) and Not IsEmpty ( Services::BillingPartyID ) and Not IsEmpty ( Services::cMonth) ]
                                       Set Field [ Invoices::Month ; Services::cMonth ]
                                    End If

                                    If an invoices record for this service entry does not already exist, it will create one. If one does exist, this step attempts to modify the record but no visible change will take place as Invoices::Month and Services::cMonth will already have matching values in that case.