Embarassing stories: repair lookup fields

You deployed lists with lookup in old fashioned way and something went wrong.
Your lookup field points to nothing because you team forgot the difference between list definition and list instance.
The script will change (brutally) the schema of the field using CSOM: I don't like it but it works like a charm, so don't panic, you can use this script and you'll be at home asap.

Just change the uppercase values with yours.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

using (var ctx = new ClientContext(SPWEB)) {     ctx.Credentials = new NetworkCredential(USERNAME, PASSWORD, DOMAIN);     string listName = "DESTINATION LIST";     string listFieldName = "DESTINATION LIST LOOKUP FIELD";     string lookupListName = "LOOKUP LIST";     string listFieldColumnGroup = "LOOKUP LIST GROUP NAME";       Web web = ctx.Web;     ctx.Load(web, i => i.Id);     ctx.ExecuteQuery();     ctx.Load(web, include => include.Lists.Include(l => l.Title));     ctx.ExecuteQuery();       List list = web.Lists.GetByTitle(listName);     ctx.Load(list, i => i.Fields);     ctx.Load(list, i => i.Id);     ctx.ExecuteQuery();     Field field = list.Fields.GetByInternalNameOrTitle(listFieldName);     ctx.Load(field);     ctx.ExecuteQuery();       List lookupList = web.Lists.GetByTitle(lookupListName);     ctx.Load(lookupList, i => i.Fields);     ctx.Load(lookupList, i => i.Id);     ctx.ExecuteQuery();       var schema = field.SchemaXml;     string fieldId = field.Id.ToString();     string fieldDisplayName = field.Title;     string fieldInternalName = field.InternalName;     string webId = web.Id.ToString();     string lookupListId = lookupList.Id.ToString();       string newSchema = @"<field ID=""{" + fieldId + @"}"" Name=""" + fieldInternalName + @""" DisplayName=""" + fieldDisplayName + @""" Type=""Lookup"" List=""{" + lookupListId + @"}"" ShowField=""Title"" Required=""FALSE"" Group=""" + listFieldColumnGroup + @""" WebId=""{" + webId + @"}"" SourceID=""{685aa422-c6da-477c-98ea-5cf16d9a3dd6}"" StaticName=""" + fieldInternalName + @""" Version=""1"" ColName=""int1"" RowOrdinal=""0"" />";     field.SchemaXml = newSchema;     ctx.ExecuteQuery(); }