Problems using TOTAL in a baq

Epicor ERP 9
1

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:

  1. Created a BAQ with 2 tables, “PORel” and “RcvDtl”, the joins were company, ponum, poline, porelnum.
  2. Made the “RcvDtl” table a summary table.
  3. 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.

image
image.png1230×683 239 KB

Any advice would be much appreciated.

2

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

3

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

4

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.

image
image.png355×580 102 KB

5

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

6

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

7

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

8

Are you grouping by PONum or anything?

9

image
image.png594×568 126 KB

10

I mean GroupBy on the column screen

11

Hi Chris,
No group by applied

12

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 :frowning:

13

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?

14

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!

15

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.

16

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

image
image.jpg1111×556 187 KB

17

Trying making PORel and RcvDtl both summary tables?

18

Only RcvDtl

19

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

20

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