Solved

How to embed SUM function in an IIF statement of a GI condition

  • 27 November 2023
  • 10 replies
  • 202 views
J
Rank
Userlevel 4
Badge

I am trying to use the following statement of a Data Field in the Results of a GI.

=iif([PrevPDO.EffectiveDate] >= '2023/06/24',[EmployeePDOHours.PDOHours]-[PrevPDO.HoursUsed],[EmployeePDOHours.PDOHours]-SUM(Iif([PMTask.TaskCD] = '10',[PMTimeActivity.TimeSpent]/60,0.00)) + [EmployeePDOHours.HoursUsed] )

When I run the GI it errors out with a GROUP BY clause error.

However, when I take out the SUM(Iif([PMTask.TaskCD] = '10',[PMTimeActivity.TimeSpent]/60,0.00)), I can run the GI without an error, but it does not calculate what I want as I need to include the SUM of the time activity based on that Task ID.    I can put that SUM(Iif([PMTask.TaskCD] = '10',[PMTimeActivity.TimeSpent]/60,0.00)) into another GI Results row and it returns the correct value, so it IS a correct forumla.  It simply does not want to work inside of that other IIF statement.  Is there something I can do to make this work?

(If there is even a way to create a GI variable with that SUM value and then referenced in the IIF statement that would be fine also).


Thank you,

 

icon

Best answer by NetAdmin42 14 December 2023, 14:14

View original

10 replies

Robert Sternberg
Rank
Userlevel 7
Badge +8

Hi Jeff, can you attach your Generic Inquiry so we can take a look.  Using SUM values in IIf statements should be possible.  Can you also let us know if you have an Aggregate function assigned on the line already in the “Aggregate Function” column.  

 

In the meantime something like this might work

IIf([PMTask.TaskCD] = '10',-SUM([PMTimeActivity.TimeSpent]/60),-0.00)

 

Thanks!

Robert Sternberg, Crestwood Associates -- ** Summit 2024 - Visit Us at Booth #600 ** -- If a community member helped you, please *Like* their post/reply. If they provided an answer to your question, please mark it as *Best Answer*.
J
Rank
Userlevel 4
Badge

Hi Robert, I can post that GI, sure, however, it references some custom DAC tables which are not part of the base Acumatica.  Oh, and no, I am not setting the Aggregate on this row.  I tried that to, but did not seem to work

I did try your statement and had a similar GROUP clause error.


 

 

And to clarify, using SUM in the statement below works perfectly without errors, but does not return the value I want.  So, the issue appears to be embedding SUM() inside of IIF() does not work, but you can embed IIF() in a SUM() function.  Not sure why you cannot do that.
 

 

Robert Sternberg
Rank
Userlevel 7
Badge +8

The GI would be helpful.  It would also help to know more about the intended function of the GI.  Is there a reason you cannot add a group by of TaskCD? 

Robert Sternberg, Crestwood Associates -- ** Summit 2024 - Visit Us at Booth #600 ** -- If a community member helped you, please *Like* their post/reply. If they provided an answer to your question, please mark it as *Best Answer*.
J
Rank
Userlevel 4
Badge

Hi Robert,

Here is the GI attached.  Basically it is a very simple thing I am trying to accomplish.  Based on a customization that contains the inputted value of a person’s PDO time on the Employee screen, I want to subtract out the total amount of time from the PMActivity table that someone has requested for time off, collectively throughout the calendar year.

For example, someone starts the year with 100 hours of PDO time and then over the year from the PMActivity table they will request time off.  The SUM function iterates the PMActivity table, totals up the requests for the year and subtracts it from the total.   There are 3 main columns.  Total PDO time, Time Used and Time request.  So, someone who has 100 hours might have used 40 hours, and then requested another 20, which would be 100-(40+20) = 40 hours remaining.  Very simple. 
 

All of the columns are working, I simply need to subtract that one column, (from example, the 20 hours), from that total.
Make sense?

BTW, I even tried something very wonky like combining multiple IIF statements, (see below), and still ended up with the GROUP BY clause error.

         What worked : (assuming 100 - 40 = 60)

=iif([AKTEmployeePDOHours.Pdohours] = null, [PrevPDO.Pdohours],[AKTEmployeePDOHours.Pdohours]) - iif([PrevPDO.EffectiveDate] >= '2023/06/24' ,[PrevPDO.HoursUsed],[AKTEmployeePDOHours.HoursUsed] )

         What failed : (assuming 100 - (40 + 20) = 40)

=iif([AKTEmployeePDOHours.Pdohours] = null, [PrevPDO.Pdohours],[AKTEmployeePDOHours.Pdohours]) - (iif([PrevPDO.EffectiveDate] >= '2023/06/24' ,[PrevPDO.HoursUsed],[AKTEmployeePDOHours.HoursUsed] ) + SUM(Iif([PMTask.TaskCD] = '10',[PMTimeActivity.TimeSpent]/60,0.00)))

NOTE:

I would be happy if I could even do something like this where I have to use multiple statements to set a memory variable

@PMVariable = SUM(Iif([PMTask.TaskCD] = '10',[PMTimeActivity.TimeSpent]/60,0.00)))

=iif([AKTEmployeePDOHours.Pdohours] = null, [PrevPDO.Pdohours],[AKTEmployeePDOHours.Pdohours]) - (iif([PrevPDO.EffectiveDate] >= '2023/06/24' ,[PrevPDO.HoursUsed],[AKTEmployeePDOHours.HoursUsed] ) + @PMVariable)

Thank you for your help

Jeff

 

1 Attachment
Robert Sternberg
Rank
Userlevel 7
Badge +8

A couple of questions now that I start to review. 

  1. Can we add a condition TaskCD = 10
  2. Why are we grouping by AcctCD when our Primary Table is Employees?  Is there duplication somewhere if we don’t, do we know why?
  3. What Acumatica version are you running?
Robert Sternberg, Crestwood Associates -- ** Summit 2024 - Visit Us at Booth #600 ** -- If a community member helped you, please *Like* their post/reply. If they provided an answer to your question, please mark it as *Best Answer*.
J
Rank
Userlevel 4
Badge

A couple of questions now that I start to review. 

  1. Can we add a condition TaskCD = 10  (sure, I can add that)
  2. Why are we grouping by AcctCD when our Primary Table is Employees?  Is there duplication somewhere if we don’t, do we know why?  (I was testing to see if that was part of the problem.  Also, I noticed that if I do not include some kind of grouping, then that statement of SUM() will throw an error for any of the other DAC columns in the IIF statement.)
  3. What Acumatica version are you running?  Unfortunately, this is 22 R1 build 103

 

J
Rank
Userlevel 4
Badge

This is beginning to look like there is simply no way to do with a GI what I want to do.  Even though I WAS able to do what I want to do with a RPX using Report Designer.  But I do not really want a “report” I want to do this as a screen GI.

So, if anyone knows how to take this RPX and convert it to a GI, please let me know.

Thank you,

Jeff

1 Attachment
Robert Sternberg
Rank
Userlevel 7
Badge +8

A couple of questions now that I start to review. 

  1. Can we add a condition TaskCD = 10  (sure, I can add that)
  2. Why are we grouping by AcctCD when our Primary Table is Employees?  Is there duplication somewhere if we don’t, do we know why?  (I was testing to see if that was part of the problem.  Also, I noticed that if I do not include some kind of grouping, then that statement of SUM() will throw an error for any of the other DAC columns in the IIF statement.)
  3. What Acumatica version are you running?  Unfortunately, this is 22 R1 build 103

 

This looks like you are getting closer, I also reviewed the RPX and I do not see anything that stands out as impossible in a generic inquiry.  I did see you are using a variable which obviously does not exist in a GI however it it just to gather the SUM which is possible.  

 

It’s a little more difficult for my testing without source data or the custom DAC definitions (however the RPX does help).  

 

I think the key to resolving this is in numbers 1 and 2 above. 

 

Number 1 drops the need for the IF side of the SUMIF (if we are always grabbing TaskCD=10 we do not need to have it as a condition) I noticed that carried over to the RPX on the join, that is acceptable in the GI as well. 

Number 2 leaves us with a couple of options, a. completely drop the group (without any group you cannot use aggregate functions, seems like that is not an option), b. keep the group and add surrogate aggregate functions for columns requiring an aggregate or Group By.  For example the MAX of 1, 1, 1, and 1 is 1 therefore the aggregate is acceptable.  (Adding Group Bys would also be an option)

 

If this is getting to be too complex to reliably implement you can create a SQL view and publish it as a customization project. 

 

Robert Sternberg, Crestwood Associates -- ** Summit 2024 - Visit Us at Booth #600 ** -- If a community member helped you, please *Like* their post/reply. If they provided an answer to your question, please mark it as *Best Answer*.
N
Rank
Userlevel 4
Badge

First, my original account was disabled due to my changing to a new company, so I am using this new account to provide a solution for this issue.

The only way I could make this work the way I wanted to was to create a SQL view and make all of the calculations and iterative summations there and then use a GI to display them.  That is really the only way this will work.

Robert Sternberg
Rank
Userlevel 7
Badge +8

@NetAdmin42 

thanks for sharing your solution! 

Robert Sternberg, Crestwood Associates -- ** Summit 2024 - Visit Us at Booth #600 ** -- If a community member helped you, please *Like* their post/reply. If they 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