AnsweredAssumed Answered

Auto-Enter Suddenly Doesn't Evaluate Properly

Question asked by Bentech on Sep 22, 2017
Latest reply on Sep 25, 2017 by Bentech

Our organization uses an elaborate calculation to evaluate the status of jobs in our shop. This calculation is defined as an auto-enter and set to replace the value when the record is updated ("Do not replace existing value of field" box is UN-checked.) The calculation has been working just fine for a number of months, but suddenly (literally overnight) has begun to evaluate incorrectly. We've run analysis on the database and no structural changes have occurred between when the calc. ran just fine and when the problem began. It's very strange. If I force the field to re-evaluate, I get an incorrect value. I've tried creating an entirely new field using the same calculation and receive the same, incorrect results. If I set the field in a script step however (using the same calculation), or evaluate the calculation in the data viewer, the correct results are returned. Any thoughts as to what's going on here?

 

EDIT:

 

This is the entire calculation. It checks the status of a few fields, makes a few SQL calls, then based on those criteria determines the routing (what we call the individual steps in our mfg. process) status. It's been incorrectly returning "Ready" in numerous cases, even though the component pieces (the SQL calls and let variables) indicate there's something holding it up.

-----------------------------------------------

 

/* BECAUSE THIS IS AN AUTO ENTER CALC FIELD, ONLY FIELDS FROM THE ROUTING_JOBS TABLE CAN BE USED.

YOU CAN NOT USE RELATED FIELDS AND HAVE THE CALCULATION AUTO UPDATE

IT IS BEING DONE THIS WAY BECAUSE A CALCULATION FIELD IS WAY TO SLOW WHEN USING IT FOR THE SHOP DISPLAY SCRIPT

 

Logic of calculation ...

1) Identify all Assembly Level codes used for the records Job ID for it's Assembly Level primary number

  This is numbers to left of 1st decimal, or number if no decimal.

2) Cleanup the list to only be unique Assembly Level codes,

   This is just incase a user made an error and has the same Assembly Level code entered more then once.

3) Identify list count and where in the list the current record is (row)

4) Identify the status of all Assembly Level records (get list of all Assembly Levels identified if they are complete and dependent.

5) Identify if the item is on a purchase order, and if so, has it been received.

6) Identify if it is an item that is dependent on a BOM item that is on a purchase order.

7) Evaluates if there is more then one record with same Assembly Level, it then looks to the Sequence numbers with lower #'s needing to be done first

8) Identify if saw '00.10' is completed but ignore '00'

9) Identify if first material handling '00' is completed but ignore '00.10'

10) Evaluate if the record is ready for production based on possible previous steps needed or if item is on PO and received.

 

*This calculation uses custom functions cf_UniqueValues, cf_FindInList, cf_RoutingProgressList

*/

 

Let([

update = RecordRefresher ;

 

ReturnSub = "\n" ; // Swap out carriage returns in results with a different character, so specify the character here.  \n is the default.

 

/* ###############  #1  GET A LIST OF ALL ASSEMBLY LEVELS FOR THE JOB THAT ARE NOT COMPLETE   ###############

It will look something like this:

00

00.10

30

30.10

40

999

999.10

*/

SQLResult = ExecuteSQL (

"SELECT a.\"AssyLvl\"

FROM \"Routing_Jobs\" a

WHERE a.\"JobID\" = ? AND a.\"Completed\" is NULL

ORDER BY a.\"AssyLvl\" ASC" ;

"    " ; "|*|" ;

JobID  ) ;

assyLvlListRaw =Supertrim(Substitute ( SQLResult ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ] ;  [ ¶ & ¶ ; ¶ ] ;  [ ¶ & ¶ ; ¶ ]  )) ;

 

/* ###############  #2  CLEAN UP RETURNED LIST TO ONLY INCLUDE UNIQUE VALUES   ###############

It will look something like this:

00

00.10

30

30.10

40

999

999.10

*/

assyLvlList = cf_UniqueValues ( assyLvlListRaw  );

$$assyLvlList = assyLvlList;

 

//###############  #3  IDENTIFY SEVERAL PARAMETERS BASED ON LIST   ###########

saw = If ( not IsEmpty(FilterValues ( assyLvlList ; "00.10" )) ; "Not Cut" ; "" );

mtlHandling = If ( not IsEmpty(FilterValues ( assyLvlList ; "00" )) ; "No Material Handling" ; "" );

row = cf_FindInList ( assyLvlList ; AssyLvl );

$$saw = saw;

$$row = row;

listCount = ValueCount ( assyLvlList );

 

/*###############  #4  IDENTIFY STATUS OF EACH ASSEMBLY LEVEL RECORD   ###############

 

It will look something like this:

00  Not Done  Dependent

00.10  Not Done  Single

30  Not Done  Dependent

30.10  Not Done  Single

40  Not Done  Single

999  Not Done  Dependent

999.10  Not Done  Single

*/

assyLvlStatus = cf_RoutingProgressList ( JobID ; assyLvlList );

 

// $$assyLvlStatus = assyLvlstatus;

 

//###############  #5  IDENTIFY IS ITEM IS ON A PURCHASE ORDER, AND IF SO, HAS IT BEEN RECEIVED   ###############

po = Case (

IsEmpty ( PoID ) ; "" ;

not IsEmpty(PoID) and POReceived ≠ "Y" ; "Hold"

);

 

//###############  #6  IDENTIFY IF IT IS AN ITEM THAT IS DEPENDENT ON A BOM (Job Component) ITEM THAT IS ON A PURCHASE ORDER   ###############

bomPOnotReceived = ExecuteSQL (

"SELECT COUNT(a.\"POID\")

FROM \"Job Components\" a

WHERE a.\"Job ID\" = ? AND a.\"Assy Lvl\" LIKE ? and a.\"Complete\" is NULL AND a.\"POReceived\" is NULL" ;

"    " ; "|*|" ;

JobID ; AssyLvl & "%"

) ;

 

//###############  #6B  IDENTIFY IF IT IS AN ITEM THAT IS DEPENDENT ON A BOM (Job Component) ITEM THAT IS ON A PURCHASE ORDER   ###############

assyPOnotReceived = ExecuteSQL (

"SELECT COUNT(a.\"POID\")

FROM \"Job Components\" a

WHERE a.\"Job ID\" = ? and a.\"Complete\" is NULL AND a.\"POReceived\" is NULL" ;

"    " ; "|*|" ;

JobID

) ;

 

//###############  #7  IDENTIFY IF THE ASSEMBLY LEVEL IS USED MORE THEN ONCE, IS IT THE LOWEST SEQUENCE NUMBER OF THESE   ###############

subListSQL = ExecuteSQL (

"SELECT a.\"AssyLvl\", a.\"Sequence\"

FROM \"Routing_Jobs\" a

WHERE a.\"JobID\" = ? AND a.\"AssyLvl\"=? AND a.\"Completed\" is NULL

ORDER BY a.\"Sequence\" ASC" ;

"    " ; "|*|" ;

JobID ; AssyLvl  ) ;

// $$jobid = JobID;

// $$assylvl = AssyLvl ;

subList = Supertrim(Substitute ( subListSQL ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ] ;  [ ¶ & ¶ ; ¶ ] ;  [ ¶ & ¶ ; ¶ ] ; [ "    " ; "." ]  )) ;

// $$subList = subList;

subListCount = ValueCount ( subList ) ;

// $$subListCount = subListCount;

firstItem = Supertrim( MiddleValues ( subList ; 1 ; 1 ) ) ;

// $$firstItem = firstItem;

 

//###############  #8  Identify if saw '00.10' is completed but ignore '00'   ###############

 

//###############  #9  Identify if first material handling '00' is completed but ignore '00.10'   ###############

 

//###############  #10  IDENTIFY IF ROUTING RECORD IS READY FOR PRODUCTION   ###############

 

//############### DEBUG - SET GLOBAL VARIABLE TO SEE DATA USED   ###############

$$debugRoutingReady = "assyLvlStatus: " & ¶ &  assyLvlStatus

 

];

 

//###################################################################################################################################

//###############                                      LOGIC IF ROUTING IS READY                                      ###############

//###################################################################################################################################

 

Case(

 

// CASE 1: The routing can not already be completed to be displayed

Completed = "Y" ; "Error: Already Completed" ;

 

// CASE 2: Assembly Level is always to have a value.  If it does not, show an error.

IsEmpty( AssyLvl ) ; "Error: Missing Assembly Level" ;

 

// CASE 3: job isn't released to Shop

     IsEmpty(JobStatus); "Hold-Not Released";

 

// CASE 4: If initial Assembly Level '00' for material handling is not complete

Saw ≠ "Not Cut" and MtlHandling = "No Material Handling" and AssyLvl ≠ "00" ; "Hold-Material Handling" ;

 

// CASE 5: If initial Assembly Level '00.10' for saw is not complete

Saw  =  "Not Cut" and AssyLvl ≠ "00.10"; "Hold-Saw" ;

 

// CASE 6: If item is a PO line item, is it received

        not IsEmpty ( PoID ) and not IsEmpty ( PoLI ) and IsEmpty ( POReceived ); "Hold-PO";

 

// CASE 7: If item is dependent on a non routed BOM line that is not on a routing, is the the PO line item received

  bomPOnotReceived ≠ 0 ; "Hold-PO for Part" ;

 

// Case 8: If item is dependent on a non routed BOM line that is not on a routing, .....

  Left(AssyLvl;3) = "999" and assyPOnotReceived ≠ 0 ; "Hold-Unreceivd PO" ;

 

// Not sure # 9 is needed

// Case 9: Only 1 record for this num1 Assembly Level and it is not an item on a PO that must be received

// ValueCount(assyLvlList) = 1 and po = "NA" ; "Ready" ;

 

// Not sure # 10 is needed

// Case 10: Only 1 record for this num1 Assembly Level and it is being purchased and the purchased item is received

// ValueCount(assyLvlList) = 1 and po = "Received"  ; "Ready" ;

 

// Case 11: Part of Parent Assembly Level, it is dependent on another record and the child assembly level is not complete

PatternCount ( GetValue ( assyLvlStatus ; row ) ; "Dependent" ) = 1 and PatternCount ( GetValue ( assyLvlStatus ; row + 1 ) ; "Not Done" ) = 1 ; "Hold-SubAssembly" ;

 

// All prior items are not complete and it is 999.10 Assy Level (if it exists).

// Case 12:  If job is a repair and only has 999.10 for Assembly (Teardown), it is captured in prior 'Case' statement of only 1 record

 

//Not sure if needed

 

PatternCount ( GetValue ( assyLvlStatus ; row ) ; "999.10") = 1 and row = 1  ; "Hold-Assy" ;

PatternCount ( GetValue ( assyLvlStatus ; row ) ; "999.10") = 1 and listCount ≠ 2 ; "Hold-Assy" ;

 

// Believe 2 lines below are not accurate as the 'Completed' should always be zero as set (commented out) toward the top of this calc.

// PatternCount ( GetValue ( assyLvlStatus ; row ) ; "999.10") = 1 and Completed + 1 = row  ; "Hold-Assy" ;

// PatternCount ( GetValue ( assyLvlStatus ; row ) ; "999.10") = 1 and Completed + 2 ≠ listCount  ; "Hold-Assy" ;

 

// Case 13: If the Assembly Level is duplicated and the Sequence number is the lowest one

subListCount > 1 and firstItem ≠ AssyLvl & "." & Sequence ; "Hold: Previous Operation" ;

 

// Not sure # 14 is needed

// Case 14: Part of Parent Assembly Level and is the youngest child record (1st needing to be done)

// row = listcount ; "Ready" ;

 

// Not sure # 15 is needed

// Case 15: Part of Parent Assembly Level and the record itself is complete

// PatternCount ( GetValue ( assyLvlStatus ; row ) ; "Completed" ) = 1 ; "Completed" ;

 

// Not sure # 16 is needed

// Case 16: Part of Parent Assembly Level and it is not dependent on another record (Single)

// PatternCount ( GetValue ( assyLvlStatus ; row ) ; "Single" ) = 1 ; "Ready" ;

 

// Not sure # 17 is needed

// Case 17: Part of Parent Assembly Level, it is dependent on another record and the child assembly level is complete

// PatternCount ( GetValue ( assyLvlStatus ; row ) ; "Dependent" ) = 1 and PatternCount ( GetValue ( assyLvlStatus ; row + 1 ) ; "Completed" ) = 1 ; "Ready" ;

 

"Ready"

 

)

) // end of Let

Outcomes