AnsweredAssumed Answered

Compile report on text entries into feild over multi records - sorted by month or week

Question asked by Insight on Jan 11, 2011
Latest reply on Jan 11, 2011 by philmodjunk

Title

Compile report on text entries into feild over multi records - sorted by month or week

Post

Ok I will attempt to explain this as best I can...

Hope someone can help or advise.

Large FM database with over 40,000 records per year

The FM system is built for a 'hand made product' production line- tracking product order and production.

Problem is in item #5 below

1.

Order come in - office insert product requirements description etc tick boxes

2.

Production teams make product based on custom entry detail (from record - tick boxes of instruction)

3.

Product goes along production line - and is Quality Control checked at 5 different stations

QC looks at product in production and fails or passes product. Uses filemaker to select fail choices and ticks boxes

Has five types of fail type headings

Fail Type 1 - Fail Type 2 - Fail Type 3 - Fail Type 4 - Fail Type 5

Each of these Fail Types has up to 10 choices of  sub fail explanation 

Example:

Fail Type 1.

- Border Broken

- Corner not rounded correctly

- Die not cut to correct Specifications

- etc.

NOTE*

The quality control user can select multiples of errors and issues.

These sub errors assigned to each 'Fail Type' field are available from a predefined 'value list' in FM that is editable by Administrator.

Result is that this field Fail Type 1 can have multi lines of text

- Frame Made to Large

- Corner not rounded correctly

- Die not cut to correct Specifications

- Backing soft

FINE SO FAR....

BUT (and this is not the problem... this is just explaining the system better before I propose my problem..)

The product can be failed multiple times and then passes and moved to the next station.

SO

When the QC passes the errors reported are all removed from the main record and the record is flaged and moved on.

SO 

We record the errors in a new table.

When the QC ticks the errors in each Fail Type field the errors (MULTI selection tick boxes and not radio button) are recorded in a different tabel

Example

On click fail button -> go to ayout create new record and script copies the errors for each fail type and the entries entered by the QC report table with date recorded (month and week number etc).

So we now have multi error fail reports recorded for individual product production records in the main data file.

This Works Fine.

4. We then have a report screen based on the error reports which gives us 

Based On Month Name - or Week Number (Which ever report we call)

Summary Count of Type 1 Fail Errors - Type 2 Fail Errors -Type 3 Fail Errors ETC.

Total Of Fail Errors in a month.

Etc

5. THE PROBLEM

The client NOW wants a breakdown of each of the selected (or ticked errors) in each Fail Error Type... So if the QC clicked on

- Corner not rounded correctly

I need to count the amount of times the error 'Corner not rounded correctly' was entered for the month or week.

Now I have a calculation field that can collect the text from all the FAIL TYPE fields and puts a carriage return between each report error entry for each of the error type fields

So this field can look like this (one click of Error Fail Type button):

Frame Made to Large

Die not cut to correct Specifications

Backing soft

Cover Short

Cover Soft

And the order of the text can be in different orders with no sort etc.

----------- SO MY QUESTION -------

Does anyone know a way of querying the text entered into that calculation field and counting the amount of times say 'Cover Short' was selected in that month.

Is there a search text content query that will pull a report on each entry of error ???

Big question i know but....

Would a chart work ???

If you have read this far thank you... any help would be appreciated. I know some will say shouldn't have built the system that way but this project has morphed over the past 10 years and has had to adapt to the business and its requirements. Main database has over 3000 fields and runs on a FM Pro server with 25 users.

Thanks

Bob

Outcomes