By using this website, you agree to our Terms of Use (click here)
GI noob here......Are we limited to the functions that show up in GI?
I tried using IIF(And(XX>20,XX<30),blah blah, yada yada). Didn't work or I did something wrong.
I want to calculate a value in the results grid only if a DateDiff value falls between two numbers.
That stuff works just fine, but the syntax can be rough at the start. I would suggest using the conditions tab and choosing the "is between" condition and setting the first value to 20 and the last value to 30. If you have more issues, feel free to post your code directly or export your GI into an XML file and attach that file here. Most of us are very hands-on and would be happy to get you rolling by addressing your specific issue.
Please note that a GI condition only accepts your final answer in boolean. So your iif needs to evaluate out to True or False.
Â
EDIT: And yes, we only get the functions shown to us in the Formula Editor in the GI & Report Writer.
Ya, nothing beats the ease of Excel functions. But GI is different. I'm still not sure if the GI functions are more like C# or SQL, but definitely not Excel.
=IIF([INTranDetail.TranType]<>'ADJ',(IIF(( DateDiff( 'd', [INTranDetail.TranDate], Today())>90 AND DateDiff( 'd', [INTranDetail.TranDate], Today())<181),[INItemSite.AvgCost]*[INSiteStatus.QtyOnHand],0)),0)
Passes validation (most things do) and doesn't bomb when running the query. Only problem is it ignores the AND. It calculates a value for anything >90.....
I'm trying to age my inventory and put it into a category based on date range. Example: A Ware is <=90 days, B Ware is >90 days and <180, etc for C Ware, D Ware....
Once I fix this, I have to figure out why a KPI tile on a dashboard won't sum this column. Returns 0.00.
I don't have your rpx/xml so I can't test this, but can you try this line? I've explicitly contained both sides of your AND in parens as I'm not sure if this isn't a semantics issue when the system parses out your line:
=IIF([INTranDetail.TranType]<>'ADJ',(IIF(( (DateDiff( 'd', [INTranDetail.TranDate], Today())>90) AND (DateDiff( 'd', [INTranDetail.TranDate], Today())<181)),[INItemSite.AvgCost]*[INSiteStatus.QtyOnHand],0)),0)
Â
Thanks, I'll try it. I "think" I successfully sent you the xml.....
You have tables in your GI that I don't have in my system, so testing is not super feasible on my end. Tim will likely be hopping back in here again. If you didn't send him your XML, you might wish to do so. He has more flexibility (and knowledge) in this system than I do. So far your logic seems right to me. If enclosing the two halves of the AND statement doesn't solve your dilemma, I highly recommend poking at Tim.
You're a rockstar Michael. Thanks so much for jumping in on these. I love it when you beat me to the punch.
Russ, I just tried your .xml file on my SalesDemo database.
I think the problem is that your DateDiff formulas are returning negative numbers since they are calculating [INTranDetail.TranDate] minus Today() rather than Today() minus [INTranDetail.TranDate]. Try flipping them around like this (taking Michael's formula here and modifying):
=IIF([INTranDetail.TranType]<>'ADJ',(IIF(( (DateDiff( 'd', Today(), [INTranDetail.TranDate])>90) AND (DateDiff( 'd', Today(), [INTranDetail.TranDate])<181)),[INItemSite.AvgCost]*[INSiteStatus.QtyOnHand],0)),0)
Also, note, if you want to get that nice "code" effect here in the forums, just select any line in your text and click this button on the menu:
And it will give the "code" formatting like this:
=IIF([INTranDetail.TranType]<>'ADJ',(IIF(( (DateDiff( 'd', Today(), [INTranDetail.TranDate])>90) AND (DateDiff( 'd', Today(), [INTranDetail.TranDate])<181)),[INItemSite.AvgCost]*[INSiteStatus.QtyOnHand],0)),0)
Flipping didn't work..... 🤬Â
Hmmm, this is strange. I was expecting flipping to provide the opposite sign, but apparently that's not the case.
Maybe you can spot something that I'm missing. Here is what I'm seeing.
I added two columns:
Today - TranDate with the following formula
=DateDiff( 'd', Today(), [INTranDetail.TranDate])
And TranDate - Today with the following formula:
=DateDiff( 'd', [INTranDetail.TranDate], Today())
Then I ran the Generic Inquiry and got this:
What's strange is that I was expecting the second and third columns to have the same values, just with the sign flipped. On some lines that is indeed the case (like with 4/1/2016), but on others they pull two completely different numbers. And I'm not sure why.
Here are the DateDiff numbers according to Excel:
Can you spot some kind of pattern?
Is that a trick question? That is bizarre. I was going to tell you that I got the formula to work:
=IIF(( DateDiff( 'd', [INTranDetail.TranDate], Today())>90 and DateDiff( 'd', [INTranDetail.TranDate], Today())<181),
[INItemSite.AvgCost]*[INSiteStatus.QtyOnHand],0)
Do you think the query results are related to multiple date records in the query? That's what doomed by Aging GI.
Even though the formula works, if I have multiple receipts on an item, it shows up it more than more aging column.
Being able to use a GI as a table for a GI would be a dream come true....
If you want to "nest" tables like that, the report writer will allow you call a sub-report(s) and it's pretty phenomenal. You can pass in params from the master report to the sub and call variables from the sub along with directly passing values from the sub.
My SME's and I had to make a really funky setup to create a time-phased inventory report and sub-reports were the only way we could find to solve the problem.
I agree @michael-hansen, calling sub-reports in Report Designer is nice. Having the same kind of thing with nested tables in Generic Inquiry would be nice too.
Not a trick question @russ-williams, I'm baffled too. Maybe it's a bug in my Acumatica version.
So it seems like you got the formula working, but now you're having trouble with just some of the records?
Can you paste a screenshot showing which records are correct and which are not?
Online Members
