By using this website, you agree to our Terms of Use (click here)
I'm having to divide the total by 100 to get the correct value. Any ideas why this is the case?
SPA_Attributes = 1.00
ARTran.Qty = 25
Expected results: 25.
Actual result: 2500 (without divide by 100)
=(cDec([InventoryItem.SPA_Attributes]) * cdec([ARTran.Qty]))/100
Is the 1.0 maybe a percentage that's getting stored as 100?
I'm not sure what SPA_Attributes represents but maybe it would be easier to evaluate if it didn't equal 1 and maybe more awkward, so it's obvious to us if/when it works? Maybe something like 0.8 or 1.2 kind of deal?
alternatively... is there any chance there's a many to one relationship without your knowledge, like maybe on of the two field has 100 instances of the value? again, might be easier for this example to have SPA_Attribute not equal 1 which could help lead us down a more appropriate path.
😉
@jmck Actually I also have a 0.75 in that value on others. It simply represents a dollar amount. The attribute is an attribute I set up for inventory items
So the expected result is 25 * 0.75 = $18.75. The actual result is $1,875.
Here is the attribute setup:
We have it set up as an attribute on a stock items. My assumption is it should just be stored as 1.00. @Timrodman may be on to something in that Acumatica may assume it's a percentage. Not sure why though.
@travisl hrmm... my gut is telling me it could be a grouping issue...
A general practice of mine is to build a GI that lists each filed on it's own line within the RESULT GRID which helps confirm the intentions or assumptions being made.
Intentions or Assumptions
- SPA_Attributes = 1.00
- ARTran.Qty = 25
...but what if they don't when running through the GI?
I'd suggest you add them both into the RESULT GRID as is and using the conversions so...
- SPA_Attributes
- cDec([InventoryItem.SPA_Attributes])
- Qty
- cDec([ARTran.Qty])
You might find that...
- one of them is 10 times what you thought it should be
- things magically start to work
- cDec is not having the desired results
...tough to predict but this should help
Either way, my gut is telling me to suggest you...
- add a unique identifier in the mix to ensure that there's no unexpected grouping going on
- double check the grouping
- double check the type of join between the tables
Welcome to my method of trouble shooting... I do this a lot since I'm accustomed to SQL so, in my head, there seems to be loads of unexpected result with GI's.
Good luck! 😉
@jmck Thanks for the response.
Here's the results. SPA Rate is the attribute and SPA Payment is the cDec. Interesting that cDec is turning it from 0.50 to 50. That explains why dividing is required. No odd grouping - there isn't any. Also I can reconcile each individual line so it's not duplicating or grouping.
@travisl Does this mean the "problem" has been resolved?
One of my practices is to layout a formula in a way that makes sense when I read it...
Yours is =(cDec([InventoryItem.SPA_Attributes]) * cDec([ARTran.Qty]))/100
Mine would be =(cDec([InventoryItem.SPA_Attributes])/100) * cdec([ARTran.Qty])
I know it works out the same BUT when I review the formula in the future then I'm more likely to remember what the /100 is for this way.
imho, it's still quit curious to me why cDec is doing this to [InventoryItem.SPA_Attributes]. I wonder if it has something to do with the original data type?
😉
@jmck I did actually change the formula to be =(cDec([InventoryItem.SPA_Attributes])/100) * cdec([ARTran.Qty]) afterwards. It's not really "resolved" per se. Just have to be creative with the solution by dividing by the 100 to get the result I need.
I was mostly curious why the cDec was converting the decimal to a whole number.
I did find something on Acumatica's forum though using entry masks. I have 0.00. They show using 0.### so maybe changing the mask to #.## would yield the correct result. Thinking out loud.
https://community.acumatica.com/reports-and-generic-inquires-115/creating-a-numeric-attribute-18027