Follow

Custom conditions on Conditional Lookup fields

If you need to use a custom condition on the condition field in a Conditional lookup, you might have it look like this.

It should not be run unless Inventory Posting Group is not empty.

1.PNG

2.PNG

I got an error

The error message.

Ambiguous column name 'Inventory Posting Group'.

Details:

SQL Server: 'TX-TL'
SQL Procedure: 'usp_Item_Clean'
SQL Line Number: 250
SQL Error Number: 209

Ambiguous column name 'Inventory Posting Group'.

The script part that failed.

-- Copy data for the conditional lookup field 'Inventory Posting Group Description' (Algorithm: One lookup, Group by)

; WITH CTE AS
(
	SELECT
		MAX(S.[Description]) AS [~Value]
		, S.[Super Inventory Key]
	FROM [DSA].[Inventory Posting Group] S
	GROUP BY
		S.[Super Inventory Key]
)
UPDATE R
SET
	R.[Inventory Posting Group Description] = CTE.[~Value]
FROM [DSA].[Item_R] R
INNER JOIN [DSA].[Item_T] T ON
	T.[DW_Id] = R.[DW_Id]
INNER JOIN CTE ON
	CTE.[Super Inventory Key] = T.[Super Inventory Key]
WHERE
	[Inventory Posting Group] IS NOT NULL 

Why is does this give an error

You will notice the message is about ambiguous columns and this means that there are two versions of the same field. You can see that in the failing script, at the where statement at the bottom. You will need to prefix it with either T for the transformation view or R for the raw table. Either table will work, but you have to choose the one that best fits the condition.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.