11 Replies Latest reply on Jun 9, 2009 8:16 AM by UncleGeo

    Auto enter serial number in external data source (MySQL) field

    UncleGeo

      Title

      Auto enter serial number in external data source (MySQL) field

      Post

      When I create a new record I'd like to have a serial number auto entered in an "item" field in the remote database table. It's a MySQL database running on my website's server. I do have the field enabled for this but it ain't happnin.

       

       

        • 1. Re: Auto enter serial number in external data source (MySQL) field
          kapitaen_1
            

          i had the same problem with sqlserver. after i marked the field in sqlserver as "identity" all problems between sqlserver and filemaker have been gone.

           

          but be sure not to let sql server or mysql AND filemaker having set to generate the serials. generating them on both sides will get big chaos. they only may be generatd on one side and all is well. (oops, is it possible to tell filemaker to generate serials for an external data source?) 

           

          greetings from germany

          chris 

          • 2. Re: Auto enter serial number in external data source (MySQL) field
            UncleGeo
              

            Kapitaen,

             

            Thanks for replying. There does not seem to be a setting for "identity" as a field type in MySQL (at least in MyPHPAdmin). I think you might mean that I use "item" as the relationship field -that I have already done. 

             

            FM lets me choose to auto enter a serial number in the remote field but it just doesn't auto enter it when I create a new record -it stays blank. I do not have it turned on for the local FM "item" field.

            • 3. Re: Auto enter serial number in external data source (MySQL) field
              kapitaen_1
                

              then try to set the mysql field as the primary key. if then the corresponding filemake field stays empty, then i can´t help anymore. sorry.

               

              greeetings from gemany

              chris

              • 4. Re: Auto enter serial number in external data source (MySQL) field
                mrvodka
                  

                I am unsure of what you mean... FileMaker can add calculation and summary fields to the existing ESS table but it can not add data fields. You will have to create the auto-increment field in MySQL by either using a GUI or ALTER sql statment.

                 

                 

                • 5. Re: Auto enter serial number in external data source (MySQL) field
                  UncleGeo
                    

                  Setting "item" as the primary key did not help.


                  Mr. Vodka, what I want to have happen is that when I create a new record I want the "item#" field in the remote MySQL database to have a new serial number autoentered in it.It looks like maybe you can only do this in a local table.

                   

                  It sounds like what you are saying is to not set this in Filemaker at all, but instead have MySQL auto enter a serial number when a new record is created. So would this mean that if I create a new record in Filemaker it would create a new record in the remote table, where MySQL would then make the serial number? I assume it would then report it back to me in FM either when I changed fields or on commit (which I take to mean when I leave the record)?

                   

                  Is this making sense?

                  • 6. Re: Auto enter serial number in external data source (MySQL) field
                    mrvodka
                      

                    If I am understanding you correctly then yes. The FileMaker shadow table will display the External mySQL table. If a new record is created, as long as you have an auto-increment field in the MySQL table, it will update each time you create a new record. However, depending on when the user was viewing FM's shadow table of your ESS, you may need to refresh and flush the external cache.

                     

                    I posted this link earlier on another thread. It may help you.


                    http://www.filemaker.com/downloads/pdf/public_techbrief_ess_en.pdf
                    • 7. Re: Auto enter serial number in external data source (MySQL) field
                      UncleGeo
                        

                      Wow, great article. I'm skimming it now but it looks like it will be very helpful to wrap my head around the concepts involved (important for a power user used to FM6 and who does something else for a living than make databases!).

                       

                      So this seems to be the solution to my question. I'll give it a try: before I do though, if I change the field to auto increment in MySQL (I'm using PHPMyAdmin) will it trash all the existing data in the "item" column? I'm guessing not, as it's a "behavior" of the field (but, as may already be obvious, I know only enough to get myself in big trouble). I guess that is really more of a MySQL question.

                       

                      Luckily I am the only user, sitting here all by myself talking to some server off in Utah where my website lives so I won't be having issues with others sharing the FM database. (That, though, means everything is my fault!)

                      • 8. Re: Auto enter serial number in external data source (MySQL) field
                        mrvodka
                          

                        Sorry I have never used that tool. I usually use Navicat and MySQLFront. I dont think it should blow away your current data but it never hurts to test it in a test file first. :-)

                         

                         

                        • 9. Re: Auto enter serial number in external data source (MySQL) field
                          UncleGeo
                            

                          I'll cross my fingers and let you know if I trash it! (I may consult some MySQL docs first.)

                           

                          Thanks!

                          • 10. Re: Auto enter serial number in external data source (MySQL) field
                            UncleGeo
                              

                            So, yup, you can set the field to auto increment and it does show in Filemaker. It started at 1 of course and it appeared to work.

                             

                            So I went into MtySQL with PHPMyAdmin and used an ALTER  command

                             

                            ALTER TABLE Items AUTO_INCREMENT=10523

                             

                            to change the next number to be what I wanted it to be and now it doesn't. I'm tired of farting with it for now. I'll report back after I've picked this up again with what I find out. No comments needed unless you want to.

                             

                             

                            • 11. Re: Auto enter serial number in external data source (MySQL) field
                              UncleGeo
                                

                              Gee I thought I sent a response but I don't see it...

                               

                              I set the MySQL field to auto increment and it did and it did not wreck any data. It showed up fine in FM.

                               

                              Then I set it to auto increment starting with a higher number and zip, nada. Didn't work anymore. Could not figure out why.

                               

                              For now I wrote a script that simply copies the item# I enter on the FM side and pastes it to the item# field in the MySQL table (It copies other fields too to finish out the new record.) This is a workable and perhaps better solution to my particular situation.

                               

                              I guess, even though I chose a different solution, I've learned that you can indeed set a MySQL field to auto increment when you make make new record (at least if you start from "1") -you just have to set that attribute it at the remote database field and then it will show up in FM.

                               

                              Lets call this baby solved!