2

I'm having trouble creating a joined edit or view form from 2 lists in Sharepoint 2010.

Details

I have two lists in Sharepoint 2010 that have a 1:1 relationship. When a user creates an entry in the first list (call it "Requests"), this triggers a workflow that creates a corresponding entry in a "Reviews" list. The Reviews list as a "ReqID" column, so each Review has a pointer to the Request item it relates to.

The two lists have different permissions and access points, so only reviewers can see the Reviews list.

What I want to achieve is this- When a reviewer accesses an item in the reviews list, either to view, or to edit, they then see the fields in the Reviews list PLUS the fields from the corresponding Requests item, directly underneath, in a single page. Like this-

   REVIEW INFO
   Reviews column 1: <Request ID>
   Reviews column 2: <some data>
   Reviews column 3: <some data>
   ...

   REQUEST INFO
   Requests ID: <= request ID above>
   Requests column 1: <some data>
   Requests column 2: <some data>
   ...

What I've done so far is-

  1. Cloned the "Editform.aspx" page for the Reviews list to create a new "Editform_mod.aspx" page.
  2. Inserted a list view (XsltListView web part) on the Requests list in this page, underneath the normal editing web part for the Reviews entry.
  3. Set up a connection from the Reviews ListForm web part, to provide a filter row to the Requests XsltListView web part, with the connection set to "Get Filter Values From", and the columns chosen so "ReqID" from the Review list sets the filter value for "ID" in the Requests list.

I will eventually make my modded form the default editform for the Review list, once I can make it work...

Problem

This almost works! However the second view shows no data. When I look at the filter settings in the browser, if (say) I am looking at a Reviews item with ReqID=3, rather than the filter being set simply to the number 3, it's set to "3;#3", which of course doesn't match anything.

Not sure if i'm doing something obviously wrong here. Any pointers would be much appreciated.

Improve this question

4 Answers 4

Reset to default
1

This is assuming you are using SharePont Designer 2010; I would advise that you use the "Extract Substring from Index of String" Action. This will allow you copy the ReqID value into a custom variable, trim that variable, then use the edited variable as your filter.Some thing like the below

"Copy from [Current Item:ReqID], starting at 4 (Output to Variable:variableName)"

PS:the 4th caracter usually indicates the value needed.

Cheers

Improve this answer
2
  • Sounds interesting, but- 1) Seems weird that SP lists can't consume filter values from other SP lists? 2) "Actions" are new to me. Do I need to trigger a workflow to use them? If i'm looking at a single page in edit mode, I assume an action won't be triggered until the page is saved? 3) The required value (3 in this case) is only the 4th character if the Request ID is 1 character wide. In general list IDs can grow indefinitely, so I would need to use something like a regular expression to extract "All the digits up to but not including the first non-digit". Not sure if Sp can do this?
    – colin_e
    Commented Nov 6, 2012 at 15:55
  • Yes colin_e, you would have to use some sort of workflow to trigger it off. In SPD, there are already pre-defined actions to use in your workflow. That specified action picks whatever is on the 4th index and uses it; so if the value is double or triple digits, be rest assured that it will still return the value. for ex "3;#3" will return 3, "3;#12" will return 12.
    – Nonso
    Commented Nov 7, 2012 at 4:12
0

Ummm, if you have two lists with a 1:1 relationship, you in fact only have 1 list. Why not create two fields that both lookup the same list?

Improve this answer
2
  • I had a couple of reasons for using two lists- 1) Simplicity. When entering a Request the users see a simpler form, without any of the Review fields they don't need to see. 2) Permissions. Although I haven't got that far, the idea is that only Reviewers will be able to edit the Reviews list fields. (Note there's a question in my mind about the permissions of the workflow that creates the Reviews entry when a Request is created, but I haven't got that far yet).
    – colin_e
    Commented Nov 6, 2012 at 15:46
  • BTW on the second point, as I understand it SP doesn't do column-level permissions, so this is effectively a way to work around that.
    – colin_e
    Commented Nov 6, 2012 at 15:58
0

Colin,

Edit the lookup column and make the lookup on Title field instead of ID...

And in "Add a column to show each of these additional fields" select ID column... Now try the web part connection with "Request:ID" column in Review List...

Lookup Column

Also, Nat gave a very nice suggestion - if the relationship is 1:1, you should keep the both Review and Requests in single list and make different forms for each of these.. Or you could have also created two content types for each of them!

I hope this will work!

Improve this answer
5
  • In theory there is no guarantee that Request titles will be unique, that's why I went for database-style ID lookups, they seemed safest (maybe I was naiive about SP oddities). The "additional fields" feature in SP2010 is pretty strange BTW, because it only offers you a small subset of the fields from the "other" (joined) list. If you could pull across all the fields from the Request as columns in Reviews I wouldn't need to try this procedure of joining lists on the page.
    – colin_e
    Commented Nov 6, 2012 at 16:01
  • BTW I did consider using one list (and I may have to go back to that idea). Issues I forsaw were- 1) Column level permissions. Only Reviewers shoudl be able to edit Review fields. 2) Maybe i'm wrong but my understanding of content types is that each list row can only "be" one content type. So, if I create a row as a Request, I can't subsequently mutate it into a Review and work on the columns that would only exist in a Review. Have I misunderstood that?
    – colin_e
    Commented Nov 6, 2012 at 16:04
  • Colin, yes you are right... content types will not solve the problem! need to think of something else I guess..
    – Arsalan Adam Khatri
    Commented Nov 6, 2012 at 16:32
  • BTW I did some testing and the Title field does work in the connection (which helped me a lot to understand the problem, so thanks for that!) but as I suspected, a duplicate title causes multiple matches to turn up in the second list. See above for the solution, it needs to be a numeric ID connection, but NOT stored in a lookup column!
    – colin_e
    Commented Nov 7, 2012 at 19:35
  • Yeah that can be a workaround too :)
    – Arsalan Adam Khatri
    Commented Nov 8, 2012 at 9:22
0

Thanks for all the responses.

After a lot more experimentation and testing I have a better handle on what's going on.

The basic problem (with one list feeding a filter value to the other) in my original setup is triggered by the source value coming from a lookup column. Even though this value starts off as a number (an integer ID), and is copied into the "ReqID" column as a number, because that column was declared as a lookup it causes this weird formatting problem.

I now recall I've seen issues like this before with lookup columns. if you use a lookup column as a promoted/demoted value from an Office document you also get these weird string elements inserted in the values.

The workaround is to either-

  1. Declare the "ReqID" column as a number not a lookup (which might not be practical in all cases). or-
  2. Have a second "_ReqID" column that IS a number, copy the Request ID into that was well, and use _ReqID as the source of the filter value.

Clunky but it works.

Now i'm having other problems with the presentation of the second list, but that's another question!

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.