1 2 Previous Next 19 Replies Latest reply on Oct 16, 2013 4:41 PM by fysio2008

    Creating multiple invoice number sequences and create on commit/print

    fysio2008

      Title

      Creating multiple invoice number sequences and create on commit/print

      Post

           Hi.

           I have created a database with clients, treatment records and am now trying to set up an invoice part.

           The invoice part works fine with invoice numbers  in sequence (10001, 10002... etc) created by a number field with auto-enter serial.

           My two  challenges is:

           1)I need to set it up so that every user/account (at the time 3 or 4) has it´s own line of invoice numbers.

           Therapist 1 : 10001, 10002...etc

           Therapist 2: 30001, 30002...etc

           Therapist 3: 50001, 50002...etc

            

           Each therapist is self-employed (I don´t know if it is the right word, but I guess you can understand). Therefore, due to tax reasons, they need their own separate sequence of invoice numbers. For the same reason each sequence must be consecutive.

            

      2) To eliminate problems with invoice number sequences, for instance if a therapist by mistake click on the button "create new invoice" I need the invoice number to be assigned when the invoice is printed and not when "create new invoice" is clicked.

           I can´t just use "on commit" instead of "on creation" with the auto-enter serial, because, as it is right now, I create the invoice lines in a portal, and I guess it causes problems with the relation to the "patientId" field.

            

           Both problems would not arise if there were only one therapist and the therapist did not accidently click on "create new invoice", but unfortunately the databse is not being used in a perfect world :)

            

           The database is created so that the user has to log in using their own initials, for instance TK.

           I have 4 tables (related to this part):  "Patient", "Faktura" (invoice), "FakturaLinier" (invoicelines), "Produkter" (products). They are related as you can see in the attached picture.

           I guess I can create the 3 or 4  invoice numbers sequences with a calculation, but unfortunately I am not skilled enough yet to figure out how it should be done.

            

           Help with some details will be very much appreciated as I am quite new to FileMaker.

            

           Thanks!

      Udklip_11.JPG

        • 1. Re: Creating multiple invoice number sequences and create on commit/print
          philmodjunk

               I strongly recommend that you keep your current invoice number for use as a a match field in your relationships. Use this "encoded meaning" invoice number strictly for printing/displaying invoices.

               I'll need to use English and my own names as I'm not sure that I can identify the correct table occurrences from your screen shot.

               Set up a self join relationship that matches any given invoice to all invoices with the same therapist:

               Invoices-----<Invoices|SameTherapist

               Invoices::_fkTherapistID = Invoices|SameTherapist::_fkTherapistID

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

               Invoices|SameTherapist is a new Tutorial: What are Table Occurrences? that refers to the same data source table as Invoices.

               Then this script can be used to assign the needed invoice number either via a button on the invoices layout or as part of a script that prints the invoice:

               If [ IsEmpty ( Invoices::InvoiceNumber ) // no number has been previously assigned to this invoice ]
                 Set Field [ Invoices::InvoiceSequence ; Max ( Invoices|SameTherapist::InvoiceSequence ) + 1 ]
               End If

               Define InvoiceNumber as a field with this auto-enter calculation:

               Therapists::__pkTherapistID & Right ( "0000" & InvoiceSequence ; 4 )

               Specify a Unique values validation field option on this field to guard against accidentally getting two invoices with the same invoice number. (This might happen if two users run this script at the same time for two invoices for the same therapist.)

          • 2. Re: Creating multiple invoice number sequences and create on commit/print
            fysio2008

                 Hi Phil.

                 Thank you for trying to help me out once again. It is very much appreciated.

                 Let me specify the steps I take:

                   
            •           Create new number field in Invoices Faktura called _fkTherapistID  _fkBehandlerID
            •      
            •           Create a new table occurrence from Invoices table called Invoices|SameTherapist Faktura|SammeBehandler
            •      
            •           Create a many-to-many relationship between the two tables  via _fkTherapistID  _fkBehandlerID (it seems on your listing that I should create a one-to-many relationship, but I guess its a minor mistake)
            •      
            •           Create new number field in Invoices called InvoiceSequence FakturaSekvens
            •      
            •           Create new calculation field in Invoices called InvoiceNumber FakturaNummer with your auto-enter calculation (I don´t quite get what you mean by "Specify a Unique values validation...."??)
            •      
            •           Create script as defined by you:

            If [ IsEmpty ( Faktura::FakturaNummer ) // no number has been previously assigned to this invoice

                 ]

            Set Field [ Faktura::FakturaSekvens ; Max ( Faktura|SammeBehandler::FakturaSekvens ) + 1 ]

                   
            •           End If

                  

                 Unfortunately I can´t get it to work.

                 When I run the script it creates an Invoice number: 00001 and repeats that every time I create a new invoice.

                 It is getting a little bit late here in Denmark, so I will try to have another look at it tommorow. If you have some comments, I would very much like to hear it.

                 Thank you for your help until now.

            • 3. Re: Creating multiple invoice number sequences and create on commit/print
              philmodjunk

                   What I described requires that there be a value in _fkBehandlerID that identifies the patient's therapist. That value should be copied from and ID field in the Therapists ("behandling"?) table. That assumes that you have one of the following relationships in order to copy that value into the invoices record via an auto-enter calculation:

                   Therapists::__pkTherapistID = Invoices::_fkTherapistID

                   or

                   Therapists::__pkTherapistsID = Patients::_fkTherapistsID
                   Patients::__pkPatientID = Invoices::_fkPatientID

                   But I do not see anything in your original screen shot that looks like that relationship. The one relationship you have between journal and behandling--if behandling is the therapists table, does not appear to be set up correctly as it shows a one to one relationship.

                   

                        it seems on your listing that I should create a one-to-many relationship,

                   Well it functions as a one to many relationship but since a foreign (not unique) key is used as a match field in both sides of the relationship, FileMaker will show the "crow's foot" connector on both sides.

                   

                        I don´t quite get what you mean by "Specify a Unique values validation...."

                   See the circled field option in the screen shot. You access the Options dialog by double clicking the field's field definition in Manage | Database.

              • 4. Re: Creating multiple invoice number sequences and create on commit/print
                fysio2008

                     Hi Phil.

                     Ah...I should have grapped that earlier....I did not create a table called "Therapists" Behandlere.

                     (the "Behandling" table is not "Therapists" table but "Treatment" table and related to a "medical record table")

                     Therefore there were no working relationship.

                     I have now created a table called "Therapists" Behandlere

                     In that table I have created a number field with auto-enter serial called "_pkTherapistID" _pkBehandlerID and also two text fields for first and last name.

                     I have then related Therapists::_pkTherapistID with Invoices::_fkTherapistID Behandlere::_pkBehandlerID ->Faktura::_fkBehandlerID

                     I have changed the  Invoices::InvoiceNumber Faktura::FakturaNummer to a text field (I originally created it as a calculation field. Thats why I wasn´t able to click the Unique Value tab under validation) and created the following calculation:

                     _fkBehandlerID  &  Right ("0000"  & FakturaSekvens ; 4)

                      

                     Unfortunately I still can´t get it to work (there is something about the setup with the accountname/user that I have not created right, I think)

                     Also when i create a new invoice, the invoice number FakturaNummer is constantly "Admin0000" and it asks me: "FakturaNummer" is defined to contain unique values only. Allow this duplicate value?

                     What Am I doing wrong?

                • 5. Re: Creating multiple invoice number sequences and create on commit/print
                  philmodjunk

                       The value in _fkBehandlerID won't magically appear on its own. Are you selecting a value in that field?

                       If you are, did you clear the "do not replace existing values" check box on your text field? (Which does not have to be text. It can be a number field.)

                  • 6. Re: Creating multiple invoice number sequences and create on commit/print
                    fysio2008

                         I tried using using Auto_Enter: Creation Account Name (As you maybe can se in the picture top right.

                         That obviously didn´t work, but I am struggeling to figure out how to get it right.

                         If I Auto_Enter the calculated value: Behandlere::_pkBehandlerID (Therpists::_pkTherapistID) and unclick the "Do not replace existing value of field" and run the script, I get nivoice number "000?" on all of the invoices.

                         I am still on my first month of using FileMaker, so I apologize in advance if I should be able to figure it out blush

                    • 7. Re: Creating multiple invoice number sequences and create on commit/print
                      fysio2008

                           I can pm you a link to the database file, if that is easier for you to help that way.

                           I really appriciate your effort!

                      • 8. Re: Creating multiple invoice number sequences and create on commit/print
                        philmodjunk

                             Auto-enter won't work in this case (I was referring to the field that uses an auto-enter calculation that combines therapist ID and sequence number not the therapist ID field.).

                             Instead, you'll need to select a therapist for each invoice. You can format _fkBehandlerID as a drop down list if you set it up with a value list that lists IDs from your Therapists table as "field 1" and their name field as "field 2".

                             Then you can manually select a therapist for every invoice. (Auto-enter could work via a link to patients once you have selected a patient for the invoice if you linked each patient to the therapist table.)

                        • 9. Re: Creating multiple invoice number sequences and create on commit/print
                          fysio2008

                               Ok..I think it is just too theoretical for me now.

                               Manually selecting the Therapist is not a possibility as it leaves a big risk for human mistake...and that will happen.

                               I was hoping to be able to set it up so that the logged in user/acount was automatically selected as Therapist.

                          • 10. Re: Creating multiple invoice number sequences and create on commit/print
                            philmodjunk

                                 That is indeed possible, but you'd need to describe what you have set up.

                                 Example:

                                 This script could run each time the file is opened by a user:

                                 Freeze Window
                                 Go to Layout ["Therapists" (Therapists)]
                                 Enter Find Mode[]
                                 Set field [ Therapists::AccountName ; Get ( AccountName ) ]
                                 Set Error Capture [on]
                                 Perform Find[]
                                 IF [ Get ( FoundCount ) = 1 // a record for this account name was found ]
                                    Set Variable [$$TherapistID ; Therapists::__pkTherapistsID ]
                                    Go to Layout ["specify the layout they should see when first opening your database here" ]
                                 Else
                                    Show Custom Dialog ["Log in error with account name data in Therapists table"]
                                 End If

                                 File Options from the file menu can set this script to run "on first window open".

                                 You can use Manage | Security to give each therapist their own account.

                                 Now, in INvoices, you can give the _fkTherapistID field this auto-enter calculation:

                                 $$TherapistID

                                 and it will auto-enter the value set by your start up script into each new invoice they create.

                                 But this could create problems if you have two or more therapists in the same office as it requires them to log in under their password before creating an invoice or human error will kick in here and one therapist will find that they just created an invoice assigned to the other due to forgetting to do this.

                                 It would be better, in my opinion, to make the _fkTherapist ID field a required field so that they get an error if they try to print the invoice and have not selected a therapist. There are also ways to limit the list of therapists to just those in a specific office.

                            • 11. Re: Creating multiple invoice number sequences and create on commit/print
                              fysio2008

                                   As for now there are only three therapists who are going to use the system, and each of the will use their separate iPad, so being logged in as the wrong therapist is not going to happen, I think. It is much easier for them to accidently tap on the wrong therapist in a dropdown list when they need to print an invoice.
                                    

                                   That is why I need it to be automatically.

                                   I allready have a start up script (and created the users in Manage | Security) and I will se if I am able to combine the two.

                                   Right now I am a little confused about which of the tables, fields, field settings and relationships (regarding the invoice part of my database) I have created actually works. That´s why I tried to describe it very thorough what I have done, step by step.

                              • 12. Re: Creating multiple invoice number sequences and create on commit/print
                                philmodjunk
                                     

                                          That´s why I tried to describe it very thorough what I have done, step by step.

                                     But since that wasn't going to work, I suggested an approach that would.

                                • 13. Re: Creating multiple invoice number sequences and create on commit/print
                                  fysio2008

                                       Ok...I am really trying to follow your suggestions, but as I mentioned earlier, I am still very new at FileMaker and it is still a little difficult for me to follow and understand.

                                       If I have offended you somehow, that was clearly not my intention.

                                        

                                       I am getting a little bit closer with the parts now.

                                       The script "on first window open" seems to work and I have managed to get it to create a InvoiceNumber based on the Account ID (first digit) and the calculation you created earlier.

                                  The problems remaining are:

                                       It does change the first digit in the invoice number dependend on who the user are. However it continues to create a 4 digit sequence no matter who is logged in:

                                       User 1: 200001, 200002

                                       User 3: 400003, 400004, 400005

                                       I hope you understand.

                                       I guess it has something to do with how I have set up Invoice::InvoiceSequence. Right now I have put it on Auto_Enter Serial number but that is probably what is causing the continues sequence. If you would be so kind to explain if this is causing the problem and how I could correct it, I would appriciate it very much.

                                       The second problem is that the invoice number is created when the record is created and not when I run the print script (you suggested earlier).

                                        

                                  • 14. Re: Creating multiple invoice number sequences and create on commit/print
                                    philmodjunk
                                         

                                              Right now I have put it on Auto_Enter Serial number but that is probably what is causing the continues sequence.

                                         Yes, that is the problem. my suggested approach does not use an auto-entered serial number for this reason. Instead, it uses the self join relationship to match to all other invoices linked to the same therapist and assigns the value of the maximum number in that set of related records plus 1 to get the next value in the series.

                                         I would also suggest that you not allow users to delete any invoices. Instead, add a means where they can mark an invoice void if one is created by mistake. That way, you can present any auditors with an uninterrupted sequence for each therapist.

                                    1 2 Previous Next