Jump to content

Conditional sync with Ipad


siroos12
 Share

This topic is 2285 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

 

 

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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? 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 2 weeks later...
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 by Joshua Willing Halpern
Link to comment
Share on other sites

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 ).

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Joshua Willing Halpern
Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 2285 days old. Please don't post here. Open a new topic instead.

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
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.