Skip to main content

Created a conditional lookup based on following :


T1 joined to T2 where T1.A = T2.B


Looked up T2.C based on the above join condition and stored the value in T1.D


Now I need to filter the resultant data set which is on T1 based on certain values of T1.D


Unable to put that filter in the Table Data Selection Rule.


Even I tried to copy T1.D(look up filed) to T1.E (a separate filed created just to hold the value of T1.D) and filter on T1.E, even that did not work.


Appreciate help on the same.

Hi Supriyo, 


Jan, Thank you for your great description of the process and suggestions. I also have another suggestion in mind. 


You can create a custom data selection rule. I'll show an example using PurchaseOrderHeader and PurchaseOrderDetail from the Adventureworks database. These two tables encounter the same issue as your situation, where the order date is only located in the header table. 


So once you have brought both tables into the solution


  1. Right click on PurcahaseOrderDetail > Add Data Selection Rule. 

  2. Then in the bottom of the Data Selection Pane on the right, be sure the operator is set to Custom, then click Add. This opens up a scripting window. 

  3. I can then use the following script to filter the PurchaseOrderDetail table based on the OrderDate located in the Header table:

lPurchaseOrderID] > =

(SELECT MIN(PurchaseOrderID)
FROM PurchaseOrderHeader
WHERE OrderDate >='01-01-2014')


This method is actually filtering on the PurcahseOrderID (the join of the two tables) based on the OrderDate of my choice. 


One limitation here is that you would need to ensure the PurchaseOrderHeader table is executed prior to the PurcahseOrderDetail. But if you are doing lookups into Detail then this would always be the case. 


Once done, you can click OK and Deploy/Execute the table to validate your changes. The final solution should look something like this:_1Bv03UukM7X3LzBOIy7TA.png


Hope that works for you, let me know if you have any questions. 


Happy Developing!


 


 

Thanks Jan and Joseph for your responses.


Though I have a question:


In the 'Data Fields' section of the 'Custom Selection Rule' window the PurchaseOrderID from PurchaseOrderHeader is not available. Do I have to pull all the columns from looked up tables in the main table on which I have to the put the filters?

Hi Supriyo, 


Nope, in this case you just type in the table/field names. If you like you can first type the where clause into SSMS so you have the benefit of intellisense then copy everything after the "Where" and paste it into this window. 

Reply


Terms & ConditionsCookie settings