Condition on generic inquiry for formula in results grid?

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 

@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

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 
    }

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/

I found the way to have that @sean21 

Firstly, do not put any condition in style yet. you just run GI and put Sum in column Aggregate Function or use express in your data field like Sum( DataFile). 

When you run the GI to to show trace you will see the column name generated by Acumatica like Object_Formular_somthing.

You copy that column name and put it in your Style with condition you one then it should work. Please not that that column data type is string as default data type, if you want to do compration you need to convert it to your data type and you can have comparison. 

I am using 2023R2