Dynamic Calculation for ES_Exclude

[Leo Di Croce]

Hi,

 

I've spent several hours trying to debug this one. At first I thought it was because I was running the hosted and local files on the same machine. But the problem persists even after having moved the hosted file to another machine.

 

The Goal:

Exclude User records — that don’t match the “System” User ID — from being pulled.

 

Background:

The local file contains a System table and a User table. The System table has a User ID global field. The User table has a User ID key field.

 

The hosted file also contains a User table with a User ID key field.

 

What works:

Hardcoded calculation for ES_Exclude on hosted file. For example, ES_Exclude = not ( User::ID_User = “C659FBEF-1C4F-49A5-9551-923F7D96A71B” )

 

What doesn’t work:

Dynamic calculation for ES_Exclude on hosted file. For example, ES_Exclude = not ( ID_User = $$client_UserID )

 

The setup:

1. EasySync Settings on local file - sets variable equal to User ID. That is, EasySync Settings script step (approx line 32):

Set Variable $$additional_pull_info = System::ID_User.

2. Prepare Payload for Client on hosted file - sets global variable for User ID. That is, Prepare Payload for Client script step ADDED after (approx line 25):

Set Variable $additional_settings = GetValue ( Get ( ScriptParameter ); 12 )

Set Variable $$client_UserID = $additional_settings

3. ES_Exclude field in hosted User table changed to calculation: not ( ID_User = $$client_UserID )

 

The result:

No records are pulled using dynamic calculation for ES_Exclude.

 

Expected result:

One record pulled matching $$client_UserID .

 

Any suggestions are much appreciated.

 

Thanks,

=L=

[timdietrich]

Leo --

 

Have you configured ES_Exclude as an unstored calculation?

 

-- Tim

[Leo Di Croce]

Hi Tim,

 

Yes, ES_Exclude is an unstored calculation.

 

=L=

[bcooney]

Rather than using ES_Exclude, you could incorporate $additional_settings in the Set Variable $dyn_sql step in the Prepare Payload for Client:

"SELECT " & 
$dyn_sql & ", '" & $$record_delimiter & "'" &
" FROM "" & $sync_table & """ & 
" WHERE " &


// Exclude records flagged for sync exclusion.
"( COALESCE ( ES_Exclude, 0 ) = 0 ) " &

"AND ( sync_group_id = " & $additional_settings & " ) " & 

// If the client is merging new/updated data with data already on the device...
// Only include records that have been added/updated since the last pull...
// And only include data that they did not just push (i.e. no "round tripping!")...
If ( ( $$sync_method = "Merge" ) and ( $last_pull_utc > 0 );  "AND ( ES_UTC_Time > " & $last_pull_utc & " ) " &  "AND ( COALESCE ( ES_Device_ID, 'X' ) <> '" & $client_persistent_id & "') "; "" )

[Leo Di Croce]

Thank you. Unfortunately, the changes you suggested for the Set Variable $dyn_sql (reference your line 10) result in "?" later, when script step evaluates the ExecuteSQL statement (reference $temp_recs in Prepare Payload for Client script).

[Leo Di Croce]

***UPDATE***

The problem appears to be Sync Check, which deletes the User record.

I stepped through the sync process again (this time more fervently) using the unstored dynamic calculation for ES_Exclude.

All appeared to be OK... until Sync Check.

During the pull process, everything was cool. I checked and confirmed that the User record was created on the Mobile file. (Yay!)

But then Sync Check deleted the record. (Boo!)

When the Sync Check script compared Mobile with Hosted UUIDs, the $diff showed one UUID (approx line 60), representing the one User record that the pull process created on the Mobile file. Then the Sync Check script proceeded to delete the User record.

How can I stop this from happening?

Strange that there is no $diff when the calculation is "hardcoded".

Thanks,
=L=

[timdietrich]

Leo --

 

Just as the pull phase supports additional params ($$additional_pull_info), the "sync check" phase does as well. You can specify those params using "$$additional_sync_check_info" and I think in your case you would want to set it to: System::ID_User

 

Would you give that a try and see if it helps?

 

-- Tim

[Leo Di Croce]

Hi Tim,

 

It works! Thank you for the advice. The $$additional_sync_check_info did the trick.

 

You made my weekend!

 

Thanks again,

=L=

[timdietrich]

Leo --

 

That's great news! 

 

I'm glad it's working.

 

-- Tim