Jump to content
Hampden Tech

Error on insert with custom PK

Recommended Posts

I am seeing an error after syncing when I have a new record on the spoke (FileMaker DB) to be synced into a Microsoft SQL server table on the hub. The MirrorSync setup uses a custom primary key to do the insert into the SQL table.

According to the sync window as well as the sync log, the insert fails. However, when I review the table in SQL Server on the hub, the record has indeed been inserted properly. For some reason, MirrorSync is reporting this as an error. In addition, MirrorSync does not seem to remember the PK of the newly inserted hub record so if I do another sync on the spoke, it inserts it again. If I run the sync 3 times, I will have 3 new records inserted.

This is the error that I see in the log. No further information is available.

table Hub node MS SQL Server/KeyPhrases failed for source nodeId '275'

This also happens on another table as well.

Is this a bug in version 4 of MirrorSync or is there some configuration step that is missing?

Any help would be greatly appreciated, as this is preventing us from releasing this solution to production.

Thanks!

Share this post


Link to post
Share on other sites
Jesse Barnum    51

I will need to see the log to make a guess as to what is happening. Could you repeat the problem and then submit a problem report from the mirrorsync launch page? This will attach the server log file. 

Share this post


Link to post
Share on other sites

Hi Jesse,

Okay, I have submitted the ticket from the MirrorSync launch page with information about the issue. The ticket is HD-37158.

Thanks!

Bob

Share this post


Link to post
Share on other sites

Hi Jesse,

Yes, I got your response. I'm checking on it right now.

There is a column in the SQL table that is being inserted as a null. It is a nullable integer so NULL is a perfectly valid entry in SQL, albeit not the best approach. I'm thinking that in the select statement, the null is throwing off MIrrorSync. I have added this field to the sync layout and will make sure it is populated with an integer in the FileMaker spoke to see if that does the trick. I'll let you know.

Thanks for your prompt response to this!

Share this post


Link to post
Share on other sites

Hi Jesse,

Any updates on this issue?

Thanks!

Bob

 

Share this post


Link to post
Share on other sites

I'm just following up to see if there are any updates on this issue.

If this is a bug, are there any workarounds?

At this point, our project is on hold because we can't release this if we are unable to create new records on the FileMaker Go spoke up and then sync with the MS SQL table on the hub.

Thanks!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


  • Similar Content

    • By Hampden Tech
      I have a situation that is causing me some issues. In my MirrorSync configuration I am able to download data from the HUD, make changes in the spoke DB and then sync back up and the changes are properly reflected in the HUB (Microsoft SQL) database.
      However, I tried to add a new record on the spoke DB and when I did a sync operation, the error on the HUB indicated that the primary key field could not be null.
      We manage the primary keys in our SQL database ourselves, so the next number for each table is stored and updated within the DB. MirrorSync must be expecting that our HUB table is using an Identify column to set the primary key. There is no way that I see of specifying the primary key value in the HUB database. In other words, if I could match up a value in the spoke DB with the primary key and then insert it, I could use an insert trigger on the HUB database to catch this and then assign it a new number in sequence. For example, I could setup a new field in the table called "Temp PK" and set it to some magic number like "999916". Then, I can trap for this on an insert trigger in SQL and assign it the correct way. The only issue that I see with this is that
      1. I'm not sure how MirrorSync will be able to match up this new HUB PK with the Spoke PK, and
      2. I'm not sure if there would be any conflict issues with multiple users syncing at the same time
       
      Any advice on how to work around this would be helpful.
    • By VictorLion
      I am building a customer database. The database will have a primary key. I am looking to make the primary key either a unique integer or a UUID (with 32 chars). The UUID appeals to me due to its ability to sync tables. The primary key will be used for (obviously):
      Searching and sorting records As a tag against documents eg document XX is linked to customer AA, BB and CC; and  A foreign key to link tables My questions are:
      Do UUIDs adversely affect database size Do UUIDs adversely affect speed If UUIDs retain their full 32 chars, then there will obviously be a disadvantage.
      However, is FM clever enough to convert the UUID "under the hood" (such as to an integer referenced index) to improve database speed and/or size.
    • By Luke Cottingham
      Hiya,
      1st of all - I have personally refrained from posting for help on a forum as I wanted to try and read, learn and solve my problems using trial and error! I am learning the FMP platform from scratch and am currently in week 2 of it.
      My question relates to a file I have attached to this post - the products area.
      For each product I have a number of 'size options' within it and each with their own unique SKU (stock keeping unit). Each SKU needs its own barcode. My job now is to produce a method to easily assign an 'available' barcode to a specific SKU.
      Barcodes are purchased from GS1 and are globally unique. I have a table hosting a pool of barcodes that I have available to me. They can only be assigned to one SKU and once assigned, needs to be removed from the available selection of barcodes in this pool.
      Issues
      I wanted to set a placeholder text for 'EAN-13' field in the product layout (this is the barcode). This field is a button! When clicked it can perform a script to open a popover or window containing all barcodes that do not have a 'ProductOptionID' assigned to it.
      How do I structure this script? Click the EAN field button - this sets the 'ProductOptionID' variable globally (is this correct to do) - [$$productOptionID] The script then opens the object 'Barcodes'. This is a popover button with a portal inside filtering all barcodes from the pool that do NOT have a productOptionID value in the foreign key field The script must then pause whilst I manually select a barcode and set a $BarcodeID variable? Or should I script it to pick a barcode at random from the pool of available barcodes (i've already set a script to give available a figure of 1 and unavailable a figure of 0) Then the script must put the BarcodeID variable in the product option record Is the above the right way to go about it? I also need a way to un-assign a barcode from a product and therefore remove the value from the product option id field in the barcode pool I have one more query. I have a relationship between Product Option table and the Barcode Pool. This relationship is O2M the barcodeIDpk with a lookup for the actual EAN (barcode) value on the Product Option table. When I set a barcode ID in the Product Option table - shouldn't the ProductOptionIDfk field update in the Barcode pool with the ID of the product option that the barcode is assigned to??
      I am convinced I had it working earlier - but not anymore and cannot see to figure out why?
      See my relationship graph below and the solution attached.
      Thanks,
      Luke
      CRM Trial barcode.fmp12

    • By Jason Mundok
      I'm setting up a new MS configuration (v. 2.109) with about 12 tables. All tables have the primary key and creation/modification dates on special MS layouts as required. When I get to the screen that allows me to define the primary key field for the tables, the key field for one particular table is not appearing in the drop down list. I've tried removing it and re-adding it to the layout with no effect.
       
      I noticed that there are far fewer fields in that drop down list than exist in the table. What are the criteria for a field to be available in the list?
       
      Thanks,
      Jason
    • By ron G
      I have a simple membership app whereby I have
      Members Table -<< Awards
      Members Table -<< Dues
      etc...
       
      This project has been going on for over 18 months and I have a couple of users who have 'cross linked' their primary keys.  That is, I have placed a read only copy of the Primary Key on the Members layout and I can see that there are sometimes 2 members with the same Primary Key.  Weird. 
       
      (The PK is Auto Enter, Serial Number, X Prohibit Modification During Entry)
       
      Anyway, as I release current versions of my app, the users import the previous version.  This is scripted and it works well.
       
      I would like to somehow cause each imported Member record to generate a 'new' PKey  and have each 'Many table' Foreign Key receive that Primary Key.  But, I can't even come up with the paradime of how to do that.
       
      I sure would appreciate some guidance from my more experienced fellow FM programmers.
       
      Thank you
       
      Ron
×

Important Information

By using this site, you agree to our Terms of Use.