Skip to main content
Answer

Need to Modify Query of a Projected DAC

  • June 16, 2023
  • 8 replies
  • 342 views
J
Jr Varsity II
Forum|alt.badge.img

Hi,

I need to modify ARTranForDirectInvoice Project DAC.

Existing Query In DAC

[PXProjection(typeof(Select2<ARTran,
	  LeftJoin<INTran, 
		  On<INTran.FK.ARTran>>,
	  Where<ARTran.released, Equal<boolTrue>,
		And<
			Where2<
				Where<INTran.released, Equal<True>, 
					And<INTran.qty, Greater<decimal0>,
					And<INTran.tranType, In3<INTranType.issue, INTranType.debitMemo, INTranType.invoice>>>>,
				Or<
					Where<INTran.released, IsNull, 
					And<ARTran.lineType, In3<SOLineType.miscCharge, SOLineType.nonInventory>,
					And<
						Where2<
							Where<ARTran.qty, Greater<decimal0>, 
								And<ARTran.tranType, In3<ARDocType.debitMemo, ARDocType.cashSale, ARDocType.invoice>>>,
							Or<
								Where<ARTran.qty, Less<decimal0>,
								And<ARTran.tranType, In3<ARDocType.creditMemo, ARDocType.cashReturn>>>>>>>>>>>>,
		OrderBy<Desc<ARTran.refNbr>>>),
		Persistent = false)]
	[PXCacheName(AR.Messages.ARTran)]
	[Serializable]

I need to remove And<INTran.qty, Greater<decimal0> condition from above query.

How I do that.

Best answer by aaghaei

@jeewanishalika20

sorry for typos. Writing on cellphone.


The projection DACs in Acumatica are equivalent of SQL views. I don’t think there is a way to alter built in Projections behavior. I am not sure about the extent of this projection DAC and its purpose of usage in your case but here are alternate options you have. Depending on your use case one or all my work. I ordered as easiest to most difficult below. please note the below option1 can work if inside the graph the view is not casted as the Projection DAC otherwise the platform will fail to explicitly convert one to another.

  1. copy the existing DAC and change the Projection query then in your graph extension override the view selecting from the original DAC to the Custom DAC
  2. wWrite a view delegate instructing the platform to read from custom DAC instead of original. If the view has already a delegate you will override the existing 
  3. A little bit of hacking by overriding the view in graph initialization and directing the existing view to read from a new one. The below is an example for a different scenario but logic is the same. See Yuriy’s response also can be found in his blog.

 

robk20

8 replies

Naveen Boga
Captain II
Forum|alt.badge.img+19
  • Naveen Boga
  • Captain II
  • June 16, 2023

Hi @jeewanishalika20  I have removed the below condition and please find the query below.

And<INTran.qty, Greater<decimal0> 


	[PXProjection(typeof(Select2<ARTran,
	  LeftJoin<INTran,
		  On<INTran.FK.ARTran>>,
	  Where<ARTran.released, Equal<boolTrue>,
		And<
			Where2<
				Where<INTran.released, Equal<True>,
					//And<INTran.qty, Greater<decimal0>,
					And<INTran.tranType, In3<INTranType.issue, INTranType.debitMemo, INTranType.invoice>>>,
				Or<
					Where<INTran.released, IsNull,
					And<ARTran.lineType, In3<SOLineType.miscCharge, SOLineType.nonInventory>,
					And<
						Where2<
							Where<ARTran.tranType, In3<ARDocType.debitMemo, ARDocType.cashSale, ARDocType.invoice>>,
							Or<
								Where<ARTran.qty, Less<decimal0>,
								And<ARTran.tranType, In3<ARDocType.creditMemo, ARDocType.cashReturn>>>>>>>>>>>>,
		OrderBy<Desc<ARTran.refNbr>>>),
		Persistent = false)]

 

Naveen B - Acumatica MVP 🏆 | Independent Developer for Hire 🚀 ⏩ https://www.linkedin.com/in/boga-naveen-kumar-54079a66/
aaghaei
Chris Hackett
J
Jr Varsity II
Forum|alt.badge.img

Hi @Naveen Boga ,

In this case I need to know Should I need to create a DAC extension from above DAC and need to add query mentioned by you.

Naveen Boga
Captain II
Forum|alt.badge.img+19
  • Naveen Boga
  • Captain II
  • June 16, 2023

Hi @jeewanishalika20  Sorry, I’m not clear with the above question. Can you please elaborate?

Naveen B - Acumatica MVP 🏆 | Independent Developer for Hire 🚀 ⏩ https://www.linkedin.com/in/boga-naveen-kumar-54079a66/
J
Jr Varsity II
Forum|alt.badge.img

I need to Change query in ARTranForDirectInvoice DAC file.

I’m clear with how to modify it.But,I need to know How to add my modified query into DAC.

Can we change ARTranForDirectInvoice.cs file or need to create DAC extension to add the modified query.

Naveen Boga
Captain II
Forum|alt.badge.img+19
  • Naveen Boga
  • Captain II
  • June 16, 2023

No, we cannot change the ARTranForDirectInvoice.cs file, either you need to inherit or extend the DAC and check once.

Naveen B - Acumatica MVP 🏆 | Independent Developer for Hire 🚀 ⏩ https://www.linkedin.com/in/boga-naveen-kumar-54079a66/
Chris Hackett
J
Jr Varsity II
Forum|alt.badge.img

@Naveen Boga ,

If I created a DAC extension and can I add the modified query into it.

aaghaei
Captain II
Forum|alt.badge.img+10
  • aaghaei
  • Captain II
  • Answer
  • June 16, 2023

@jeewanishalika20

sorry for typos. Writing on cellphone.


The projection DACs in Acumatica are equivalent of SQL views. I don’t think there is a way to alter built in Projections behavior. I am not sure about the extent of this projection DAC and its purpose of usage in your case but here are alternate options you have. Depending on your use case one or all my work. I ordered as easiest to most difficult below. please note the below option1 can work if inside the graph the view is not casted as the Projection DAC otherwise the platform will fail to explicitly convert one to another.

  1. copy the existing DAC and change the Projection query then in your graph extension override the view selecting from the original DAC to the Custom DAC
  2. wWrite a view delegate instructing the platform to read from custom DAC instead of original. If the view has already a delegate you will override the existing 
  3. A little bit of hacking by overriding the view in graph initialization and directing the existing view to read from a new one. The below is an example for a different scenario but logic is the same. See Yuriy’s response also can be found in his blog.

 

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
Chris Hackett
JKurtz29
Varsity II
Forum|alt.badge.img+1
  • JKurtz29
  • Varsity II
  • March 6, 2025

 

An answer here:
 

https://stackoverflow.com/questions/41536269/extend-acumatica-projection-based-dac-query

 

 

Jerry Kurtz | Senior Director Application Development | JKurtz@algorithminc.com
Chris Hackett
Terms & ConditionsCookie settingsAccessibility statement
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved