PSA for Application Studio: If you SORT by the column in the BAQ, you must include the column in the DISPLAY fields

Kinetic ERP
1

I am sure I read this somewhere here, but I can’t find it now.

So, I’m doing a normal Application Studio app/dashboard thing today, and the grid would not load. BAQ works fine though.

Did F12 in the browser, says something like, Bad SQL statement; check server log for error. Bogus, but OK.

Server log says, Invalid column name 'Project_BuildSequence_c'. Again, not true, but OK.

I was using that column to sort the BAQ, but I have no reason to display it in the results, so I didn’t.

After this error, though, I added the field to the “Display Fields” list in the BAQ.

Tested it again. Guess what? It works now.

This is also true of a BAQ grid on your home page. (That was not always the case, but it is now – since 2022 maybe?)

In summary, if you put the field here in the BAQ…

image
image1133×325 13.5 KB

…you must also put it here in the BAQ:

image
image1166×529 27.8 KB

I did not need to display the field in App Studio, though. Just in the BAQ

[EDIT: This seems to be only related to UD fields.]

4 Likes
2

That’s been a thing, even in classic, for as long as I can remember.

3 Likes
3

That’s not even an Epicor thing, that’s a SQL thing.

5 Likes
4

I absolutely believe you both, but I’ll say, this is a rule I have broken for a long time then!

Like I say, the BAQ designer does not have a problem with me not displaying the columns even now. And I have to believe that classic dashboards were not always this way.

I’ve always tried to keep to a minimum the number of fields I display in a BAQ. For the simple fact that it’s cluttered and that makes me sad lol.

2 Likes
5

Can you show me an example of where you are breaking that rule? Not saying you’re lying, but I’m also saying I’m not sure I believe you, :stuck_out_tongue_winking_eye:

2 Likes
6

What am I misunderstanding in this thread?

image

4 Likes
7

I thought that was required, but you are right @GThom , it’s not. I learned something I guess.

Question for @JasonMcD , is that the only UD field that you would be using in the query? I’m wondering if it uses the ERP table instead of the DBO view if it’s not bringing back a UD field. For a test, can you show some other custom field, than try to order by this field?

1 Like
8

Oh great, now I cannot replicate this.

So, I’m having the opposite issue now - it’s WORKING everywhere.

I made a BAQ identical to @GThom 's SQL statement. For that same BAQ:

  1. In BAQ designer, it works
  2. Created Classic dashboard and deployed as Classic application; it works
  3. Created Kinetic app from that Classic dashboard; it works in client
  4. Tried same app in browser; it works in browser
  5. Created a Kinetic app from scratch; it works

I did obviously have an error the other day though…

1 Like
9

Ahhh. Maybe that’s the trick. My experiment a few seconds ago was not a UD field. Hold on…

10

I just tested that here, and it still works even with UD fields.

I’m gonna assume that there is some level of complexity somewhere that triggers this issue. I have seen the error like @JasonMcD posted, so it’s a common thing, but it seems to be situational.

@JasonMcD , is that field coming from a subquery? (in your original post)

Edit: tested that too, no error.

Looks like a fun new years riddle to figure out how to create that error again. lol.

11

No, I disagree that you are wrong lol. I think I can confirm you were right about the UD fields.

Here I modified the BAQ to sort by a UD field:

image

And it works in classic dashboard but not Kinetic.

image
image1419×694 35.5 KB

And there’s the error:

image
image869×261 23 KB

12

That’s the difference. I didn’t try it in Kinetic. Looks like the editors are doing something different. That’s probably worth a ticket to say it works differently from one system to another. Although, since the workaround is so easy, I kind of doubt you’ll get much traction.

13

Ugh, I know you’re right.

Complaining is easier.

1 Like
14

Also worth tagging @Rich … I wonder if there is some underlying issue in the browser with the EpiMagic behind the whole “auto joining” of the erp.abcd and erp.abcd_UD sql tables?

1 Like
15

When you make the dataview in App Studio are you bringing in all the columns?

16

I mean, all of the columns displayed in the BAQ, yes.

Do I go and manually add the “missing” sort column to the data view? No I don’t.

Man, for all that I’d just add it to he BAQ.

17

I was wondering if the dataview would automatically bring it over but there goes my theory.

18

I did submit a ticket for this 11 days ago. Still no response. I asked today for a response.

Any harm in me sharing my case number? It’s not sensitive info, is it?

Also, @Olga since you supported me on a smart-client parity gap before, I’ll ask for a lifeline again, if you don’t mind.

[Edit: Well… apparently I forgot to report the other parity gap @olga helped me with 3 months ago. :man_facepalming: But I reported it now… :confused:]

19

I cannot say anything else except “report it”. :woman_shrugging:
If you wonder what is the difference, then you can dump SQL statements generated for both dashboards in the log and compare them.
Flag is:
<add uri="profile://ice/fw/DynamicQuery/BaqStatement" />

2 Likes
20

Just chiming into say I got bitten by this recently too but hadn’t taken the time to report it - if the BAQ executes successfully then the app studio layer shouldn’t fail. I can see the app studio layer includes the field names of the sort by fields (UD or not) in the call to the BAQ. This seems like a bug to me, and potentially related to the bug where app studio also automatically includes fields from the subquery that are not part of the top level query. Would love to see a PRB on this.

2 Likes