Possible to use a table UNION in SmartlistBuilder?

(0) Share

Report

Posted on by Community Member Microsoft Employee

I have two tables that I would like to pull data from where the document numbers are in a single column. Looking to get Sales Trx History SOP Numbers and RM Receivables Transactions Document Number data to show both SOPs and CreditMemos/DebitMemos in a single column as posted for a specified date range. So far it seems I'm missing something in the options to join a SQL Script, as it only returns table data if I enter only the "Select * from XXXXXXX" table name and doesn't allow for entry of actual script language to use UNION statement. Is this beyond SmartlistBuilder capabilities since it's not a JOIN and I want to see the information in a single column with associated document date, amount and Document Description in the case of CM/DM?

*This post is locked for comments

All responses (2)

Answers (1)

Community Member Microsoft Employee on at

Like (0)

Report

RE: Possible to use a table UNION in SmartlistBuilder?

Thank you very much, Susan! I appreciate your insight here.
Verified answer

sueconrod 335 on at

Like (0)

Report

RE: Possible to use a table UNION in SmartlistBuilder?

You can build it a view in SQL and then point to the view thru SmartList Builder. That way you can do all your scripting and then just pull the data. When we have complicated queries that pull from multiple tables it is a lot easier to do it as a query originally and then turn it into a view and point at the view. The only issue is that you have to put the view into multiple companies.