In an effort to improve your experience, we have a simple story to build out in Kinetic, but we need some more data. I know that many of you can probably help add to the list.
I created an Idea Populate Standard Data Table JOINS for BAQ Usage as a starting point, but we could use your input.
Building BAQs already supports standard joins… if you put two tables into the BAQ, it will create the link (in most cases) between the two tables, and automatically join the appropriate fields… but there are some cases where the system doesnt know anything about the join. This is because the data is simply one in the zdatatable structure.
We want to rectify that… so:
Vote on the above idea
COMMENT on the above idea and add any other tables that we are missing from the list aready in the idea.
@timshuwy , I think this is a great idea. I also believe that the idea was already submitted by @JasonMcD a couple of years ago. You might want to merge the ideas if they are the same.
I won’t vote for this simply because there are so very many other ideas that are vastly more important to me. It would be nice if more of the standard joins were there but its so trivial to add the ones I need I wouldn’t even categorize this as a pain point.
Also, the way to get the standard cost of a part (or average cost, etc.) is on par with that.
Again, I understand the place for the details of that is Ideas, not here. I’m just brainstorming.
EDIT: So on that note, something well beyond this discussion is, it would be amazing if the BAQ editor might say “I see you are trying to join PartBin to PartPlant. The best-practice way to do that is like this…” (Gently implying that you probably should not do so directly - it’ll work in a one-warehouse setup, but not when you exand down the road…)
Just some additional thoughts on the topic:
I would expand on this by adding the ability to search thru your existing BAQs and allow you to copy a join you have already created.
Something like “Right Click” search thru BAQ Joins for a list of suggestions. This would then cover item’s like all the Joins I do to User Defined Codes which is custom to my company but I do repeatedly. Also I have a couple of joins I do that I know are BAD joins and would not be part of the product to share across the board but since I my case I have a limited amount of data and hey they work for me. Also copying from existing BAQs helps make all my BAQs very consistent. Even possibly adding a field to BAQs so you can flag it as a TEMPLATE BAQ for better defining that you have specifically flagged this a one to copy joins from.
Could that be done somehow using the Like Table, Like Field, and Linke Columns properties in Extended Property Maintenance? If the like’s are set, the relationships could be set automatically based on the existing ones for that field…
Note that just because there are other ideas, doesnt mean that this could not be done in paralell… we have multiple teams, and this specific idea would be done by a team that might have some extra time. Also, it is an “easy” item, becuase it is simply adding relationships to the relationship data that is in our golden database.
THIS is why we need to have input from you all to know which tables you are continually trying to join.
ALSO, we are trying to improve performance in areas such as where people create poor performing BAQs because they dont join them correctly.
funny, i just got done adding these to the idea… Part to PartPlant to Plant (to get the cost id) to Warehouse to PartWarehouse (to get the quantity) and finally to PartCost (to get the cost). it takes 5 tables to get the part number, description, quantity and price. One of the more difficult queries to build.
I personally have mixed feelings about the join helpers. More often than not I have to clean up after - fix which table it’s joining to, change the join type, review the fields because some get skipped, and know that sometimes the auto-join includes unlicensed fields and breaks the query. If users need join helpers to function, they’re going to struggle with these common events. When join helpers aren’t a bridge for skill gaps, they give a small time savings on specifying the join.
Where join helpers save the most time is for users who don’t know you can type in the field name instead of mouse scrolling through its hundreds of comrades. It’s unfortunate that doesn’t work in WHERE.
If you have examples of the system generating invalid joins, we would like to know about that. I do know that sometimes when you join the Order to the Customer table, it will join the BTCustomer instead of the Customer number. But there is also a combo box asking you which of the two joins you want to use.
Many times, we are finding that people create inefficient joins. for example they leave out the join to the COMPANY field, which makes SQL work harder to get to the data you want. Our joins should include the full key to get to the data.
It’s not that they’re invalid, but that the relationship between tables supports a variety of use cases. JobProd to OrderRel is a great example. Sometimes I want to fetch the whole order, sometimes I want all of the releases for the order line, sometimes I only want the single release. Inserting an additional interaction where the user must select from a list of the cartesian product of all possible joins removes the context of users reasoning stepwise through what they’re doing, and creates new work for those users when they don’t need it.
The biggest time expense I’ve found for joining this to that is finding what tables that a field might live in. Searching top down (table → field) requires assumptions about context and the spelling of table names. Searching bottom up (field → table) would be a powerful addition. Basically, the equivalent of:
select
t.name
,c.name
from sys.tables t
join sys.columns c
on t.object_id = c.object_id
join sys.types ty
on ty.user_type_id = c.user_type_id
where 1=1
and c.name like '%job%'
and ty.name = 'nvarchar'
Finding what tables to join to often takes exponentially longer than identifying fields in those tables. It’s true that zdatafield exists, but requires closing out our BAQ session and writing a separate BAQ.
I merged these two ideas, and added notes to the bottom of the bigger idea. Eventually this idea will be pushed over to an Epic as one big set of things to fix. Note that the list has grown based on my personal experience as well as a few notes I have gathered from here and in the comments of the idea. Please post your comments inside the idea of any other joins. Thanks.
I just saw this question… so… this is a proposed gap filler in the way that we defined our database. MOST of the standard tables have standard predefined joins available. When you use, say OrderHed, OrderDtl, and OrderRel, the system will automatically link them for you in a BAQ. BUT if you attempt to brgin in JobOper and JobMtl, the Job Material is not automatically joined, even though there is a logical join that could/should be used.
THIS idea is to help fill in those gaps so that users who are creating their own BAQs have the extra work already done.
Thank you for merging the custom join idea in there also. Since you indicated this was a “in the team’s spare time” task, I didn’t want to pile on a whole new interface. But if it makes sense to do it all at once, all the better.
@timshuwy - Humor me - there are no NEW join definitions in 2023.2 that are not in 2023.1 or 2022.2, right? (Not including joins for brand-new tables.)
My point is, I am planning to skim through some of my BAQs in 2022.2 and see if they have any joins that may be of interest. But I don’t want to include one that Epicor already fixed.
I’m presuming that the join dictionary has been pretty stagnant for the last few years, save for some new tables that were added.
I feel like this could be something you query out of peoples databases to find all of the most common joins. You could literally make a BAQ to pass around, and send in the results of your BAQ to be able to pareto out the most common tables that are joined and the joins that they use.
Relying on self reported data (what I feel like I need) is a pretty unreliable source.
/*
* Disclaimer!!!
* This is not a real query being executed, but a simplified version for general vision.
* Executing it with any other tool may produce a different result.
*/
select
[QueryRelation].[ParentTableID] as [QueryRelation_ParentTableID],
[QueryRelation].[ChildTableID] as [QueryRelation_ChildTableID],
[QueryRelation].[JoinType] as [QueryRelation_JoinType],
[QueryRelationField].[ChildFieldName] as [QueryRelationField_ChildFieldName],
(count(1)) as [Calculated_MyCount]
from Ice.QueryRelation as QueryRelation
inner join Ice.QueryRelationField as QueryRelationField on
QueryRelation.Company = QueryRelationField.Company
and QueryRelation.QueryID = QueryRelationField.QueryID
and QueryRelation.SubQueryID = QueryRelationField.SubQueryID
and QueryRelation.RelationID = QueryRelationField.RelationID
and ( not QueryRelationField.ChildFieldName in ('ForeignSysRowID') )
group by [QueryRelation].[ParentTableID],
[QueryRelation].[ChildTableID],
[QueryRelation].[JoinType],
[QueryRelationField].[ChildFieldName]
This gives you a list of the joins from table to table and counts. This would give you a place to start looking for the most commonly used ones.
