Answer

Joining Relations to Formulated Fields In Acumatica GI

Forum|Forum|6 months ago
June 4, 2025
3 replies
91 views

B

+1

bodiec
Semi-Pro II

I have a GI that is pulling another GI in as a data source.

Both GIs have a similar Formulated field that is just the code for the month.

“=Year([SOOrder.OrderDate]) * 100 + Month([SOOrder.OrderDate])” which should return something like this: 202506 meaning 06 2025 (June). And they both do this on their own correctly.

But when I try to bring the other GI in as a data source, the formulated field is not an option in the relations tab which I believe is normal, so I re-enter the expression into the active relations:

=Year([SOOrder.OrderDate]) * 100 + Month([SOOrder.OrderDate]) equals =Year([AMBatch.CreatedDateTime]) * 100 + Month([AMBatch.CreatedDateTime])

But for some reason, the system can’t find [AMBatch.CreatedDateTime] as an actual field?

Which doesn’t make any sense because I can pick the [AMBatch.CreatedDateTime] as a field by itself, but when it’s in this expression it cannot find it?

The idea for this is, I want to have a simple GI that is grouped by month, with a column for the total qty we have on sales orders that month, and a column for the total qty of FG that we produced that month.

Again, both of these GI’s work on their own, but I would like them to be on the same GI. I don’t really want to just make a dashboard and place them next to each other.

Does anyone know why the system might not be able to find [AMBatch.CreatedDateTime] as an actual field in the relations expression?

Best answer by lauraj46

Hi @bodiec,

When referencing a formula field in the child GI, use the GUID field name that Acumatica assigns. You should be able to find this by clicking on the formula builder pencil next to any of the fields in the Results tab. The names aren’t descriptive, so you may need to add the fields to the output results to see which is which. Once you’ve identified the proper fields, use these references on the joins in your Relations tab (e.g. =[SOOrderDate.SOOrder_Formula2465277a75ef4aa3bd863ff66aa3e1ca]

Hope this helps!

Laura

+8

lauraj46
Captain II
Answer
Forum|Forum|6 months ago
June 5, 2025

Hi @bodiec,

When referencing a formula field in the child GI, use the GUID field name that Acumatica assigns. You should be able to find this by clicking on the formula builder pencil next to any of the fields in the Results tab. The names aren’t descriptive, so you may need to add the fields to the output results to see which is which. Once you’ve identified the proper fields, use these references on the joins in your Relations tab (e.g. =[SOOrderDate.SOOrder_Formula2465277a75ef4aa3bd863ff66aa3e1ca]

Hope this helps!

Laura

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.

B

Like

B

+1

bodiec
Author
Semi-Pro II
Forum|Forum|6 months ago
June 5, 2025

Thanks @lauraj46

I am still getting an error saying, “This Expression contains an invalid name:”

But in the results grid of the same GI, it does recognize the calculated field and does not give me an error, do you have any idea why that could be? I have simplified both GIs as much as I can.

Am I supposed to put the formula in the relations tab in a different format? And should I be concerned that when I open the fields in the relation tab, none of the formulas are displayed, I have just been manually pasting the formula from the results grid.