Avatar of tomfogarty
tomfogarty
Flag for Ireland asked on

"Join" Question

Hi,

I have a query which is giving me a bit of trouble. I have used derived tables to put together the correct aggregates for a invoicing report. I'm now trying to break it down by a column for product analysis.

Essentially, I've got two derived tables returning the data I need but when they are joined it is duplicating some rows.
ID1, ID2, Value1, Value2
1, 1, 50, 60
2, 2, 600, 55
3, 2, 555, 899

ID1, ID2, Value3
1, 1, 400
1, 3, 2000

Value 1 and Value 2 are totals taken from an invoice detail table. Value 3 is taken from a delivery table which tracks what has been delivered of the products.

ID1 would basically be sales IDs and ID2 would be product IDs.

To tie these together in the main query I'm using a join on ID1 but I also need to show the FK value for ID2 so I have to join that as well. I think this may be where the problem is.

When they come together I'm getting duplicates on Value 1 and Value 2 and Value 3.

Any ideas?
Microsoft SQL Server 2008Microsoft SQL ServerDatabases

Avatar of undefined
Last Comment
tomfogarty

8/22/2022 - Mon
Scott Pletcher

If you're just trying to eliminate the duplicate values in an otherwise working query, just add DISTINCT to the start of the SELECT column list:

SELECT DISTINCT ID1, ..., Value1, Value2, Value3
FROM ...
...
Koen Van Wielink

Hi Tom,

What do you want your final result set to look like? You're correct that when you join both the sales ID AND the product ID you will get duplicate records. Are you looking for an aggregate?

Rgds,

Koen
tomfogarty

ASKER
This is the query. Not sure where I would put a distinct other than in the first select clause as that doesn't make a difference.

SELECT 
dbo.SalesProposal.SalesProposalID, 
dbo.Product.Product,
ISNULL(ProductBreakdown.InvoicedQuantity, 0) AS InvoicedQuantity,
ISNULL(ProductBreakdown.InvoicedAmount, 0) AS InvoicedAmount,
ISNULL(DispatchDerived.DeliveredQuantity, 0) AS DeliveredQuantity
FROM dbo.SalesProposal
INNER JOIN dbo.SalesProposalStatus ON dbo.SalesProposal.StatusID = dbo.SalesProposalStatus.SalesProposalStatusID AND dbo.SalesProposalStatus.SalesProposalStatus = 'Sold'
LEFT OUTER JOIN
(SELECT dbo.Dispatch.SalesProposalID, dbo.Dispatch.ProductID, ISNULL(SUM(dbo.Dispatch.Weight * dbo.Dispatch.ConversionFactor), 0) AS DeliveredQuantity
FROM dbo.Dispatch GROUP BY dbo.Dispatch.SalesProposalID, dbo.Dispatch.ProductID) AS DispatchDerived ON DispatchDerived.SalesProposalID = dbo.SalesProposal.SalesProposalID
LEFT OUTER JOIN 
(SELECT dbo.Invoice.SalesProposalID, InvoiceItemDerived.ProductID, ISNULL(SUM(InvoiceItemDerived.InvoicedQuantity), 0) As InvoicedQuantity, ISNULL(SUM(InvoiceItemDerived.InvoicedAmount), 0) As InvoicedAmount
FROM dbo.Invoice LEFT OUTER JOIN 
(SELECT dbo.InvoiceItem.InvoiceID, dbo.InvoiceItem.ProductID, ISNULL(SUM(dbo.InvoiceItem.Quantity), 0) AS InvoicedQuantity, ISNULL(SUM(dbo.InvoiceItem.Quantity * dbo.InvoiceItem.Price), 0) AS InvoicedAmount
FROM dbo.InvoiceItem GROUP BY dbo.InvoiceItem.InvoiceID, InvoiceItem.ProductID) AS InvoiceItemDerived on Invoice.InvoiceID = InvoiceItemDerived.InvoiceID GROUP BY dbo.Invoice.SalesProposalID, InvoiceItemDerived.ProductID) AS ProductBreakdown ON dbo.SalesProposal.SalesProposalID = ProductBreakdown.SalesProposalID
INNER JOIN dbo.Product ON dbo.Product.ProductID = DispatchDerived.ProductID OR dbo.Product.ProductID = ProductBreakdown.ProductID

Open in new window


A salesproposal is basically a lot to be auctioned. PK is salesproposalID.
Invoice is obvious. Joined to salesproposal table with a FK salesproposalID. PK is invoiceid.
InvoiceItem contains the line items for invoices. Joined to Invoice using Fk invoiceid
Dispatch logs all deliveries of products. It is joined to salesproposal with Fk salesproposalid. PK is dispatchid.
Products (productids) are stored in each invoiceitem and dispatch. Product information is stored in the product table.

Hope this helps with deliberations.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
tomfogarty

ASKER
I fyou take the sample data I gave:
ID1, ID2, Value1, Value2
1, 1, 50, 60
2, 2, 600, 55
3, 2, 555, 899

ID1, ID2, Value3
1, 1, 400
1, 3, 2000

ID1 being salesproposal ID and ID2 being product ID.

What I want it to look like is:
ID1, ID2, Value1, Value2, Value3
1,1,50,60,400
1,3,0,0,2000
2,2,600,55,0
3,2,555,899,0

It's a report on invoiced vs delivered. Value 2 isn't actually too important. Value 1 and Value 3 (invoiced and delivered quantitites are the most important).

Does this help?
Koen Van Wielink

Hi Tom,

Is it possible that the duplicate records are the result of the OR statement in the last inner join? What if you only use 1 of those (just dbo.Product.ProductID = DispatchDerived.ProductID for instance) do you still get the double records?

Rgds,

Koen
Koen Van Wielink

If I'm right, you can add the link to products in your subqueries and derive the product descriptions before grouping everything together. That should prevent the duplicates.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
tomfogarty

ASKER
Hi Koen,

Sample result sets below. I'm using GUIDs for IDs. The first set is for the query I just gave to you. Second is without the dbo.Product.ProductID = DispatchDerived.ProductID part of the last join and the third is without the dbo.Product.ProductID = ProductBreakdown.ProductID part of the last join.


1BEA66B3-E3F2-4535-9191-1F20EF3225D5      Through & Through      3000.00      171000.000000      0.0000
CFB5A1EC-CB1E-44E4-B594-55136681997D      Through & Through      499.00      27195.500000      0.0000
1CEAB3DB-1D31-4214-AF77-F6AB7AF110FA      Pallet      62.57      2252.520000      155.7270
1CEAB3DB-1D31-4214-AF77-F6AB7AF110FA      Pallet      62.57      2252.520000      2000.0000
1CEAB3DB-1D31-4214-AF77-F6AB7AF110FA      Energy Wood      62.57      2252.520000      2000.0000



1CEAB3DB-1D31-4214-AF77-F6AB7AF110FA      0492E3A2-7490-4778-804B-0040971BADA9      62.57      2252.520000      155.7270
1CEAB3DB-1D31-4214-AF77-F6AB7AF110FA      0492E3A2-7490-4778-804B-0040971BADA9      62.57      2252.520000      2000.0000
1BEA66B3-E3F2-4535-9191-1F20EF3225D5      A2910AA4-4D19-4497-AE25-C1F551DA578E      3000.00      171000.000000      0.0000
CFB5A1EC-CB1E-44E4-B594-55136681997D      A2910AA4-4D19-4497-AE25-C1F551DA578E      499.00      27195.500000      0.0000



1CEAB3DB-1D31-4214-AF77-F6AB7AF110FA      0492E3A2-7490-4778-804B-0040971BADA9      62.57      2252.520000      155.7270
1CEAB3DB-1D31-4214-AF77-F6AB7AF110FA      31E94D82-0139-4542-84D0-BF035EB4A756      62.57      2252.520000      2000.0000


Tom.
tomfogarty

ASKER
Correction - the first set of results should look like this:

1BEA66B3-E3F2-4535-9191-1F20EF3225D5      A2910AA4-4D19-4497-AE25-C1F551DA578E      3000.00      171000.000000      0.0000
CFB5A1EC-CB1E-44E4-B594-55136681997D      A2910AA4-4D19-4497-AE25-C1F551DA578E      499.00      27195.500000      0.0000
1CEAB3DB-1D31-4214-AF77-F6AB7AF110FA      0492E3A2-7490-4778-804B-0040971BADA9      62.57      2252.520000      155.7270
1CEAB3DB-1D31-4214-AF77-F6AB7AF110FA      0492E3A2-7490-4778-804B-0040971BADA9      62.57      2252.520000      2000.0000
1CEAB3DB-1D31-4214-AF77-F6AB7AF110FA      31E94D82-0139-4542-84D0-BF035EB4A756      62.57      2252.520000      2000.0000
tomfogarty

ASKER
Hi Kvwielink,

I tried that but the issue I had was when using information from the product table in the main select clause, how do you join it to the 2 derived tables because you essentially have 2 sets of data containing product information.

Tom.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Sharath S

You have LEFT JOIN to DispatchDerived and InvoiceItemDerived FROM SalesProposal but INNER JOIN to Product. But there is no join condition between Product and SalesProposal. Hence your LEFT JOINs work as INNER JOIN. So I changed the LEFT JOINs to INNER JOINs in the below proposed code. You can change based on your data.
Also, I think you need a UNION instead of two LEFT JOINs. give a try and see the result.
;WITH cte 
     AS (SELECT * 
           FROM dbo.SalesProposal SP 
                INNER JOIN dbo.SalesProposalStatus SPS 
                        ON SP.StatusID = SPS.SalesProposalStatusID 
                           AND SPS.SalesProposalStatus = 'Sold'), 
     dispatchderived 
     AS (SELECT D.SalesProposalID, 
                D.ProductID, 
                ISNULL(SUM(D.Weight * D.ConversionFactor), 0) AS DeliveredQuantity 
           FROM dbo.Dispatch D 
          WHERE EXISTS (SELECT 1 
                          FROM dbo.Product P 
                         WHERE D.ProductID = P.ProductID) 
          GROUP BY D.SalesProposalID, 
                   D.ProductID), 
     productbreakdown 
     AS (SELECT I.SalesProposalID, 
                InvoiceItemDerived.ProductID, 
                ISNULL(SUM(InvoiceItemDerived.InvoicedQuantity), 0) AS InvoicedQuantity, 
                ISNULL(SUM(InvoiceItemDerived.InvoicedAmount), 0)   AS InvoicedAmount 
           FROM dbo.Invoice I 
                LEFT OUTER JOIN (SELECT II.InvoiceID, 
                                        II.ProductID, 
                                        ISNULL(SUM(II.Quantity), 0)            AS InvoicedQuantity,
                                        ISNULL(SUM(II.Quantity * II.Price), 0) AS InvoicedAmount
                                   FROM dbo.InvoiceItem II 
                                  GROUP BY II.InvoiceID, 
                                           II.ProductID) AS InvoiceItemDerived 
                             ON I.InvoiceID = InvoiceItemDerived.InvoiceID 
          WHERE EXISTS (SELECT 1 
                          FROM dbo.Product P 
                         WHERE D.productid = InvoiceItemDerived.ProductID) 
          GROUP BY I.SalesProposalID, 
                   InvoiceItemDerived.ProductID) SELECT c1.SalesProposalID, 
       PB.ProductID, 
       ISNULL(PB.InvoicedQuantity, 0) AS InvoicedQuantity, 
       ISNULL(PB.InvoicedAmount, 0)   AS InvoicedAmount, 
       0                              DeliveredQuantity 
  FROM cte c1 
       JOIN productbreakdown PB 
         ON c1.SalesProposalID = PB.SalesProposalID 
UNION ALL 
SELECT c1.SalesProposalID, 
       DD.ProductID, 
       0                               InvoicedQuantity, 
       0                               InvoicedAmount, 
       ISNULL(DD.DeliveredQuantity, 0) AS DeliveredQuantity 
  FROM cte c1 
       JOIN dispatchderived DD 
         ON c1.SalesProposalID = DD.SalesProposalID 

Open in new window

awking00

select coalesce(t1.id1,t2.id1) id1
      ,coalesce(t1.id2,t2.id2) id2
      ,coalesce(t1.value1,0) value1
      ,coalesce(t1.value2,0) value2
      ,coalesce(t2.value3,0) value3
from t1 full outer join t2
on t1.id1 = t2.id1
and t1.id2 = t2.id2
order by 1,2;
Replace t1 and t2 with your derived tables.
tomfogarty

ASKER
Hi Sharath,

Getting an error on this line:
 
         WHERE EXISTS (SELECT 1 
                          FROM dbo.Product P 
                         WHERE D.productid = InvoiceItemDerived.ProductID) 

Open in new window


The multi-part identifier "D.productid" could not be bound.

Awkingoo - I've done exactly as you say - replaced the t1 and t2 values with the derived tables but not go - getting a lot of syntacical errors.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'id1'.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 17
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 22
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 24
Incorrect syntax near '.'.
Msg 156, Level 15, State 1, Line 29
Incorrect syntax near the keyword 'full'.
Msg 156, Level 15, State 1, Line 31
Incorrect syntax near the keyword 'on'.
Msg 102, Level 15, State 1, Line 35
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 36
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 41
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 42
Incorrect syntax near '.'.

Sorry.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
PortletPaul

I would suggest you adpot the full outer join plus use of coalesce as proposed by by: awking00 (ID: 39018988)

any inner or left joins will require that one derived table as the 'master' whereas the full outer join won't, it will ensure all rows are included. The use coalesce then simplifies the columns to just those you expect in the final result.
SOLUTION
Sharath S

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
PortletPaul

I would suggest you adopt the full outer join plus use of coalesce as proposed by awking00 (ID: 39018988)
[sorry for the repeat, but had to correct my awful typo!]
tomfogarty

ASKER
Hi Sharath,

I'm still getting duplicates on one of the items.
SalesProposalID      ProductID      InvoicedQuantity      InvoicedAmount      DeliveredQuantity
1CEAB3DB-1D31-4214-AF77-F6AB7AF110FA      0492E3A2-7490-4778-804B-0040971BADA9      62.57      2252.520000      0.0000
1BEA66B3-E3F2-4535-9191-1F20EF3225D5      A2910AA4-4D19-4497-AE25-C1F551DA578E      3000.00      171000.000000      0.0000
CFB5A1EC-CB1E-44E4-B594-55136681997D      A2910AA4-4D19-4497-AE25-C1F551DA578E      499.00      27195.500000      0.0000
1CEAB3DB-1D31-4214-AF77-F6AB7AF110FA      0492E3A2-7490-4778-804B-0040971BADA9      0.00      0.000000      155.7270
1CEAB3DB-1D31-4214-AF77-F6AB7AF110FA      31E94D82-0139-4542-84D0-BF035EB4A756      0.00      0.000000      2000.0000

There is a sales proposal which has rows with more than 1 product.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
tomfogarty

ASKER
Hi PortletPaul,

I'm trying to rework my query using what awking suggested but still getting errors. Bearing in mind what you're saying but trying to get it to work.

Thanks.
tomfogarty

ASKER
FYI, the data should look like this:
1CEAB3DB-1D31-4214-AF77-F6AB7AF110FA      0492E3A2-7490-4778-804B-0040971BADA9      62.57      2252.52      155.727
1BEA66B3-E3F2-4535-9191-1F20EF3225D5      A2910AA4-4D19-4497-AE25-C1F551DA578E      3000      171000      0
CFB5A1EC-CB1E-44E4-B594-55136681997D      A2910AA4-4D19-4497-AE25-C1F551DA578E      499      27195.5      0
1CEAB3DB-1D31-4214-AF77-F6AB7AF110FA      31E94D82-0139-4542-84D0-BF035EB4A756      0      0      2000

Just did a bit of editing in Excel to demonstrate it.

Tom.
PortletPaul

Tom, can you post your most current code here? This will save much time in the long run.
if possible use a code block (so there are lines numbers to refer to) or attach as a text file.

As best as I can tell it isn't quite as simple as the full outer join example given, not all fields need coalesce as some are exclusive to the 2 derived tables and there's 'product' which isn't in either. This is what I came up with in case its helpful:
SELECT
  derived.SalesProposalID
, dbo.Product.Product
, derived.ProductID
, derived.InvoicedQuantity
, derived.InvoicedAmount
, derived.DeliveredQuantity
FROM (
            SELECT
               coalesce(DispatchDerived.SalesProposalID , ProductBreakdown.SalesProposalID) as SalesProposalID
             , coalesce(DispatchDerived.ProductID, ProductBreakdown.ProductID) as ProductID
             , ISNULL(ProductBreakdown.InvoicedQuantity, 0) AS InvoicedQuantity
             , ISNULL(ProductBreakdown.InvoicedAmount, 0) AS InvoicedAmount
             , DispatchDerived.DeliveredQuantity
            FROM (
                     SELECT dbo.Dispatch.SalesProposalID
                      , dbo.Dispatch.ProductID
                      , ISNULL(SUM(dbo.Dispatch.Weight * dbo.Dispatch.ConversionFactor), 0) AS DeliveredQuantity
                     FROM dbo.Dispatch
                     GROUP BY dbo.Dispatch.SalesProposalID
                      , dbo.Dispatch.ProductID
                  ) AS DispatchDerived
            FULL OUTER JOIN (
                     SELECT dbo.Invoice.SalesProposalID
                      , InvoiceItemDerived.ProductID
                      , ISNULL(SUM(InvoiceItemDerived.InvoicedQuantity), 0) AS InvoicedQuantity
                      , ISNULL(SUM(InvoiceItemDerived.InvoicedAmount), 0) AS InvoicedAmount
                     FROM dbo.Invoice
                     LEFT JOIN (
                      SELECT dbo.InvoiceItem.InvoiceID
                       , dbo.InvoiceItem.ProductID
                       , ISNULL(SUM(dbo.InvoiceItem.Quantity), 0) AS InvoicedQuantity
                       , ISNULL(SUM(dbo.InvoiceItem.Quantity * dbo.InvoiceItem.Price), 0) AS InvoicedAmount
                      FROM dbo.InvoiceItem
                      GROUP BY dbo.InvoiceItem.InvoiceID
                       , InvoiceItem.ProductID
                      ) AS InvoiceItemDerived ON Invoice.InvoiceID = InvoiceItemDerived.InvoiceID
                     GROUP BY dbo.Invoice.SalesProposalID
                      , InvoiceItemDerived.ProductID
                   ) AS ProductBreakdown
       ) as derived
INNER JOIN dbo.Product ON derived.ProductID = dbo.Product.ProductID

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
tomfogarty

ASKER
This is the latest version of the code that I'm using. It's showing the proper details in terms of showig rows once. But, looking at it from the point of view that there are 2 derived tables, the data from each seem to be separated in the results. This may be because it's simply a union of the 2 derived tables. They need to merge then we'll have what we need.

WITH cte 
AS (SELECT * 
FROM dbo.SalesProposal SP 
INNER JOIN dbo.SalesProposalStatus SPS ON SP.StatusID = SPS.SalesProposalStatusID AND SPS.SalesProposalStatus = 'Sold'),
dispatchderived
AS (SELECT D.SalesProposalID, D.ProductID, ISNULL(SUM(D.Weight * D.ConversionFactor), 0) AS DeliveredQuantity 
FROM dbo.Dispatch D 
WHERE EXISTS (SELECT 1 FROM dbo.Product P WHERE D.ProductID = P.ProductID) 
GROUP BY D.SalesProposalID, D.ProductID), 
productbreakdown 
AS (SELECT I.SalesProposalID, InvoiceItemDerived.ProductID, ISNULL(SUM(InvoiceItemDerived.InvoicedQuantity), 0) AS InvoicedQuantity, ISNULL(SUM(InvoiceItemDerived.InvoicedAmount), 0)   AS InvoicedAmount 
FROM dbo.Invoice I 
LEFT OUTER JOIN (SELECT II.InvoiceID, II.ProductID, ISNULL(SUM(II.Quantity), 0) AS InvoicedQuantity, ISNULL(SUM(II.Quantity * II.Price), 0) AS InvoicedAmount
FROM dbo.InvoiceItem II GROUP BY II.InvoiceID, II.ProductID) AS InvoiceItemDerived 
ON I.InvoiceID = InvoiceItemDerived.InvoiceID 
WHERE EXISTS (SELECT 1 
FROM dbo.Product P 
WHERE P.productid = InvoiceItemDerived.ProductID) 
GROUP BY I.SalesProposalID, 
InvoiceItemDerived.ProductID)
SELECT c1.SalesProposalID, PB.ProductID, ISNULL(PB.InvoicedQuantity, 0) AS InvoicedQuantity, ISNULL(PB.InvoicedAmount, 0) AS InvoicedAmount, 0 DeliveredQuantity 
FROM cte c1 JOIN productbreakdown PB ON c1.SalesProposalID = PB.SalesProposalID 
UNION ALL 
SELECT c1.SalesProposalID, DD.ProductID, 0 InvoicedQuantity, 0 InvoicedAmount, ISNULL(DD.DeliveredQuantity, 0) AS DeliveredQuantity 
FROM cte c1 
JOIN dispatchderived DD ON c1.SalesProposalID = DD.SalesProposalID  

Open in new window

PortletPaul

:)

"UNION ALL" will append and NOT merge (which is good for performance! not a criticism)

for an experiment put 2 dahes in front of "ALL" to make UNION --ALL in your code.

Is this result better?

Ideally one of the other suggestions would be adopted - but do try UNION
tomfogarty

ASKER
Same results with UNION --ALL


1BEA66B3-E3F2-4535-9191-1F20EF3225D5      A2910AA4-4D19-4497-AE25-C1F551DA578E      3000.00      171000.000000      0.0000
CFB5A1EC-CB1E-44E4-B594-55136681997D      A2910AA4-4D19-4497-AE25-C1F551DA578E      499.00      27195.500000      0.0000
1CEAB3DB-1D31-4214-AF77-F6AB7AF110FA      0492E3A2-7490-4778-804B-0040971BADA9      0.00      0.000000      155.7270
1CEAB3DB-1D31-4214-AF77-F6AB7AF110FA      0492E3A2-7490-4778-804B-0040971BADA9      62.57      2252.520000      0.0000
1CEAB3DB-1D31-4214-AF77-F6AB7AF110FA      31E94D82-0139-4542-84D0-BF035EB4A756      0.00      0.000000      2000.0000
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
tomfogarty

ASKER
This looks like it's right. I've tested with my own data and it looks OK. I'm deploying to a live testbed to check. If I want to add other joined details such as custoemr information I presume I have to add them into the group clause as well.
PortletPaul

>>If I want to add other joined details such as customer information...
"that will depend"
you can "nest" the current group by and join other tables from it, but you want to be careful you don't re-expand the number of carefully reduced rows.

You could do this inside the CTE's? (then you have to carry through all additional fields into the final group by.)

There are 2 keys to play with (SalesProposalID, ProductID) to get more text with (e.g. client name, product name) - without intimate knowledge of the data model all I can do at this point is offer general advice.
tomfogarty

ASKER
I'm splitting the points. Sharath did the spade work and PortlePaul got it over the line. Good solution. Thanks.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.