Skip to main content
Solved

Is it possible to UNION two tables in Generic Inquiry?


There are two tables with same colomns. Is there anyway to get the union result using Generic Inquiry?

Best answer by MPushpakumara48

nhatnghetinh wrote:

Hi @MPushpakumara48 

You can use Cross 2 DACs in Generic Inquiry but it will create many duplicate lines of data.

Another way you can create a new DAC is by creating a View in SQL and Union 2 tables in SQL.

 

Best Regards,

NNT

Thank You!

View original
Did this topic help you find an answer to your question?

3 replies

nhatnghetinh
Captain II
Forum|alt.badge.img+11
  • Captain II
  • 395 replies
  • July 11, 2024

Hi @MPushpakumara48 

You can use Cross 2 DACs in Generic Inquiry but it will create many duplicate lines of data.

Another way you can create a new DAC is by creating a View in SQL and Union 2 tables in SQL.

 

Best Regards,

NNT


  • Author
  • Acumatica Employee
  • 2 replies
  • Answer
  • July 11, 2024
nhatnghetinh wrote:

Hi @MPushpakumara48 

You can use Cross 2 DACs in Generic Inquiry but it will create many duplicate lines of data.

Another way you can create a new DAC is by creating a View in SQL and Union 2 tables in SQL.

 

Best Regards,

NNT

Thank You!


lbarker
Varsity II
Forum|alt.badge.img+1
  • Varsity II
  • 75 replies
  • July 11, 2024

I use an attribute called MYPIVOT that is a combo, and I add values 1 to 10 as values with sort orders.

Then I use that in my joins.  Add CSAttributeDetail to your inquiry.

The one link to the main table of data has a join of attributeID = ‘MYPIVOT’ and SortOrder is greater than or equal to  2 so it creates 2 lines per main table data row.

Then u join the sub table of data to main but in your join u set the CSAttribututeDetail.sortorder=1. 

The second sub table has a join where the sort order =2

And in your inquiry fields you can do formula’s if you only want data on the first row for instance like

=Iif([CSAttributeDetail.SortOrder]=1,[ARTran.Qty],0)

 

 


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings