By using this website, you agree to our Terms of Use (click here)
Can someone please explain to me what the Schema Field does in the GI Results Grid and how it interacts with the Data Field?
Also, how do the Aggregate Functions settings impact the results? Thank you!
Aggregates: If you decided to set a group, let's say you're grouping all your PO Lines by your PO Number, then you need to decide how to display the value for things like "Cost" at the line level. Did you want to see which line has the highest cost per PO? Use MAX. Want to see the cost for all the items together? Use SUM. These only affect values who are grouped. The system just wants to know what to do with that pile of values you grouped together.
Schema: I find this to be largely useless. What it does it copy attributes. Want to use 6 decimal places instead of 2? Set that field to a schema who has 6 decimal places. For example my inventory items only display 2 decimals for length, if I set the schema to being my item's volume, it'll now show 6 decimals. I find this useful ONLY when I need to shorten a value. Say my boss doesn't care that I can show him 6 decimals of volume. I may try to apply a schema with only 1 or 2 decimals when I display the volume in my GI.
Additional point on the Schema column. I've found that I pretty much always need it when I do a calculated column because Acumatica doesn't know how to format the result. It's a little weird though the way you pick a column with the format that you want. I kind of wish it would function like the Format field in Report Designer where you could either choose a field that has the format you want or put in a format code. Note though that it isn't only applying formatting, it also determines whether or not you will get a hyperlink on the record because some fields have that functionality built-in.
Additional point on the Schema column. I've found that I pretty much always need it when I do a calculated column because Acumatica doesn't know how to format the result. It's a little weird though the way you pick a column with the format that you want. I kind of wish it would function like the Format field in Report Designer where you could either choose a field that has the format you want or put in a format code. Note though that it isn't only applying formatting, it also determines whether or not you will get a hyperlink on the record because some fields have that functionality built-in.
1. Do you know of a Field that has no decimal places that can be used?
2. Can you provide a link to more info on format codes?
Thanks!
Adding on to the original question:
Can i aggregate with an If condition?
Example:
To get the Qty sold of each item i use the ARTran table, group by Inventory ID, add inventory ID and Qty fields to the results and make the aggregate function SUM.
That part is easy enough.
Next i want an additional column that shows the sum of Qty for each item for a specific customer (lets assume the customer can be hard coded in the GI). How can this be achieved?
I tried adding a condition =iif([ARTran.CustomerID]='Mycustomer',ARTran.Qty,''). and get this error:"Conversion failed when converting the varchar value 'Mycustomer' to data type int."
So i tried adding cStr to the condition like such :=iif(cStr([ARTran.CustomerID])='Mycustomer',ARTran.Qty,'') and got this error :"Error converting data type varchar to numeric."
I also tried adding ARtran twice (the second time with an alias) and joining the alias as a child to the parent with a condition that the customer ID is equal to 'Mycustomer'. This one does not error, but i get no results.
Any ideas?
You don't need single quotes around your CustomerID. I'm really not sure why, but I hit that wall before when trying to set it in the conditions tab. If it still fails after removing the single quotes around Mycustomer, please reply back. I have a quite a few ARTran GI's that I can easily modify to try and replicate your behavior, as do many of the others on this forum.
oh interesting
Which of the failed examples are you suggesting this for?
I tried that in all 3 and i get "Error: A field with the name Mycustomer cannot be found."
i will also clarify after rereading my post, i did not add a condition in the conditions tab, but rather added a condition in the formula in the results tab.
Adding a screen shot:
I assume Mycustomer is a customer ID in your system correct? For example I have customer ID: ALB-TOLLES and the description is Albertson's Tolleson. So I would use ALB-TOLLES without quotes for the comparison, I would NOT use the description text. Iff should use the same semantics as the conditions tab. If Mycustomer is a parameter, you need to use parameter notation for it, which would be [Mycustomer], although I've never tried running a check against a parameter in the results grid.
It's not possible to do the type of comparison you are attempting inside a formula. To see why, add the field CustomerID to your query results and mouse over the exclamation mark that exists. Customers are Business Accounts. They are stored as an ID and a CD (code) field, ie. BAccountID and AcctCD. To match to the actual string value, you need to be joining against acctCD and you need to use quoted strings.
You should be able to join BAccount to your query (join ARTran.CustomerID to BAccountID) and then you can apply criteria against BAccount.AcctCD to match to specific customer IDs (using quoted strings).
In GIs you need to be careful as what can be displayed as a Result can not always directly be used in Formulas. If there is an exclamation in the result column, it means that Acumatica is doing some smarts behind the scenes that aren't done inside a formula.
Was just about to post that solution!
There were 2 issue with the original formula.:
1. As you mentioned, i couldn't use the Id from Artran. so i joined the customer table( same as the BAccount in your case).
2. I added cdbl() to the true part of the formula, which have numeric value.
Ended up with: =iif([Customer.AcctCD]='Mycustomer',cdbl(IIf( [ARTran.DrCr]='D', -1, 1 )*[ARTran.Qty]),0)
I also grouped by Inventory ID and then CustomerID
Thanks for your explanation @Royce Lithgo
1. How about SOOrder.Priority, will that work?
2. There is a really good Report Designer Formatting section in the S130 Reporting: Data Retrieval and Analysis 2017 R2 (click here) course. Click here and scroll to page 86 to see it:
Online Members
