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
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.
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
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.
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?
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.
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!)
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. :-)
I'll cross my fingers and let you know if I trash it! (I may consult some MySQL docs first.)
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.
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!