Hello everyone and thank you in advance!
I am attempting to build a BAQ related to our new DMR internal process. The BAQ is attached for reference. The goal I am trying to accomplish is to create a subquery (since I do not think it possible to do on the top level) that displays two field calculations telling the user if a particular DMR has any files attached (calc 1), and the total number of attachments (calc 2).
As far as the calculations goes I think I have that worked out. The problem I have is creating the subquery from DMRHead to XFile.Attch which establishes if there are any files attached to a DMR record. The second table I need in the subquery, XFileRef tells you the name/location/type of file that is attached. Why Epicor structured attachments in such a overly complicated way is beyond me. Would have made more sense to have a single table or just nest attachments in the actual DB table they reside, but that’s another discussion…
What I am looking for is the right way to construct a subquery via summation or the “Count” calculation that does not create duplicate rows for each attached file. If, for example, DMR 1074 has two files attached and you run the BAQ, my expectation is that there is an “Attachments ?” field displaying Yes/No if there are any attachments to the DMR record and a second field “Number of Attachments” does a Count or some kind of summation telling the user the total number of attached files.
I have tried every combination I can think of when creating the subquery and either get zero results or duplicate records for each attached file type. Hoping someone on here can help get me on the right path. If imported, remember to remove the UD fields denoted with a _c at the end otherwise will not work for you.
Order of operations which I am not sure about. There are Table/SubQuery Criteria on both the top level tables as well as the subquery tables. Hopefully part of the answer to this question will illustrate what you fill out on the Table/SubQuery Critiera and where these should be populated since they exist on the top level table you are joining the subquery to, the subquery as it exists on the top level, and the subquery itself. Again Epicor probably could have designed this a little better to make more sense.BNI_DMR_With_Attachments.baq (63.0 KB)
- Build top level BAQ query.
- Create new subquery (Inner Join?) with XFileAttch and XFileRef joined together.
- In new subquery “Attachments” create SubQuery Criteria linking Key1 (DMR #) to DMRHead.DMRNum?
- In Top Level BAQ, click on DMRHead and create SubQuery Criteria to Attachments?
- Add Subquery Attachments to top level BAQ and link to DMRHead using DMRNum to Key1?
We are on-prem and using 10.2.300.23 currently.
Bug note - every time you create a SubQuery Criteria, Epicor changes your Field to Company whenever you tab out of said field requiring you to change it back to the field you want to link the top level to the subquery.
Thank you!