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

3 Comments

  • 0
    Avatar
    Joost Romijn

    How do you create a custom Condition? I can only see the predefined conditions (Empty / Like / Equal etc.)

  • 0
    Avatar
    Thomas Lind

    Hi Joost

    You can make custom conditions by pressing add and not choosing a table to base the condition on.



    The reason is you cant see it in your picture is that you chose Planned Start Date.

  • 0
    Avatar
    Joost Romijn

    Thanks!

Please sign in to leave a comment.