I could use another set of eyes on a problem I just discovered.

I have a table, call it "Tenants" to keep it simple. On the HUB (a SQL Server DB) the primary key is pTenantID. On the FileMaker spoke the primary key is pk_TenantID.

I have another table "WorkOrder" on both the hub and spoke which contains as a foreign key field, fk_TenantID to the Tenants table.

When I do a sync to the spoke, all tenant records come over and are assigned PK's on the spoke. If a Work Order exists on the HUB, the corresponding tenant is properly assigned on the spoke, so if I view it on the FM database, the correct tenant is shown.

However, if I create a new work order record on the spoke, select a valid tenant from the tenant's table and then sync, the tenant assigned in the HUB database is the first tenant record chronologically. In other words, it does not match up the selected tenant PK from the spoke/FM databae with the corresponding Hub/SQL table.

At first, I thought it may be a config problem, but I went through the config and the correct relationship is defined and the fields are matched up properly. I can't figure out why MirrorSync would assign the wrong tenant ID when the new work order is synced to the HUB.

 

Is it possible that you have the data types mixes up in your key fields? What style of primary keys are you using, and what is the data type for those fields in both FileMaker and SQL Server?

The primary key in the FM spoke is an auto-assign number and the primary key in the HUB (SQL Table) is an integer.

I checked the config again and the columns seem to be mapped correctly. In addition, each tenant has a numeric account number. Through the debugger I was able to determine that the correct account number was assigned to the AccountNo field in the WorkOrder table on the FM/Spoke. When I synced, the matching column/field in the HUB record showed a zero for this value.

Attached is the configuration for the WorkOrder table.

Those fields shouldn't be orange... can you hover over one of them with your mouse and see if there is a tooltip message?

The tooltip says that the HUB field cannot accept nulls but the Spoke (FM) database does allow nulls.

However, all of these entries are supplied default values in the spoke table before syncing. If I'm not mistaken, any attempt to insert a record with null values in non-null columns prevents the record from being updated. The insert is taking place, but some of the values are not being updated.

OK, I would recommend setting those values in the spoke (FM) database to validate that they are not empty, that will at least stop MirrorSync from complaining about that.

Regarding the foreign keys not working, I'm going to need to see the log files to help with this. To do that please:

1) Modify a record in the spoke to change its foreign key of which record it points to

2) Assuming that fails, go to the MirrorSync launch page in your browser and click the 'Send problem report and log files' link

3) In the problem report page, please be sure to specify the primary key of the child record you changed, the name of the table, the name of the foreign key field, the value you entered in the spoke, and the value that was actually stored in the hub.

That will send me the MirrorSync log files, and I can review what happened during the sync to try to figure out why it's setting the wrong value.

Oh, one last thing, please reply back here in FMForums with your ticket number so that I can find it easily.

Okay, the bug report has been sent.

The strange thing is that regardless of which tenant I select on the spoke, the same tenant is always identified on the HUB, and this is not the first tenant chronologically.