Solved

How to return a only single desired DAC fields based on the conditions from other tables?

  • 5 February 2023
  • 4 replies
  • 196 views
aaghaei
Rank
Userlevel 7
Badge +8

Hello Community,

I was wondering if is there a way to retrieve only the values of a single table based on the conditions from other tables. Something equivalent to SQL

SELECT
	TableA.* /*ONLY I want TableA*/
FROM
	TableA INNER JOIN
	TableB ON TableA_BID = TableB_BID /*Join TableA & TableB - One-to-one Relation in my case*/
WHERE
	TableB_CID IN (SELECT TableC_CID FROM TableC WHERE TableC_CD = 'MyCondition') /*Filter Result Based on TableC*/

 I know I can join tables and apply conditions in the WHERE statement, (for Example if I Join APInvoice with APTran) but the problem is when I join tables I will have the redundant APInvoice per APTran line and I want to avoid it also I do not want the APTran fields. I also know I can perform GroupBy but it is a headache and in my opinion, a dumb way to approach it. 

I only want to return a single table and its records that meet the criteria from other tables without redundancy caused by joining with other tables.

icon

Best answer by aaghaei 9 February 2023, 03:30

View original
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*.

4 replies

darylbowman
Rank
Badge +11

Pretty sure you would have thought of this already, but if you use parameters to filter instead of joining the other tables, you won’t have joined tables returning.

 

For instance:

SelectFrom<TableA>.
    Where<TableA.field.IsEqual<@P.AsInt>>.
    View.Select(Base, tableB.TableBID);

 

Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman
aaghaei
Rank
Userlevel 7
Badge +8

Thanks @darylbowman 

I wish it was that simple 😂

your assumption is I have the fields I want to filter in my DAC I want to return results from but I do not have them. The fields I want to filter, are in my joined table(s). For example I want all APRegister that it’s APTran has a specific InventoryItem or CostCode.

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*.
aaghaei
Rank
Userlevel 7
Badge +8

I was looking for something else but accidentally I came across “IsInSubselect” which is introduced in FBQL and apparently does exactly what I need. though not possible to do in BQL what I need. This is a sample I found on Acumatica

Where<GLTran.accountID.
  IsInSubselect<SelectFrom<ARTranPost>
     .Where<ARTranPost.tranType.IsEqual<GLTran.tranType>
     .And<ARTranPost.tranRefNbr.IsEqual<GLTran.refNbr>>>
     .SearchFor<ARTranPost.accountID>>>>

 

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
Rank
Userlevel 7
Badge

Thank you for sharing your solution with the community @aaghaei !

Chris ~ Let us know how we're doing: https://forms.office.com/r/xmJUdZ1QE5 - A member helped you, please *Like* their post. A member provided an answer to your question, please mark it as *Best Answer*.

Reply


Terms & ConditionsCookie settings
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