Are you using the () in the subquery list to group the appropriate subs together? CTE’s seem to require it for some BAQ’s
Also, do not forget the Publish and Subscribe features. This may help isolate and solidify each component of your logic and needs. Jusst another tool in the belt.
for the Exploaded BOM, I found I had to use CTE, then UNIONALL and then the third query was the Top Level… but now that I know about the Parenthesis… maybe it doesn’t need to be in that order.
No I haven’t used the () in the subquery list. Are there any training videos out there for E10 that focus on Sub queries and all of the features/tools available to use?
There appears to be a bit more functionality available that I am not familiar with.
Thanks to everyone for the tips / help.
Carol
That’s how I have mine set up with the CTE, then the UNION ALL and the 3rd query is the Top level. But I need to have another CTE; UNION ALL, and then join those results to the first top level. I would love to view a training video on the use of the () or the publish & subscribe feature. That may be what I need.
I will have to check out You Tube to see if there is anything available.
Thanks much.
most of the advice here is spot on. You can create it in E10. Those kind of queries are fun to do.
Most of the CTE are a min of three queries.
The anchor query, the union query, and the select of the CTE. Sounds like you are in the right direction.
3 Likes
It is a complex query and thanks to all of the advice & tips I did get it working.
I am using this same query in a dashboard and I am using publish / filter.
The field being published is a calculated field. (Expr2), but I am not seeing this on my 2nd grid to filter on.
Is it not possible to filter on a calculated field?
Once again … thank you!
Carol
You should be able to filter on whatever is published. Calculated or not.
Not sure why the field I want to filter on is not displaying. Here is my grid and I am publishing my “Calculated_Expr1” column.
In my 2nd grid which is using the same query when I highlight a row whatever is in Column Expr_1 I want to have returned every row that matches that, but Expr1 is not available to filter on.
This works wonderful in E9.05.701 … can’t figure out what would be different.
If I add a grid view the filter is there, but when I select a row it is not filtering. I’ve done this many times in E9 and it works fine. Not sure what I may be missing.
If I highlight a row only those rows that match Expr1 should be returned in the 2nd grid. For example I want only the users with ESTMGRPL to be returned in the 2nd grid.
Actually in E9 I added the same query and filter off of it, but this doesn’t work in 10
Expr1 isn’t even in the list to filter on
If you can see anything I might be doing wrong, please enlighten me… thanks
I finally called Epicor support and he did a webex and he could not get it to work either. He said that because it is no longer Progress based calculated fields will not work for Publish and Subscribe. I will have either have to back to using a sequel view or figure out a different way to do this.
Thanks for all of the help.
I did not realize that calculated fields did not show up in the filter list. When I was reading these this weekend, I was thinking that they didn’t show up for the filter value.
I see the same thing you do. I would supposed that they do this because they want to be able to “parameterize” the results of the grids for performance purposes (like when you put a value in a tracker on a dashboard) to make it run faster. If you are using a calculated field, they can’t control the inputs and so you could really kill your performance when clicking from one grid to the next.
I think there could be ways to make it work. For that field (Expr1) what’s the use case of that field? Is that something that you could populate on an extended UD field instead of calculating at the time of running the BAQ?
Brandon,
This is a rough outline of what I have for my query calculation and below some screenshots of how my dashboard works in E9.
The screen shot’s didn’t come through right.
Can you just explain what that field is for?
Brandon,
It’s a bit difficult to explain other than the Supervisors and Managers are required to review the security and menu accesses of the employees that report to them and they want to be able to do this review in its entirety in a dashboard.
In order to achieve this in Epicor9 I have views created and an external query utilizing these views to bring them all into one BAQ and then create the dashboard from that.
The views have the calculations in them and the query is just the means to get everything in the dashboard where I can Publish and filter the fields that I want.
Epicor support confirmed this morning that calculated fields cannot be used to filter on. (See their response below)
“Hello Carol, unfortunately since E10, it is not possible to publish calculated fields, as calculated fields do not exist in memory, they do not exist on the table, and thus cannot be selected as If it was an existing field, the reason it worked in E9 is because it used a different language that allowed for that, but E10 uses SQL, you can publish any existing field on the table, or you can create a new UD field with the calculation for it to be added to the table and exist in memory, then you can do the publish and susbscribe for that field, try to do the publish and subscribe with a field that is not a calculated field
I did ask them how I would create a new UD field with the calculation, but have not heard back yet.
If you know how I can create a new field with a calculation that would list each entry in the UserFile.Grouplist separately, rather than a tilde delimited field, please let me know.
Thanks much!
Carol
so I had a bunch of stuff typed up and then started paying attention to the comment about the tilde delimited field. Now I know why you need those fields to be calculated. I wasn’t getting that before, I was thinking we could figure out how to just use the database fields.
They call that “refer you to Epicor Professional Service” haha.
You would have to deploy and do a customization, but it might be possible to make your own publish/subscribe to use a “matches” or “contains” filter so that you don’t have to chop up the tilde delimited field. I would take a little C# to do it though.
This is a tough one to give good suggestions for since I am not close enough to the needs to understand all of the requirements that need to go into it.
My unfounded Gut feeling is that something in this equation is more complicated than it needs to be, but I can’t tell you what.
Thanks for your time in looking at this. Epicor did respond back and basically said this is not possible to do with a UD field and that calculations will not work for Publish and Subscribe.
I will find a way to do it. Will just take a bit more thought.
Thanks again.
Carol
Well, this is how you can concatenate rows, do the opposite. (probably not helpful)
This is thread that shows how to add a filter on a grid from a text box. You can probably make this work by getting the value of the cell in the row, and using the contains filter in the grid (so you don’t need to pull apart the tilde fields). It hijacks the grid filter which isn’t ideal is the user what’s to use it, but if that’s not a problem then I think you could get it to work.
You might need @Chris_Conn to help with the C# on getting the row values of the selected row.
Lol so basically they DONT exist? Haha I think this was misspoken. Ultimately whether or not you can publish a calculated field, I do not know, although I would image you can, it’s just a value that is broadcast to the subscribers to why not.
I’ve only read bits and pieces so what I am about to type may have 0 relevance.
var myEDV = oTrans.Factory("edvNameHere");
var currentRow = myEDV.dataView[myEDV.Row];
int myString = currentRow["StringFieldNameHere"].ToString();
//assume myString is a concat'd ~ string
string[] values = myString.Split('~');
//now values is an array of your values
foreach(string val in values)
{
do stuff
}