The end goal here: I am trying to get inspection data entered through MES into information that the Operator can use at point of entry and Quality can analyze for trends.
I can join the InspResultsNum table to the JobOpr Table [via Company, Key1 (Job), Key2 (Assy), and Key3 (oprSeq)]. But I have to pull all 200 Fields from InspResultsNum to identify the eight fields that are actually entered for this job/Part (of course there will be 8 different fields for the next Job/Part).
In addition when the data is pulled; the fields are Number001, Number002, etc. This is not a useful descriptor for knowing what data you are looking at for analysis.
Joining the InspAttr table (InspAttr has the Field Name assigned to the Inspection Attribute) to the InspResultsNum table, so you could at least see the Number001 Field Name associated with an Attribute Description in each row of inspection data, doesn’t add value because you get one-to-many multipliers because there are 50+ attributes in the table.
I am concluding that I will need a unique BAQ for each Inspection Plan, SpecID, Attribute, etc. To display/analyze them effectively. I hope I am missing something really easy here.
As I understand the Enhanced Quality module (which is where Inspection Plans live), it’s the “Inspection Plan” wrapper that customizes which fields in which table belong to which Inspection Plan… which are only available through Inspection Processing.
You’d need to write some process to call an Inspection Plan from each operation at the MES “End Activity” point… and you’d still have to write an Inspection Plan for each job/operation combination.
We just rolled out our first line on what we’ve termed an “Electronic Traveler”. This is a series of Inspection Plans set to operations for a top level device that capture the measured data and make a pass/fail determination. The BAQ I put together was/is pretty unwieldy, but it we have it feeding into a BAQ Report so it doesn’t need to be visually appealing yet. It’s similar to what you have in place, InspResults linked to all the lower tables and joined to LaborDtl.
However, I’m not a SQL guru, but I imagine there would be a way to do a compare against the SpecAttr and the InspResultsXXX tables to pull in only the fields that match. You would then have a parameter for the Spec you want to review and that should help narrow down the list considerably. I know we’ll be tackling this soon, so I’ll be sure to report back once we make some headway here. We’re still in transactional triaging, so it’s tough to keep my head above water at times.
One thing I will say is that there wasn’t much pre-made content to base our work on. A lot of what we had to build was done by trial and error. I normally like to find an out of box report to deconstruct and find out how things work, but there wasn’t anything like that in this space. I even chatted a bit with the Product Manager about this which did help a bit, but confirmed that I was more or less on my own.
I think the Inspection Results Tracker Dashboard (name may not be exactly correct) is a pretty good indicator of where this is headed. There are lots of parameters to enter and the results pare the columns down to the Specific Plan and Specification, but the header columns are still Number001, 002,etc. Leaving the user to open the User to backtrack to the Attributes/Specifications to determine what the data means.
Brian - I’m resurrecting this to see if you ever figured out the ‘dynamic’ query to get inspection results across the multiple value tables? We’re trying to implement, but would like to do our own dashboard for the result set.
The problem you initially indicated is our problem at the moment - I can’t see a way to collect the attribute field names and turn them into column names in the select clause of a query.
We have not figured this out. But we have not had to…yet, because we are keeping it simple by using one field (ie, Number001) for only one measurement (CNC Operation and Hole Diameter).
I currently have to write a BAQ for each Operation/Configurator and list all of the Attributes/Columns that collect data in the Configurator. Some Part/Operations don’t use all of the Attributes/Columns, so the data for those fields are empty. Its the same problem, but on a smaller scale and allows me to Name the Column (since we are only using it for one measurement).
Out next issue is attaching the above to the Spec table so we can pull the Min/Max and understand which of the Attributes was out-of-spec, create run charts, etc. (the current data only tells you if the entire data record was a PASS/FAIL, but not which Attributes.)
@bkozic If you can write a linq query to get the data you need you can do it in a post processing bpm on GetList. Getting the spec should be doable. Add the initial fields you need to lookup the data to the baq. They don’t have to be visible in the dashboard to be useable.
I have routines that do compete time phases or tariff calculations in GetList. I was shown by a consultant how to use this technique in E9 and use it a lot.
I don’t think without a customization you can change the column header, but you could make a calculated field for attribute1,2,3 and then corresponding value1,2,3 columns and fill them in the getlist.
You can also calculate a complex condition into a hidden field and use it for row rules to highlight cells or rows.
@gpayne I think we would both really like to see an example if you could share one. This is very interesting and I wonder how hard it would be if we have a number of different specs/plans that would need to appear on the same dashboard. It might be a lot of LINQ in one BPM.
Right now, we’ve talked the end users into using the Inspection Data tracker to pull back what they need, when they need it, rather than looking at a large chunk of semi-related able inspection data. But if you’re thing works, then maybe we can do something like that.
@MikeGross Here is one of the simple ones. This was originally done in ABL. First section calculates to highlight a job over 80 hours and under 85 % productivity that has earned more than 15% of wip.
Second section calculates the orders that a build to stock job will fulfill and assigns a sales value to the job for forecasting and updates the job with its new price per piece produced.