Scenario:

This is an simple tutorial of using Lookup column to synchronize the data between two lists: Source List and Destination List.

1. In the Destination List, create a RefID which gets information from ID field of the Source List. Very similar with the Foreign Key in the SQL Server.

2. Create a simple workflow attached to Source List to create the initial the relationship.

3. Create a workflow attached to Destination List to sync the changes back to the Source List.

4. Test results.

1) Create a new item in Source List and wait for the workflow to complete.

2) In the Destination List, a list is created by the workflow attached to Source List and the RefID has been filled out.

If you click the RefID, you could navigate back to Source List.

3) Modify the list item in Destination List, wait for the workflow attached to Destination List to complete.

Navigate to Source List, the change made in Destination List is synchronized.

Additional Comments:

1. SharePoint Designer workflow impersonates your current account, which means you need to have Contribute Permission (at least Edit List Item Permission) on both Lists.

To workaround the limitation, a Visual Studio workflow or an Event Receiver is needed. actually Event Receiver is more suitable for this scenario.

2. As the RefID is a key field in the two lists, I do not suggest to add the RefID to the exited lists because it is not easy to fill the empty values with correct ones.

3. The tutorial implements a limited function with  least actions, it should be extended with specific requirements.

Failed on Start usually means an error is happening in SharePoint before your workflow code spins up, such as not being able to find the workflow dll. (see Developing Workflows in VS: Part 6 - Deploy and Debug your workflow for more information)

Search for ULS log, I find the following error:

12/30/2008 19:11:47.88     w3wp.exe (0x11BC)                           0x1F24    Windows SharePoint Services       Workflow Infrastructure           75yn    Unexpected    Load Workflow Assembly: System.IO.FileNotFoundException: Could not load file or assembly '<Assembly Name>, Version=1.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' or one of its dependencies. The system cannot find the file specified. 

I can see the assembly in the GAC, But my real PublicKeyToken is 7a3c3335cb88783e, I just copy the PublicKeyToken of Microsoft.Office.Workflow.Feature! So SharePoint cannot find the correct dll.

Amend the PublicKeyToken, issue is resolved !

Pay attention to COPY(Ctrl+C) and PASTE(Ctrl+V), this is what I learned.

   1:  

   2: <Elements xmlns="http://schemas.microsoft.com/sharepoint/">

   3:   <Workflow

   4:        Name="My Workflow"

   5:        Description="This workflow ..."

   6:        Id="GUID"

   7:        CodeBesideClass="ProjectName.Workflow1"

   8:        CodeBesideAssembly="ProjectName, Version=1.0.0.0,

   9:          Culture=neutral,

  10:          PublicKeyToken=publicKeyToken"

  11:        TaskListContentTypeId="0x01080100C9C9515DE4E24001905074F980F93160"

  12:        AssociationUrl="_layouts/CstWrkflIP.aspx"

  13:        InstantiationUrl="_layouts/IniWrkflIP.aspx"

  14:        ModificationUrl="_layouts/ModWrkflIP.aspx"

  15:        StatusUrl="_layouts/WrkStat.aspx">

  16:  

  17:     <Categories/>

  18:     <MetaData>

  19:       <AssociateOnActivation>false</AssociateOnActivation>

  20:     </MetaData>

  21:   </Workflow>

  22: </Elements>

  23:  

Usually I don’t have any forms associated with the workflow, after deploy the workflow, I will get the “The workflow template has specified no FormURN for this page.” error.

To resolve this, just remove the TaskListContentTypeId, AssociationUrl, InstantiationUrl, ModificationUrl properties of the workflow and redeploy the workflow. 

   1: <Elements xmlns="http://schemas.microsoft.com/sharepoint/">

   2:   <Workflow

   3:        Name="My Workflow"

   4:        Description="This workflow ..."

   5:        Id="GUID"

   6:        CodeBesideClass="ProjectName.Workflow1"

   7:        CodeBesideAssembly="ProjectName, Version=1.0.0.0,

   8:          Culture=neutral, 

   9:          PublicKeyToken=publicKeyToken"

  10:        TaskListContentTypeId="0x01080100C9C9515DE4E24001905074F980F93160"

  11:        AssociationUrl="_layouts/CstWrkflIP.aspx"

  12:        InstantiationUrl="_layouts/IniWrkflIP.aspx"

  13:        ModificationUrl="_layouts/ModWrkflIP.aspx"

  14:        StatusUrl="_layouts/WrkStat.aspx">

  15:  

  16:     <Categories/>

  17:     <MetaData>

  18:       <AssociateOnActivation>false</AssociateOnActivation>

  19:     </MetaData>

  20:   </Workflow>

  21: </Elements>