Showing list column from parent in child list view (Lookup or choice field types)

Question

I have two lists with a parent/child relationship. I have a lookup field pointing to the parent with referential integrity which serves as my foreign key. I know that from views in the child list I can get columns from the parent list to show up such as text fields and number fields by checking off the additional columns from the lookup field in the child list pointing to the parent.

What I've come to find out is that certain field types do not apply, such as lookup or choice field types. Is there anyway around this? I've tried using a calculated field, however, this breaks my InfoPath form that we use to add, edit, or view list items in.

I need to be able to see these fields from the child lists views. If I use SP designer and use the datasource (join/merge) feature, will I be able to edit list items in the view or will it be read only?

I would appreciate any direction, thank you.

Answer 1

The commonly proposed workaround for field types that don't allow to be selected as additional columns when configuring a lookup field is to add a column of type text for each property you need to show on the related list and populate it via a workflow fired on item creation/editing.

If you don't edit your existing InfoPath forms, InfoPath won't complain. If you do, then just say yes to refresh the underlying datasource and ignore the new fields that will show up (they will get their value set by the workflow).

The other route of creating a joined view is also viable (for display views). As for editing the related fields, this is not possible in the same view. The AggregateDataSource, which is what is created when you point to a Linked Source (join view) to be used inside a DVWP, doesn't support update/insert operations (it will throw an error saying that the method is not supported). So, yes, basically a joined view is a read-only view.

If you need to show related fields in an edit form, insert a normal edit form with its datasource pointing to the child list, then drop an additional SPDataSource into the page and use it to retrieve the related items from the lookup field. Then, you can show its fields with some of the databound controls you have at disposal (eg. Asp.Net repeater)