Sync Problem: No error messages, but no records syncronized

[GisMo]

I've went through the setup and everything seems to be connected up correctly. When I sync the local file I get no errors, but no changes appear on the server. Also, If I change a record on the server, the change is not pulled into the local record.

 

I'm syncing a table Named "ES_Data". I've enabled the debugs in the Settings script, but I'm not sure what I'm looking for. Nothing really stands out other than the Dialog Box:

 

"Records From ES_Data"

?

 

It just shows a question mark. Digging a little deeper in the Push Payload Script:

$dyn_sql = SELECT 'ES_Data', '' || "__DataID"[1], '' || "NameFirst"[1], '' || "NameLast"[1], '' || "_UserID"[1], '' || "ES_Record_UUID"[1], '' || "ES_UTC_Time"[1], '' || "ES_Device_ID"[1], '' || "ES_Account"[1], '</rec>' FROM "ES_Data" WHERE ( ES_UTC_Time > 63556678260924 ) AND ( COALESCE ( ES_Exclude, 0 ) = 0 )

$dyn_esql = ExecuteSQL ( "SELECT 'ES_Data', '' || "__DataID"[1], '' || "NameFirst"[1], '' || "NameLast"[1], '' || "_UserID"[1], '' || "ES_Record_UUID"[1], '' || "ES_UTC_Time"[1], '' || "ES_Device_ID"[1], '' || "ES_Account"[1], '</rec>' FROM "ES_Data" WHERE ( ES_UTC_Time > 63556678260924 ) AND ( COALESCE ( ES_Exclude, 0 ) = 0 )"; "</fld>"; "" )

$temp_recs = evaluate($dyn_esql)

$temp_recs = ?

I tested the SQL statement and removed the WHERE conditions and I get all of the records..as expected. I wonder what's going on.

 

Anything stand out?

 

Thanks.

[RyanESmith7]

If you are testing on the same machine that you created the records from, then no records will sync because the Device ID will match. What I did to get around this was wrote script to update the Record_ID in all of my tables with a new UUID and the UTC time as well. This assuming that you're starting with a blank mobile database and are expecting it to sync all the data from your host.

 

You can find more info at the bottom of the Troubleshooting guide here:

http://fmeasysync.com/troubleshooting/

Oh, if this is the ONLY table you are sync'ing, you may be having a problem with a field name. Make sure none of your field names have the | character or the / character. Keep them to just letters and underscore.

[GisMo]

I think i got it to work! Thanks. I found that I had ES_Exclude as a Text field, instead of a Number, in my tables; I changed it to number, deleted all the records and started with an empty database on the server and mobile. Now the data is pushed to the server file. I am actually syncing two tables.

 

I'm not sure if this behavior is correct, but if I open the database on the server and modify a record, the changes aren't pulled down to the mobile... Would i need to manually change the device ID for it to sync down to the mobile?

 

Also, are there any mandatory user profiles/logins that are necessary for each file? For instance, the hosted database has an Admin[Full Access] login. And the Mobile files are data entry only. I'm using it with a Separation Model on the mobile. I know I'm being vague, but I didn't see anything really detailed about permissions/logins and was wondering if there is anything necessary here. When I'm syncing, it prompts for username/password. If I remove the password, it just syncs without any prompts. What's the right protocol here?

 

Also, confirming.. For field ES_Device_ID, "Do not replace existing value of field(if any)" be unchecked?

[RyanESmith7]

I personally have not messed with the DeviceID. Hopefully Tim will step in and answer your question. I do know that it is "by design" for it not to sync on the same machine as that can cause some "round trip" issues. What I do for testing purposes is update on my iPad and sync on my laptop. Once I'm ready to roll it out, I do the opposite, update records on my laptop and sync on my iPad.

[timdietrich]

As Ryan suggested, EasySync uses the "ES_Device_ID" field to keep track of the device that last updated a record, and it does so to prevent data from being "round-tripped." The topic is covered here: http://fmeasysync.com/troubleshooting/ (Scroll down to "What is the purpose of the ES_Device_ID field?")

 

You also asked, "Also, are there any mandatory user profiles/logins that are necessary for each file? For instance, the hosted database has an Admin[Full Access] login. And the Mobile files are data entry only."

 

When developing EasySync, I had two security-related goals: (1) make the process secure while (2) not imposing anything on the developer. So it's entirely up to you as to how you handle security.

 

That being said, my suggestion is to treat the sync process just like any other FileMaker solution. Assign individual accounts to each mobile user, require strong passwords, and give them only the privileges that they absolutely need to perform a sync. In doing so, you'll end up being able to track the user that has made changes, and you'll have the ability to sync only the data that a specific user actually needs.

 

Hope this helps!

 

-- Tim

[GisMo]

Thanks Ryan. I think I've sorted out most of the issues and it's working. My last hurdle was connecting the delete record script and test it all.

 

Tim,

 

Thanks for chiming in and I appreciate the response. I'd like to touch more on the security. I have the hosted database on the server with no password. I also have my local UI and Database files for the clients which have their own login system and passwords. This allows sync to happen without any prompts from the hosted database. I would like to password protect the hosted database, but this now prompts the user to enter the user/password for the hosted file when syncing. I'd like to avoid this, but also ensure the hosted database is password protected. I understand that if both files contain the same user/pass information, then FM will automatically connect but maintaining that relationship is not simple. Do you have any solutions? Is there a way to inject the User/Pass of the hosted database into the mobile file?

 

I suppose I could sync any new user information and then run a script periodically to scan for new users in the users table and then create logins for those users without passwords. If the users change their passwords on the local machine, I would need to find a way to send that back up to the hosted machine and update those passwords. Seems clunky.

 

Thanks

[timdietrich]

This worries me: "I have the hosted database on the server with no password." 

 

"I also have my local UI and Database files for the clients which have their own login system and passwords." Are you saying that you have developed your own process of authenticating users?

 

In any case, I don't have a good solution for you. One of the things that I'm considering is modifying EasySync so that it uses the "Fireball" technique that I blogged about here: http://timdietrich.me/blog/fireball-intro/ 

 

Doing so would significantly change the way that mobile users connect to the hosted database during the sync, and in theory, resolve some of the issues that we've run into while using Perform Script on Server. In your case, this would also provide you with a way to more easily pass credentials to the server. 

 

Again, this is just something I'm considering for a future version of EasySync. I have no firm plans, schedule, etc at this point.

 

-- Tim

[GisMo]

It also worries me... for now.

 

No, Filemaker does the authentication and I piggyback a Users table with some properties for each user. The user login is a field that I pickup upon login via get(AccountName) and store in the dashboard global field. Creating New users is scripted from the users table/layout.

 

Well, I just figured out one solution..I've modified my startup script...After my users login with their unique account in the mobile file and I get the correct user id via get(AccountName) and the User table, I then use the script-step "Re-Login" and use another general account with the same credentials of the Hosted file, now it's a seamless sync. At last, now my hosted file is password protected. This is simpler to maintain and more secure for my application for the time being. Any thoughts on this approach?