Jump to content
Hampden Tech

Primary key issue in HUB using SQL Data

Recommended Posts

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.

Share this post


Link to post
Share on other sites

This is only the second time I've ever come across this issue, but fortunately we solved it for somebody before you, so you can use the same solution :-)

In the screen where you select the primary key, check the box that says 'Custom PK.' Whatever a new record is being inserted into SQL Server, this SQL statement will be executed, and the result will be used as the value for the primary key.

Use the MirrorSync-managed primary key option in this case. That will make it so that the same record can have different primary keys for different devices / databases, and MirrorSync will keep track of that.

Share this post


Link to post
Share on other sites

Thanks for the quick reply Jesse.

I just checked this out. Is that an open SQL query where any SQL can be executed? Can you give me an example of what type of SQL statement you could use there?

Thanks!

Bob

 

Share this post


Link to post
Share on other sites

Here's an example from another customer. I'm not an expert at SQL Server syntax, but I think anything that ends with a SELECT statement will work:

DECLARE @newaccountid char(12)
EXEC [sysdba].[GenerateSLXId]  3, @newaccountid OUTPUT;
SELECT @newaccountid;

Share this post


Link to post
Share on other sites

Outstanding! That worked like a charm.

Thanks for the help!

  • Like 1

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


  • Who Viewed the Topic

    9 members have viewed this topic:
    ddinisco  Josh Ormond  BruceR  Lee Smith  doughemi  MikeKD  Ocean West  bcooney  LaRetta 
  • Similar Content

    • By Hampden Tech
      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!
    • 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.