Solved

Condition on generic inquiry for formula in results grid?

  • 16 November 2021
  • 8 replies
  • 79 views

Userlevel 3
Badge

This may be a noob question, but how can I set a condition on the generic inquiry for a data field based on a formula? See below.

In the results grid, I have a formula that is calculating GP%. I want to set a condition on the GI to display only projects with a GP% lower than 25. How can this be done on the GI? 

 

 

 

 

BONUS: 

Alternatively, if someone could help me figure out why this business event is not firing, I wouldn’t have to filter the GI in the first place. 

 

 

icon

Best answer by TimRodman 17 November 2021, 04:22

View original

8 replies

Userlevel 7
Badge +10

Hi @sean21  Can you provided this condition (Less then 25%) in the Result Grid Formula it self, instead of providing in the Conditions tab and verify.

If this is NOT working, can you please share the XML format of GI here 

Userlevel 4
Badge +1

When I’ve encountered this situation, I usually create a Parameter on the PARAMETER tab, set that Parameter to not be visible and have a Default Value of true, then use the Parameter in the Data Field column on the CONDITIONS tab. Does that make sense?

Userlevel 3
Badge

@TimRodman thanks for your feedback. This makes sense for sure, but I’m missing something… 

What schema field would you use for the parameter in that case? Since the column on the results grid is through an if statement formula, how can I tie that to the parameter or condition? Since I want to see GP% under 25.00, should I modify the formula in the condition to if(GP% formula < 25.00, True, False). 

I’m scratching my head on this one, but I feel like I’m just missing something obvious… 

This is the formula for GP%: 

=iif(sum(iif([PMBudget.Type] = 'I',[PMBudget.Amount],0) + iif([PMBudget.Type] = 'I',[PMBudget.ChangeOrderAmount],0)) = 0,0 ,sum((iif([PMBudget.Type] = 'I',[PMBudget.Amount],0) + iif([PMBudget.Type] = 'I',[PMBudget.ChangeOrderAmount],0)) - (iif([PMBudget.Type] = 'E',[PMBudget.Amount],0) + iif([PMBudget.Type] = 'E',[PMBudget.ChangeOrderAmount],0)))/sum(iif([PMBudget.Type] = 'I',[PMBudget.Amount],0) + iif([PMBudget.Type] = 'I',[PMBudget.ChangeOrderAmount],0))) * 100

Userlevel 3
Badge

Hey @Naveen B - here’s the XML of the GI in case you want to explore. You can see my comments above regarding the challenge I’m currently facing. I feel like the answer is obvious LOL, but I’m just not getting it! 

Userlevel 7
Badge +10

Hi @sean21  Hope you are doing well.

I worked on this GI all possible ways to achieve this requirement. I think there is one solution for this, please find my observations and solution details below.

  •  Since we need to apply the condition on top of formula/output data set, we can not apply the conditions on output data set in GI CONDITIONS tab.
  • I tried applying this condition in RESULTS Grid data field, but still we can see all the records.
  •  Also, tried apply this condition in RELATIONS tab in GI condition but Acumatica is not allowing to save the GI due to length issue (since formula is too big).
  • Finally, also tired with the customization, this one solving the problem very simple way. Please find the details below.
    • You can introduce a new unbound field in the PMBudget table and with the GP% formula.
    • Then you can filter the records by apply the condition directly in the CONDITIONS tab.
    • Please find the code sample for your reference.
 public class PMBudgetExt : PXCacheExtension<PMBudget>
{
#region UsrGPPercentage
[PXDecimal]
//[PXDBCalced(typeof(Sub<SOLine.orderQty, SOLineExt.usrTestField>), typeof(decimal))] -- Here you can add the GP% formula
[PXUIField(DisplayName = "Available for Shipment")]
public virtual decimal? UsrGPPercentage { get; set; }
public abstract class usrGPPercentage : PX.Data.BQL.BqlDecimal.Field<usrGPPercentage> { }
#endregion
}

 

Userlevel 3
Badge

@Naveen B you sir, are the MAN! Thank you so much. You’re breakdown is a perfect summary of the issue. I wanted to see if this was possible without customization, but guess not. I’ll grab your snippet of code and get this uploaded. Thank you! 

Userlevel 4
Badge +1

To Naveen’s point about applying the condition on top of formula/output data set, I missed the SUMs in your formulas. That probably means that you are grouping right?

If you were not grouping, then, for the Parameters idea, you could put any field in the Schema Field column that is a text field. Then your Default Value column could be set to True, Yes, or whatever you like.

Then your screenshot here is exactly what I was suggesting:

In Value 1, your formula would look like this, depending on what you put in Default Value earlier:

IIf(some logic,'Yes','No')
IIf(some logic,'True','False')

The No and False values don’t really matter.

 

But, since you are grouping, this idea might not work. Here is a workaround though for when you are grouping:

https://www.augforums.com/forums/acumatica-generic-inquiries/using-parameters-to-filter-aggregate-results/

Userlevel 3
Badge

@TimRodman thank you! I did do that, but ran into issues. That’s probably where the grouping came into play. Appreciate your assistance as well and I’ll check out the forum post you provided! 💪

Reply


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 — 2020  Acumatica, Inc. All rights reserved