View Source

1. Current Gap

Laptops issued but not configured. No VAN ID assigned. No beneficiary data available locally. Field tracking cannot begin.

2. What We Need

Each laptop configured with its correct VAN ID, then synced with the central DB so local records are ready for offline field use.

End-to-End Flow:

Define Van Structure -> Create VANs in central DB, Map Tab logins with VAN -> Allocate Beneficiaries to Vans -> Central DB (all ben) -> Down-sync (filter by vanid) -> Laptop Local DB (van-specific records) -> Field worker tracks beneficiaries

3. Planning Steps:

Phase 1 - Define VAN Structure

Decide total no. of VANs and names

List all field locations and corresponding logins

Get the Beneficiary count per tab login

Finalize the Location -> Login -> VAN allocation

Phase 2 - Central DB Setup

Create all VAN records in the central

Map each tab login to its VAN ID

Verify all the logins are mapped to a VAN

Phase 3 - Beneficiary Allocation

Beneficiary record - look at “CreatedBy” - assign the ben to that VAN of CreatedBy login’s VAN mapping

Verify - VAN ID | Beneficiary Count

Phase 4 - Laptop Configuration & Down-sync

In each local laptop - configured the VAN

Trigger the down-sync process from the application of local laptop

Down-sync pulls all beneficiary records for the laptop's VAN

Verify the count on laptop

4. Tables Involved in VAN Setup and Sync

Table	Purpose
m_van	VAN master records
m_uservanmapping	Maps user login to VAN
m_parkingplace	Area / Parking Place
m_servicepoint	Service Points
m_synctabledetail	Sync table configuration
m_providerservicemapping	State _ Service mapping
m_vanserialpointmap	VAN to village mapping
m_userparkingplacemap	User to Area mapping

5. TB Clinical Tables - Verify Sync Columns (VanId, VanSerialNo, ParkingPlaceID, Processed, SyncedBy, SyncedDate, SyncFailureReason, LastModDate, LastModBy)

Tb_screening

Tb_suspected

Tb_confirmed_cases

Tb_stoptb_visit

6. New columns to track per-record down-sync delivery

Central DB (Add to each table which involved in downsync):

Column

Type

Default

Purpose

DownSynced

char(1)

'N'

'N' : Never Sent

'P' : Processed (downsynced)

'F' : Failed or conflict

'U' : Update Pending re-sync

DownSyncDate

datetime

Null

When last successfully delivered to local

DownSyncFailureReason

varchar(255)

Null

Failure detail if 'F'. Set to 'CONFLICT' when a conflict is detected.

Local DB:

Column	Type	Default	Purpose
LastDownSyncDate	datetime	Null	When this specific record was last received from central - the reference point for conflict / update detection

Note: Processed = 'F' + SyncFailureReason : Carries all required information
Any edits on the record resets Processed back to 'N' - same as a new record.
LastDownSyncDate provides the conflict detection reference during up-sync.

7. Down-Sync Order - FK Dependency Chain

Records must arrive on the laptop in this order:

Master / Reference Data

M_state / m_district / m_districtblock

M_providerservicemapping

m_van/ m_vantype / m_parkingplace

M_servicepoint / m_servicepointvillagemap

M_user / m_uservanmapping

Clinical masters

Beneficiary Root Tables

M_beneficiaryregidmapping

I_beneficiarymapping

Then all transactional / clinical tables

Note: The down-sync query for each transactional table is simply SELECT * FROM {table} WHERE VanID = {vanID}

Conflict Handling:

A conflict arises only when the same record is edited in two places after the last down-sync — meaning the central record was changed after the laptop last received it, AND the laptop also has unsaved local changes.

If central.LastModDate > local.LastDownSyncDate and local.processed = 'N' → CONFLICT

Master Table Conflicts:

Master / reference tables are central-authoritative. Local never edits them, so no conflict handling is required for those tables.

Overview:

After initial down-sync, two types of edits can happen simultaneously:

Edits a record directly in the Central DB
Edits the same record on the local laptop while offline.

Any record edited in the central after down-synced is automatically flagged for re-sync (downsync). Any local edit always resets Processed back to 'N' - covers both new records and records edited after a previous sync.

Key Actions on Each Event:

Event	Columns Changed
New record created in central	Central: DownSynced='N'
Record edited in central (was 'P')	Central: DownSynced='U', LastModDate=Now()
Down-Sync success	Central: DownSynced='P', DownSyncDate=Now() Local: LastDownSyncDate=Now()
Edits record in local	Local: Processed='N', LastModDate=Now()
Up-Sync Success	Local: Processed='P', SyncedDate=Now() Central: LastModDate, DownSynced='N'
Conflict detected	Local: Processed='F', SyncFailureReason='CONFLICT' Central: DownSynced='F', DownSyncFailureReason='CONFLICT'

Finding and Resolving Conflicted Records:

select * from <table> where DownSynced='F' and DownSyncFailureReason='CONFLICT'

From the list of records, reviews the central version Vs local changes - edits / confirms as needed, and saves. On save, the app updates the Downsynced='N' and DownSyncFailureReason=NULL. On the next down-sync, the central version is pushed to the local laptop. Local is updated to Processed='P', SyncFailureReason=NULL. Conflict cleared.