13 Replies Latest reply on Apr 14, 2016 10:26 AM by ResoluteSystems

    Help wil creating records in two databases via one script



      I have been asked to create a solution where an Invoice can be created in a new system from a job in a previous system.

      A little bit of background. I am relatively new at filemaker and this system I am working on was not created by me. At the end of the final year a copy is taken of the old system and a new file is created from it - all jobs on the system are removed so the job numbers start at the next point (for instance last job on 2015 would be 235 the first on 2016 would be 236). I have been asked to create a way so that when a button is clicked an invoice number for a 2015 job is created in the 2016 file from which that invoice would be able to found.

      Below I have attached a graph of the database relationship graph  - this would be the same for both databases currently as they are technically duplicates of each other. The invoice number is generated through a script the number is created in the invoice numbers table and then pasted into the actual invoice layout.

      Is there a way I can create a new invoice number in the 2016 instance of the invoice number table? and create a find- able invoice without creating a new 'job' file ?  I am very much in the weeds on this I'm afraid just looking to be pointed in the right direction.

      RElationships 1.PNG

        • 1. Re: Help wil creating records in two databases via one script

          Are there any benefits in your current practice of resuming work with an empty shell? Why not create a backup and continue using the database as it is? I cannot imagine that you do this for storage saving purposes …?!


          Having said that, you could create a link (an “External Data Source”) that points to the backup (i.e. the old version), add a TO of the Job table to your Relationship Graph and use this for a relationship like Some2016Table::newJobID = JobsFromBackupfile_Jobs::jobID, or simply add a layout to use that table directly.


          Again, I would reconsider your current process.

          • 2. Re: Help wil creating records in two databases via one script

            The purpose of creating a new system is so that all jobs booked on and invoices created after the new year are contained on a new separate database. This is not something I can change as this is how my boss prefers it I'm afraid.

            I have made this relationship but the script to create a new invoice number is as follows - I would need this to create a new record on the invoice number table in the 2016 file. At the moment it only adds them to the current file (2015).

            New Invoice Number script.PNG

            Apologies if this seems a bit dense

            • 3. Re: Help wil creating records in two databases via one script

              OK, can you repeat (re-formulate) this, in simpler words?


              The scenario: you have recently created the backup (say 2015), and are in the “new” / current database (let's call it 2016) that has all the old contents, excepts for jobs and invoices; they have all been deleted.


              Now …


              You want to create a 2015-style jobNumber (or probably more precisely: “create” an existing 2015 number) to find / lookup a 2015 job?

              Or do you want to create a “new” job in the backup (2015) job table (because it has been forgotten, and you want to put it into the “right” place)?


              Or … ?

              • 4. Re: Help wil creating records in two databases via one script

                Learn to use variables and set field, not copy/paste, not insert.

                • 5. Re: Help wil creating records in two databases via one script

                  BruceRobertson wrote:

                  Learn to use variables and set field […]


                  I was getting to that, Bruce … baby steps.

                  • 6. Re: Help wil creating records in two databases via one script

                    Ok I will try to explain a little clearer.


                    There are currently 300 odd jobs waiting to be invoiced on the 2015 database.  At the beginning of the year an empty copy of the database was created for the 2016 file (meaning that job numbers continue along in the chain).

                    What my boss is asking for is that when an invoice is created for a job on the 2015 system an invoice number and invoice is created on the 2016 database.

                    I would like new invoice numbers to be created in the 2016 database from the 2015 database and a record created in the 2016 invoices table with the information from the 2015 file so that it may be included in searches made for unpaid invoices etc. This invoice material would them be displayed on both databases, however it would be preferable if this can be done without creating a new jobsheet in the 2016 file.


                    Bruce - I wouldn't normally use cut/paste so extensively but this is the script as it was set up by the person who actually created the original file. I would prefer it to be a little more streamlined but am loathe to do anything that may render something further in the database useless or broken as this is the main system used for the company.

                    • 7. Re: Help wil creating records in two databases via one script

                      Not meaning to offend anyone but it appears the original creator of the database didn't know what they were doing either.  


                      I agree with erolst, you need to reconsider your design.    There is no reason for a backup copy to be used.   You can limit records by found set and or by relationship.    A script button can be created to purge old paid records after a number of years.  


                      It appears you may need to hirer a professional to design a system.    There are several free templates available on the internet.  FM comes with a starter Invoice template.   

                      • 8. Re: Help wil creating records in two databases via one script

                        That would be what I suggest too as I am very novice at this however my boss would not scrap a system which works for her. There is no recourse for changing the system now and so I have to work with what I have got.


                        I have managed to script for an invoice number to be created in the new file from a button on the 2015 . My issue now is how to present these invoices on both systems without having to create a new jobsheet for the invoice .

                        • 9. Re: Help wil creating records in two databases via one script

                          Heres what to do, in detail which makes it long. Once you get the idea it’s much easier than the length of the description implies.

                          I’m assuming that there’s no problem about creating a record in the starting Table, using the Script Step New Record. The problem seems to be about creating records in other tables from the starting table.

                          Table A = where you start

                          Table B = target table for creating a new record

                          Fields used for relationships are named in CAPS


                          Set out below is a basic technique for creating one related record in another table, which seems to be what’s required. Before you use it, check to make sure that it will never be necessary for TableA to create more than one record in TableB. If you need more than one record in TableB, first get your head round creating one, then check out how to create multiple records in TableB.


                          To Create One Record



                          First, in Table A, ensure you have an automatically serial numbered, unique, not empty, Number field called RECORDNUMBER.

                          If not, check how TableA records are created to decide whether these records should be serial numbered at Create or at Commit. (Don’t worry about wasting record numbers by cancelling creation of TableA records that were numbered at Creation then cancelled.) Then create TableA::RECORDNUMBER (Serial numbered, Unique, Strict numeric, Not empty) and serial number all the existing records in creation order (Use ShowAllRecords, Replace - serial numbers ).


                          In Table B, add two fields:

                               A Number field called TABLEACREATERECORD (Strict numeric, Not empty)

                               A Number field called TableARecordNumberTrigger


                          Create a relationship called TableACreatesTableBRecord. It joins TableA and TableB with TableA::RECORDNUMBER = TableB::TABLEACREATERECORD. Allow the relationship to create records in Table B.


                          In a script in TableA, include the Script Steps:

                          SetField ( TableB::TableARecordNumberTrigger ; TableA::RECORDNUMBER )

                          CommitRecord ( Skip validation ; No dialogue ; Force commit )


                          At the first script step, the relationship creates the new record in TableB and enters TableA::RECORDNUMBER in TableB::TABLEACREATERECORD. The Commit script step is to make absolutely sure that the new record is recognised everywhere.


                          use this relationship only to create records in TableB. To use the TableB record from TableA for other purposes, create another relationship joining TableA::RECORDNUMBER = TableB::TABLEACREATERECORD. This is the same relationship, but its purpose is to allow you to change the create and use relationships independently in the future.



                          To Create Multiple Records



                          The technique above creates one record in TableB because creation is triggered by the fact that the related record in TableB is missing when it’s used to post data in TableB::TableARecordNumberTrigger. Once it exists, the script steps won’t create it again and will simply overwrite TableB::TableARecordNumberTrigger with another instance of the same TableA::RECORDNUMBER.


                          To create multiple records in TableB it’s necessary to add another pair of key fields to the relationship.


                          In TableA, add a Number field called TABLEA::TABLEBSERIALNUMBER (Auto-Enter Data = 1, Strict numeric, Not empty).


                          In Table B, add a Number field called TABLEB::TABLEASERIALNUMBER  (Strict numeric, Not empty).


                          In the relationship described above, add another pair of join fields TABLEA::TABLEBSERIALNUMBER = TABLEB::TABLEASERIALNUMBER.


                          In Table A, use these script steps, replacing those shown above:

                          SetField ( TableB::TableARecordNumberTrigger ; TableA::RECORDNUMBER )


                          CommitRecord ( Skip validation ; No dialogue ; Force commit )


                          This will create a series of records in TableB, one for each value in TABLEA::TABLEBSERIALNUMBER as it value increments after each record is created.


                          Also, create another relationship for purposes other than creating records, even if the join fields are the same.


                          I’ve typed this out without checking and testing as I went. So please bear in mind it may include brain malfunctions! If I’ve confused the issue, just let me know.


                          Good luck.

                          • 10. Re: Help wil creating records in two databases via one script

                            You may be surprised at what you can find for free on the internet.  Such as FM Starting Point - Free FileMaker Template by RCC   There may be more free templates  related to the industry you are in, you would have to google / bing.   Several of the free templates can be modified.

                            • 11. Re: Help wil creating records in two databases via one script

                              " however my boss would not scrap a system which works for her."


                              I understand that you may have adapted to using your existing system, and that the problem of migrating data and retraining might be complex.


                              You don't so much need to scrap a system as to scrap a PRACTICE in using the system.

                              • 12. Re: Help wil creating records in two databases via one script

                                I would also point out that the system isn't working or there wouldn't be the original request!

                                • 13. Re: Help wil creating records in two databases via one script

                                  The original request was about how to do something practical in the circumstances FM_DM has now. See here:

                                  Only Me! Harry Enfield and Chums - BBC comedy - YouTube