1 Reply Latest reply on Jan 11, 2011 2:28 PM by philmodjunk

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

    Insight

      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

        • 1. Re: Compile report on text entries into feild over multi records - sorted by month or week
          philmodjunk

          Pattern Count can certainly count the number of times a specific section of text appears in a field, but I think you should consider a re-design of your database so that these values are all recorded in a multi-value list like this in the first place.

          You might want to consider a portal to an "errors Table" where the error is logged by selecting a different error in a drop down list/pop up menu for each portal row. Since your errors now reside in individual records, a summary report can now be easily created that groups these records by error name and counts them for you.