Updating a List Using SharePoint Designer 2010 - can't find an example of my use case

Question

I have two lists - one administrative with lots of details, and one public with a subset of the information. There are close to 2000 entries in the list. The admin list has 3 states, which I track in a column - Adding a new item for assessment (default value), working on a new item (pending) and assessment complete (yes). When the column goes from default to pending, the Designer workflow copies the current item to the public list. Got this down, it's easy to do in Designer. What stumps me is how to perform the second task. 2-3 weeks later the assessment will be complete and the admin will then change the assessment column to "assessment complete" or "yes". I want Designer to then replace the values in the subset of columns in the public list, with the new information entered in the admin list. The values can vary, so there isn't a fixed value. The title field will stay the same, but the other fields may or may not change. If I simply copy again, I get two entries for each item in the public list. Not what I want. I simply want the admin list to update the public list with the new values once the assessment field is changed to yes. I've tried several ways, and can't get this to work. I am setting the workflow to automatically begin when a list item changes. Any ideas?

Answer 1

You can take one more column in Public List to put the ID of the Admin List item, a kind of a key... And when "assessment complete" or "yes" status occurs to Public List item, you can get the Admin List Item from the Id and update it instead of copying/adding new item in Admin List...

You can make this column a Lookup column for easy handling, and hide this column to appear on forms.. Hiding column can be done in List Settings -> Advanced Settings -> Turn on Content Types setting [a checkbox]

Then back on List Settings -> Under Content Types -> click Item... Choose your lookup column and make it Hidden!

I hope this helps