Hi all, I have created a dashboard with two BAQs, one shows all open jobs for all customers , the other filters the materials for each job as you select on the first BAQ. I have a row in my materials for JobMtl_IssuedComplete. What I am trying to do is once all of the material rows are issuedcomplete, I want to have a bitcalc field on the first BAQ to show that the job is issued so at a glance we can quickly see if a job is ready.
I have looked for specific fields that do this in everything job related and cannot find anything I can use as a trigger and am surprised there isnt one…am I just being blind or does one not exist? or what would be the simplest approach to creating a calculated field to get the result I need?
Setup up a subquery on JobMaterial that counts number of lines and number of lines that JobMatl.IssueComplete = True - if they are equal then all materials have been issued.
Thanks, I’m new to all of this and only just learning the basics. I have figured out how to add a subquery, added the relevant table/fields and made the calculated fields but struggling how I then link that to the main query so I can then add a calc field based on the data from these two.
On the sub query (which you might name summary) you grouped by job number
The calc fields are summed
Go back to the Top Query and click on the box above the list of tables (in classic screen)
Click on sub queries tab (in Kinetic)
Select the sub query and create a link to join with Jobnum = Jobnum
Thanks, I’m slowly getting there. I have made the calculated fields and they are all working. Now trying to work out how to make the below a single row return. Currently its duplicating for the amount of material in the job even if I remove those fields.
I took out JobMtl from the top query(that is all of the lines) Added company to the Subquery because it should always be there and joined the sub to the main inner joined like below. I would also as good habit make the top query something like Top or Main and I tried to change subquery to MtlCounts, but it was already in use, so I would have ha to remove your calculations and put them back… I also took distinct off since if you do the joins right you would not need that overhead.
I am not sure of JobAsmbls purpose since you are only currently using part from there.
Perfect, Thankyou so much! Its now deployed in the dashboard and all working as intended. Now you explained it I can see where I went wrong. Learning all of this through necessity.
OK, new issue which I still cannot figure out how to get the data included.
I need to add more calculated fields but the tables they are related to keep duplicating the results again.
I need to add the longest material date based on the due dates on direct POs and if confirmed. When I add the tables and fields I get duplicates.
I also need to add a Yes/No result based on if the materials are either picked/allocated/reserved.
The plan is to have the job line go green when all materials are issued (I have this resolved)
Amber when we have the materials in house (allocated or reserved but not yet picked)
and Red when there is a confirmed material due date which is later than the job start date
I can do the calc fields, but when I am adding the fields or subqueries I get duplications which I cant wrap my head around.
If I can have it explained why its duplicating, or if it cannot be done the reason why it would be massively appreciated. We have been using Epicor for over 10 years and this work has been done in spreadsheets for far too long which is massively time consuming. I’m trying to empower the staff to get the info themselves in the simplest way possible so I dont have to hold their hands.
All of this is doable. I do ours in SQL because it was done in the E9 days, but this timephase baq would be a starting point. I would make it a CTE, so then you can join to it multiple times to get the different answers you need.
As to your duplicates. I only have a few make direct jobs, but those with more than one JobProd were the issue. That is the join from JobAsmbl to JobProd. you will probably need to make a Customer subquery that goes from Job to order grouped by JobNum so you get one customer per JobNum.
Can anyone point me to where I can get the stock allocated to a specific job?
I have allocated materials to a job in fulfillment workbench, but the data in PartWhse_AllocatedQty is the overall number of material allocated and not specific to the job. I cannot find it in any of the job related tables…Although I have just had an eye test and told I need new glasses. Might be related
I am linking it to JobMtl using company/Jobnum/Partnum/Assyseq and have selected a part from the job and only allocated material to that one part. Now my results are just that one part which has allocated material, but I want it to show all of the parts (the rest should just say zero)
Sounds like you need to change the join type between JobMtl and PartAlloc to ‘All rows from JobMtl’, you do this from the ‘Table Relations’ tab. I’ve just had to something similar for a query I’m working on:
