Work around needing look-up column data for a calculated column (SharePoint 2010)

Question

In SharePoint 2010:

I have a master list that pulls several columns from two different lists based on a unique ID. The information that's being pulled in is similar, but being entered by two different people for two different customers and must be in separate lists. Let's say the similar columns used in the master list are Quote No., Price, Quote Date and Customer and this information on the Master List is coming from List A and List B.

In this Master List, I would like to see all of the line items and only the corresponding look-up columns that have information in them. For example, if Item 0025 has a Quote No., Price, Quote Date and Customer information from List A, I don't want 4 blank columns where the information from List B would go, and vice versa. I just want one column for Quote No. that has the information, regardless of where it came from.

My original thought was to hide the look-up columns and create a calculated column that would search through and see "if Quote No. Look-up column from List A is empty, show nothing. If Quote No. Look-up column from List A has a value, display this value" and so forth.

But since you can't perform calculations on a look-up column I'm at a loss for how to display this appropriately. There are about 10 columns being pulled in from these other lists and 35+ columns total.

Any ideas? I'm happy to send in screen shots or explain the situation in more detail.

Answer 1

You could do this by using a data view web part and then modifying the XSLT through SharePoint Designer.

Simply create the data view web part, importing all 8 similar columns, then convert it to it's XSLT view.

Then, besides the other 35 columns, you will have four sections of code similiar to this:

<td>
    <xsl:value-of select="@QuoteNoA" />
</td>
<td>
    <xsl:value-of select="@QuoteNoB" />
</td>

Changing this to something like the following should do the trick:

<td>
    <xsl:choose>
        <xsl:when test="@QuoteNoA != ''">
            <xsl:value-of select="@QuoteNoA" />
        </xsl:when>
        <xsl:otherwise>
            <xsl:value-of select="@QuoteNoB" />
       </xsl:otherwise>
    </xsl:choose>
</td>

Don't forget to change the column header titles and remove the four extra columns that you do not need anymore (all done through the XSLT)

Answer 2

You can take the lists and make them in External content types as tables in SQL server. Then you can do the calculations you like. Then display all the tables as external content types. Extenal content types look like lists and also have edit forms and etc.