SmartList Builder and creating Calculated Fields with Extender data
Just recently, I ran into a case where the partner was creating a SmartList Builder calculated field using data from the RM Open table (RM20101) via an Left Join table operation with the Extender Window Field Numbers. In fact, this is a very typical scenario for a lot of deployments where Extender is used, especially when you cannot use the standard Extender functionality to integrate with out-of-the-box smartlists.
The original SLB calculated field look something like this:
When the SLB smartlist was deployed, a number of results came back as zero for the records where there was no entry in the Extender Window Field Numbers table, even when the Sales Amount and Current Trx Amount fields had a value in the RM Open File table.
Paying a bit more attention to the issue made me think of how LEFT OUTER JOINs are processed by the Microsoft SQL Server query engine. This is best illustrated with the following example:
Note that if Extender data was not entered for INV1020, a left outer join query would produce a NULL value as a result of the join operation. To overcome this situation, we applied the T-SQL ISNULL() function to the Extender field in SmartList Builder. Since SmartList Builder uses pass-through SQL to build each portion of the SELECT statement used to retrieve the records, then this should work just fine. The final calculated field is as follows:
Remember, Extender is a valuable tool to capture additional data and enhances the value of SmartList Builder when combined together to deliver reports. Pay special attention when creating calculated fields that rely on information from Extender tables in left join scenarios.
Until next post!
MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/
The original SLB calculated field look something like this:
 |
| Extender Calculated Field |
When the SLB smartlist was deployed, a number of results came back as zero for the records where there was no entry in the Extender Window Field Numbers table, even when the Sales Amount and Current Trx Amount fields had a value in the RM Open File table.
Paying a bit more attention to the issue made me think of how LEFT OUTER JOINs are processed by the Microsoft SQL Server query engine. This is best illustrated with the following example:
| RM Open | Extender | ||||||
| Customer Number | Document Number | Sales Amount | Current Transaction Amount | PT UD Key | PT UD Number | Total | |
| AARONFIT001 | INV3223 | 200.00 | 40.00 | INV3223 | 5.00 | 5.00 | |
| ADAMPARK001 | INV1020 | 100.00 | 20.00 | NULL | NULL | NULL |
Note that if Extender data was not entered for INV1020, a left outer join query would produce a NULL value as a result of the join operation. To overcome this situation, we applied the T-SQL ISNULL() function to the Extender field in SmartList Builder. Since SmartList Builder uses pass-through SQL to build each portion of the SELECT statement used to retrieve the records, then this should work just fine. The final calculated field is as follows:
Remember, Extender is a valuable tool to capture additional data and enhances the value of SmartList Builder when combined together to deliver reports. Pay special attention when creating calculated fields that rely on information from Extender tables in left join scenarios.
Until next post!
MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/
Comments