Conditional sync with Ipad

[siroos12]

Good day all,

 

I checked this :

But I did not get my answer yet. 

I have 2 different tables on my iPad file. The first one pushes only to server, the second one push/pull  to/from server.

What I need to do is to limit the second table to only pull relevant records from server to iPad based on the account name used for logging in on ipad. So if I log in on iPAd, then I should be able to only see my records pulled from server and not others records.

My question is how do I modify "WHERE" clause on the script step that sets $dyn_sql to include this filtering criteria based on account name?

Unfortunately I know nothing about SQL

 

I appreciate any advise.

 

Warm regards.

 

 

[siroos12]

Any advise would be highly appreciated folks.

 

Thanks.

[RyanESmith7]

Hey siroos,

Yes, this can be done using the ES_Exclude flag, at least that is how I do it. Change it to a calculated field for the table you want to limit and put your calculation in there. 

You should not have to modify any SQL for this. 

Ryan

 

[siroos12]

1 minute ago, RyanESmith7 said:

Hey siroos,

Yes, this can be done using the ES_Exclude flag, at least that is how I do it. Change it to a calculated field for the table you want to limit and put your calculation in there. 

You should not have to modify any SQL for this. 

Ryan

 

Hi Rayn,

 

The problem is there are 30 iPads syncyng and I want the iPads to only sync relative data to the account name logged in to iPad file. Flagging the records on hosted file wouldn't make conflicts?

For example, if user A is syncing, it needs to flag out all the records which has different account name as user A. If another user sync at the same time, then it might make conflicts, am I right?

[RyanESmith7]

Without understanding your data, exactly, I don't think that there would be a conflict as long as the records are marked somehow with the account name. It's definitely worth a test and I use ES_Exclude a lot to keep the size of data in my mobile file small. 

How do you determine which record belongs to which account? 

[siroos12]

ES_Account name stores the account name of the user created/synced it.

19 hours ago, RyanESmith7 said:

Without understanding your data, exactly, I don't think that there would be a conflict as long as the records are marked somehow with the account name. It's definitely worth a test and I use ES_Exclude a lot to keep the size of data in my mobile file small. 

How do you determine which record belongs to which account? 

ES_Account name stores the account name of the user created/synced it.

[RyanESmith7]

That's not what I meant (because that is actually set by who last modified the record). I guess I could help you a lot more if I understood your data a little better. Maybe you could give us an idea of what your table looks like?

[siroos12]

OK,

On the iPad file, "Users X" makes records for each product and add its data into the record (Table A), then they push it to hosted file. 

 

The pushed data will be synced with the same table (Table A) on the hosted database. Then our product line manager checks to make sure that all the data is correct, after that, he press "Confirm" button which runs a script to move the record from Table A to Table B. 

 

Now what I need to do is to pull data from Table B to Ipad Table B, but only those records which are made by "User X" needs to be pulled to iPad file.

Please note that many users can sync at the same time.

Hope it makes scene.

[Joshua Willing Halpern]

On 2/23/2016 at 2:37 AM, siroos12 said:

Good day all,

 

I checked this :

But I did not get my answer yet. 

I have 2 different tables on my iPad file. The first one pushes only to server, the second one push/pull  to/from server.

What I need to do is to limit the second table to only pull relevant records from server to iPad based on the account name used for logging in on ipad. So if I log in on iPAd, then I should be able to only see my records pulled from server and not others records.

My question is how do I modify "WHERE" clause on the script step that sets $dyn_sql to include this filtering criteria based on account name?

Unfortunately I know nothing about SQL

 

I appreciate any advise.

 

Warm regards.

 

 

You can add an additional WHERE clause to the $dyn_sql variable. Looking at your file, I see a UserCreated field. For this you should create a new field 'AccountCreated' because the user name can change, but their account name won't.

• First, include the user account name in the $$additional_pull_info variable in the client 'Pull Payload' script.  
• Next, parse out that account name from the $additional_settings variable in the server's 'Prepare Payload For Client' script and save it to a new variable, (e.g. $creationAccount)
• To modify the $dyn_sql clause just add the following to the end:
  •  & "AND WHERE AccountCreated = '" & $creationAccount & "'"

This should filter out any records not created by the user who is trying to sync. I don't think ES_Account will work because that field updates whenever a user modifies the record. So if another user modifies the record on the host, it wouldn't get pulled back down to the user who created it, hence the new 'AccountCreated' field.

 

Hope that helps.

Edited March 22, 2016 by Joshua Willing Halpern

[siroos12]

Joshua, that was my opinion too about ES_Exclude. 

Thanks a lot for your help. I will apply your suggestion and will get back to you soon.

 

Regards,

 

Siroos

[Joshua Willing Halpern]

4 minutes ago, siroos12 said:

Joshua, that was my opinion too about ES_Exclude. 

Thanks a lot for your help. I will apply your suggestion and will get back to you soon.

 

Regards,

 

Siroos

Hey, maybe you misread my comment because I didn't say anything about ES_Exclude. In fact I think you could definitely use ES_Exclude as an alternative. In fact, it may be a good option.

You could set ES_Exclude to an unstored calculation such as :

"If ( <AccountCreated> ≠ Get ( AccountName ) ; 1 ; "" )" 

This will flag any records that weren't created by the user with a 1 for exclusion. ES_Exclude can be a very powerful tool if used creatively.

Also, I just realized that with my previous suggestion, you may not even need to include the user's account name in the $addional_pull_info and $additional_settings variables. You can probably just use Get ( AccountName ).

[siroos12]

3 minutes ago, Joshua Willing Halpern said:

Hey, maybe you misread my comment because I didn't say anything about ES_Exclude. In fact I think you could definitely use ES_Exclude as an alternative. In fact, it may be a good option.

You could set ES_Exclude to an unstored calculation such as :

"If ( <AccountCreated> ≠ Get ( AccountName ) ; 1 ; "" )" 

This will flag any records that weren't created by the user with a 1 for exclusion. ES_Exclude can be a very powerful tool if used creatively.

Also, I just realized that with my previous suggestion, you may not even need to include the user's account name in the $addional_pull_info and $additional_settings variables. You can probably just use Get ( AccountName ).

Thanks mate, I would rather to go for your first suggestion first than adding a calc field into hosted file. Let me try it first. Thanks a mill.

[Joshua Willing Halpern]

7 minutes ago, siroos12 said:

Thanks mate, I would rather to go for your first suggestion first than adding a calc field into hosted file. Let me try it first. Thanks a mill.

No problem, I was just pointing out that the other commenters' suggestions were conceptually valid.

Before messing with the '$additional' variables try adding this clause first:

 & " AND WHERE AccountCreated = '" & Get(AccountName) & "'"

I think you need to make sure the accountName ends up in single quote marks.

Edited March 22, 2016 by Joshua Willing Halpern

[Joshua Willing Halpern]

On 3/22/2016 at 0:26 PM, Joshua Willing Halpern said:

& " AND WHERE AccountCreated = '" & Get(AccountName) & "'"

This might need to be 

 & " AND AccountCreated = '" & Get(AccountName) & "'"

without the extra WHERE.

[siroos12]

I did try both below with no success:

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

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

// 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 & "') "; "" )
& " AND We buy cars::AccountName = '" & Get(AccountName) & "'"

 

 

"SELECT " & 
$dyn_sql & ", '" & $$record_delimiter & "'" &
" FROM \\\"" & $sync_table & "\\\"" & 
" AND We buy cars::AccountName = '" & Get(AccountName) & "'" &

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

// 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 & "') "; "" )

Sorry if I am making simple stupid mistakes here, I just know nothing about SQL 

 

Thanks,

 

Siroos

[Joshua Willing Halpern]

2 hours ago, siroos12 said:

try this instead. Don't use the <tablename>:: when referencing the field. Put it at the very end like in your first example.

& " AND AccountName = '" & Get(AccountName) & "'"

Edited March 26, 2016 by Joshua Willing Halpern