Problems using TOTAL in a baq

Rowley150 · April 30, 2019, 10:29am

epicor version 9.05.701

Problem: I get the below error when adding the total function to a summarised table. I want to sum the “ourqty” field of the RcvDtl table against each PO/LINE/REL.

What I’ve done:

Created a BAQ with 2 tables, “PORel” and “RcvDtl”, the joins were company, ponum, poline, porelnum.
Made the “RcvDtl” table a summary table.
Added a calculated field with the formula: Total( RcvDtl.OurQty )

If i remove the calculated field altogether the BAQ runs fine, so it is nothing to do with the other calculated fields…its something to do with this new one i’ve written.

Any advice would be much appreciated.

jeowings · April 30, 2019, 11:19am

Are you sure it is the received qty? The error is calling out a different field it looks like.

**Josh Owings **| JR Automation

ERP Applications Manager

O: +1
(864) 397-9193 | C: +1 (864) 884-6587

Rowley150 · April 30, 2019, 11:47am

its only when i add the receivedqty calculated field that the error happens

Rowley150 · April 30, 2019, 11:55am

UPDATE:
The baq works when i join rcvdtl to podtl as opposed to porel and doesn’t error however some crazy received qty’s being returned. Doesn’t look right at all.

jeowings · April 30, 2019, 12:29pm

Can you add that field and remove the other one as a troubleshooting step just to be sure?

Rowley150 · April 30, 2019, 12:48pm

yes - i just removed all the other calculated fields altogether and just left the one…it still came back with crazy qtys

Rowley150 · April 30, 2019, 12:57pm

im wondering if it needs more in the syntax than just TOTAL (rcvdtl.ourqty)…maybe it needs to have something specific about the PO and line

Chris_Conn · April 30, 2019, 1:59pm

Are you grouping by PONum or anything?

Rowley150 · April 30, 2019, 2:07pm

Chris_Conn · April 30, 2019, 2:09pm

I mean GroupBy on the column screen

Rowley150 · April 30, 2019, 2:15pm

Hi Chris,
No group by applied

Chris_Conn · April 30, 2019, 2:29pm

Hey Mark, I am just realizing this is E9 - disregard anything I’ve said lol. I have no experience in E9 so I have no idea how much is the same as E10

snielsen28 · May 1, 2019, 1:07pm

I’ve received that error before when analyzing or checking syntax, but found the BAQ will run without errors. (We’re on 9.05.701 also.) Have you tried that?

Rowley150 · May 1, 2019, 5:55pm

Hi
Yes I did try that but got a blue circle that looked like it was trying to do something but didn’t stop… eventually had to kill it as it would killed the server!

snielsen28 · May 2, 2019, 1:14pm

Can you post a screenshot of the Phase Builder? Epicor 9.05.701 has some bugs using summarized tables. You can only have 1 “branch” that summarizes; if you add a 2nd branch that has summarized tables nothing will be returned for it. There are some older posts about it (under “9.05.702 issues w/summary tables” topic). BTW…9.05.702 fixed that, but cross-company BAQs broke.

Rowley150 · May 2, 2019, 2:07pm

I have taken the “IsSummary” off for now, so i’m getting multiple rows.

snielsen28 · May 2, 2019, 2:24pm

Trying making PORel and RcvDtl both summary tables?

Rowley150 · May 2, 2019, 2:33pm

Only RcvDtl

snielsen28 · May 2, 2019, 3:03pm

Trying making both summary tables, but leave the total set on RcvDtl table.

zwilli526 · May 4, 2019, 7:50pm

Do you even need to create the total calculated field? PORel.ReceivedQty should give you that info without needing the RcvDtl table.