Description

We want to provide tracking, load information, and production time information for internal requests for drawings, production computer files for printing and routing, concept artwork and similar requests of our Designer group. We want the fact and the status of these requests to be visible throughout the organization. And we want the time data – applied or logged time – to be costed back to the original order for variance analysis as to what was estimated or costed and what actual times turn out to be.

For this we developed a group of UDFs each containing critical data for the application – E.g. the Due Date of the request, the hours estimated to complete the request, the Designer selected for the work, the actual hours worked on the request, etc. Accordingly each request is part of an Order or Estimate record.

Users throughout the company use the Dashboard UDF instrument to cluster the Design Request UDFs in a group they can monitor as they need and, in the case of the Design Group, to easily enter and update UDF information. Additionally, in combination with the Dashboard explorer and its Filters and Count and Sum capability, the Designers have a real-time visual portrayal of their backlogs or “WIP”. Below is a screen shot of the dashboard of an ASG designer showing the tracking information. The Transaction UDF instrument on the left is used to enter data. The Explorer shows the cumulative results – in this case a current backlog of 57.5 hours. Sales and production associates can include Design UDFs in their standard transaction explorers to monitor the status of Design Requests such as whether Production Art or the entire request is complete..

Concept

Basic Discussion of Approach

As noted above, the system is based on UDFs made visible to Orders and Estimates. Many of the UDFs are intended to be entered or updated by either the requesting Associate (Salesperson or Project Manager) or the Designer working on the request. However, like all such systems, it requires computer code and checks to intercept and circumvent user mistakes and assure the data is usable. Additionally, one of the great values – running totals of total time applied to Estimates and Orders, running totals of the times estimated and the actual required – had to be maintained by code as it proved too much to expect Designers to increment these fields manually with consistency and accuracy. Finally, the system needs to operate at the TransHeader or total Order and Estimate level as the UDFs are managed either through the Explorer or the Action Menu Edit UDFs pages and rarely with a direct edit to the order. So it could not be product specific and could not require that an Order or Estimate be edited in order to enter or update the fields and initiate the calculations.

For data entry and management, the wide variety of objects and features in Cyrious' UDF layout pages allowed us to create screens similar to those of Access or other database programs. We created two layout pages – one for the Requester (Sales or Project Manager) and one for the Designers. These are selected with a right-click on a transaction in the dashboard Explorer. The screen shot below shows how this appears in action.

:

Those not familiar with the UDF layout pages will find a lot of objects which permit some pretty elegant forms such as boxes, lines, foreground and background color options for text and objects, etc. As with pricing forms, the default for number objects is “Integers Only” which must be set to False and the object controlling decimal places set to at least 2 to properly show fractions. Combined with options in the original creation of the UDF such as the “Limit to List” and “Allow Multiple Selections” of the text UDF, the developer has a lot of control over the look and feel, enhancing the likelihood of acceptance and success with the application. For example we wanted to limit the “Purpose” selections to specific names so created a text UDF with a dropdown list which is not editable; but since we also wanted to be able to show more than one “Purpose”, we selected both options in the UDF setup Dropdown values selection., as shown in the screen shot below.

The screenshot below shows the final result for the Requester's input. (Some may notice that we use the URL object of the UDF layout form to embed brief video presentations explaining the forms and systems, for which we use a service like Jing to maintain these in the cloud. When selected, this brings a 5 minute video tutorial up on the User's browser)

Because the Designers also like to have printed copies of their requests which they use in their own scheduling process, we implemented reports through the Control reporting structure with a Crystal template on the Action menu; so it is simple to select the “Design Request” report and email it to the Designer.

This approach also permitted us to incorporate any information needed to make the request more useful from the TransHeader and TransDetail records – for example, Due Date of the Order itself, the amount actually priced for Design work in the Order and the language describing to the customer the Design Services being invoiced. (Parenthetically, on the Designer's dashboard, through a Report instrument, we are able to show the full line item for Design services with the hours sold and any explanatory text which went to the customer.)

The Screen Shot below shows an example of this report.

As stated above, we added a Data Entry form for the Designers as an alternative to the dashboard instrument. Since Control handles a Save in the UDF dashboard instrument as an Edit and a change in the UDF layout page as an Edit, both approaches work well with the macro triggers. Below is a screenshot of the Designer's data entry form:

Control's Macro capability is what makes this all possible. Whenever a transaction is Edited, the On New Order or On Order Edited macro triggers cause the SQL code to update the UDFs and make the calculations and recalculations as required to maintain the system, depending on whether the Request is “Open” or “Closed”. (We also use the same triggers for Estimates which also have Design Requests.)

The SQL code resides in the Indirect Change Event formula builder. Initially, we just used the SQL Update and Set commands to change the UDF values but quickly found two problems with that. The new values did not appear until Control had been closed and restarted. Then we found that when we “Edited” the transaction, many of the values were reset to 0. There are two solutions or additional steps which were required which are explained below. (1) We needed to update a field in the TransHeader table which Control uses to know when not to recalculate UDFs on an Edit – “OverriddenUserFields” . (2.) We needed to tell the Chapi to update all the Transactions and UDFs of the Users to the new values – the refreshChapi call. The latter was just made possible with the recently implemented sql_bridge. With those additions to the code, the system works perfectly. When a Salesperson adds a Design Request on his computer with an estimate of 3 hours, the Designer in another location immediately sees his backlogs increase by 3 hours. Or when the designer “logs” time to a Request, he sees his backlog recalculated instantly on his dashboard explorer.

Still to be implemented is the exclamation point of the system. Through the macros, each transaction with Design Requests has a UDF which is summing all the hours worked. Each Order contains a part for DesignHours set up to receive hours. We hope to write the Totals Hours Logged of all DRs and all Designers into the actual DesignHours part for an actual cost of design in the Order.

Warning or areas where the approach might not work as expected.

As noted above, this approach requires that the host system has purchased and enabled Sequel Bridge.

We also recommend that, when working with UDFs, the developer be very conscious of and specify in the setup when they are to be reset – E.g when a transaction is cloned, when an estimate is converted to an order, etc. These options are in the Setup screen of the UDF and, unlike the name and type, can be edited after the UDF is created.

The limitation of this system is that there can only be one request at a time. For transactions with multiple requests we must create what we call a zero dollar tracking order. Fortunately, this need is relatively rate.

This approach calls for updates to tables in both the TransHeader and TransHeaderUserFields tables. The greatest dangers are in the development and testing of the code. Developers need to be very careful to limit the changes to these tables to Transactions which are not important to the business. Ideally, any such coding and testing would be made after a full backup of the data and before any users have modified the tables. However, problems and missteps can be avoided by a careful use of the SQL “where statement” and hard-coding ID numbers –

for example:

Where TransheaderUserField.ID = 137289

Also, remember that every Update statement requires a Where clause to limit it to specific transactions. SQL will allow you to place multiple Update statements in the same code set without a Where clause. However, those that do not will update all the records in the Update table.

--DONT DO THIS
--NOTE THAT THE FIRST TWO STATEMENTS DO NOT HAVE WHERE CLAUSES
--IF YOU EXECUTE THIS, THE FIELD IN THE FIRST TWO STATEMENTS
--WILL BE UPDATED FOR EVERY TRANSACTION IN YOUR DATABASE
Update TransHeaderUserField
Set
OverriddenUserFields = OverriddenUserFields+  'DR Is Open'
Update TransHeaderUserField
Set
OverriddenUserFields = OverriddenUserFields+ 'Is Firm Date'
--THIS IS CORRECT
Update TransHeaderUserField
Set
OverriddenUserFields = OverriddenUserFields + 'DR Due Date'
Where ID = 137265

One table which must be updated to assure that Control does not reset the UDFs to original values on an Edit is the OverridenUserFields of the TransHeader table. The update contains a text list of all UDFs which are overridden. So our code is an addition – a concatenation to existing UDFs in the table – of the UDFs used by the tracking system. The UDF names entered in this table must be exactly as they are set up in Control as seen in the listing in the User Defined Setup of Control. In other words, if the UDF has been named with spaces such as “DR Designer1 Hours Logged”, the Set command must be for that string exactly as named - SQL adds underscores wherever there are spaces or characters like a period; so you cannot use the name as it comes out of the StoreData.dbo database. The code below is a partial list of the UDFs being added which illustrates this point.

Also, we have noted that, as coded, it would be possible to have a very large field if the transaction were to receive a lot of edits. In that case we would employ a Case comparison not to duplicate UDFs already in the list; but this was not done in this case to simplify the code and create the fastest possible response.

---Partial list of codes being updated
---Note that the do not have enderscores separating the words
Update TransHeader
Set SeqID = SeqID + 1,
OverriddenUserFields = (
coalesce(OverriddenUserFields, '')
+ Char(13) + Char(10) + 'Design Hours'
+ Char(13) + Char(10) + 'DR Designer2 Hours'
+ Char(13) + Char(10) + 'DR Designer3 Hours'
The following is an explanation of the updates required from Scott StCyr:
In this case, a separate field is Control tells it if the UDF value is the
computed value (which will be recomputed and replaced) or an overridden value
 (which will keep it's value).
That information is stored in the TransHeader.OverriddenUserFields column.
When you override a UDF, you'll need to add the UDF name to this list.
The basic logic will be
When you Update a UDF field X for an order with TransHeader.ID = 
Update TransHeaderUserField
Set SeqID = SeqID + 1, X = 'New Value'
Where ID = 
;
-- and refresh the UDFs in Control
exec dbo.csf_chapi_refresh(, 10002, -1)
;
You will also need to add the field name to the list in the order that tracks
what UDF fields are overridden.
This way, Control will know to not update the value on recalculation.
Update TransHeader
Set SeqID = SeqID + 1, OverriddenUserFields = OverriddenUserFields + Char(13) + Char(10) + 'X'
Where ID = 
;
-- and now refresh the order
exec dbo.csf_chapi_refresh(, 10000, -1)
;
The Char(13) is a new line and Char(10) is a carriage return (the old typewriter terminology).
You'll need these between the existing and new values.
You don't have to worry about checking or adding a value twice.
It will still work fine.

Regarding speed, we are amazed that the implementation of this code does not noticeably effect transaction speeds. We have tested orders and estimates with and without the macro active and have seen no difference. Theoretically, however, this might be a factor with more complex heuristics in the code.

  1. Create the SQL code using an SQL editing program like MySequel or other. Hard code a Transaction number and then when you Execute the code you can immediately view the results in Control without having to execute the macro in control. There are several mandatory elements of your update code:

The code must start with and contain code to update the SequenceID of the table. So, when updating the TransHeaderUserField table, you must begin with the code below.

Update TransHeader
Set SeqID = SeqID + 1,

The code must contain a Where clause at the end to identify the condition under which it is implemented.

-- Implement this update when the OrderID is 137295
-- and the Design Request in open or not complete
-- (the Request Number does not equal the last completed number)
Where TransheaderUserField.ID = 137295 AND DR_Number  DR_No_Completed 
  1. When building the SQL code after every update set of any table, even when combining these in one query, we call the sql_bridge of the Sequel Bridge code set: As noted in the WIKI article about this code, it requires 3 elements: the ID of the table being updated and the ClassTypeID of that table and a -1. For Order and Estimate transactions the database_table_by_object_type_and_classtypeidis 10000. For the TransHeaderUserField table, the ClassTYpeID is 10002. Below is the actual code employed:
-- when developing code in MySequel or outside of 
--Control where we are hard-coding a transaction number for test
 --purposes but intend to use the  as the ID number,
--we comment that beside every line which requires the change.
-- Then, we paste the code into Control and can easily see where 
--we need to insert the  
select dbo.csf_chapi_refresh((Select ID From TransHeaderUserField with(nolock) 
   Where ID = 137295),10002,-1) --ID=
select dbo.csf_chapi_refresh((Select ID From TransHeader with(nolock) 
     Where ID = 137295),10000,-1)  --ID =

We should also mention for those with limited SQL experience that it is important to anticipate NULL fields and convert them to the type your formulas need to function for reasons beyond this “How To”. You will note in the code supplied here that we do that with the coalesce command which we may have applied over generously; but it doesn't affect performance to coalesce a field and we think better safe than sorry.

The final step is to paste the code into Control. This is done in a macro with the On New and On Edited Trigger for Orders and Estimates. Note that it is also possible to manage UDFs for Accounts, Contacts, Products, etc. with this approach). Once pasted, we switched out the ID specific code to the alias for the current transaction and saved. Screen shot below shows the Macro setup.

The code set is actually remarkably simple for how much it does. We are providing real time updates of Designer loads by subtracting the hours worked from the estimated hours to calculate the number of hours they have remaining. When they have finished a specific request, we zero out all the fields pertaining to a particular live or open request; so the next one (there may be multiple requests for a specific order) has blank fields for the person creating a new request. And we increment the running totals of the fields which are maintaining the total hours estimated and total hours worked for each order. The biggest challenge of the code development was working out the sequence of updates so we were not zeroing out fields we needed to sub-total before we had incremented the running totals.

  1. As with anything of this nature, the final step is to test, test, test, test, test.

I know that no one will construct an identical system or have UDFs with similar names but for those geeks who would like to view the entire code of this application it is included below:

----------------------CLOSED DR Calculations---------------------------------
Select
TransHeader.ID,
TransHeader.ClassTypeID
From TransHeader with(nolock)
LEFT OUTER JOIN TransHeaderUserField with(nolock) on Transheader.ID = TransHeaderUserField.ID
Where Transheader.ID =  --
--calculate all the running totals and reset the "new" open DR fields so next DR is a blank slate
Update TransHeaderUserField
Set
TransHeaderUserField.SeqID = TransHeaderUserField.SeqID +1,
Design_Hours = coalesce(Design_Hours,0) + coalesce(DR_Designer1_ActiveLogged,0),
DR_Designer2_Hours =
    coalesce(DR_Designer2_Hours,0) + coalesce(DR_Designer2_ActiveLogged,0),
DR_Designer3_Hours =
    coalesce(DR_Designer3_Hours,0) + coalesce(DR_Designer3_ActiveLogged,0),
DR_TotalEstimatedAllDrs =
    coalesce(DR_TotalEstimatedAllDrs,0) + coalesce(DR_Est__Hours,0),
DR_SalesEst_AllDrs =
    coalesce(DR_SalesEst_AllDrs,0) + coalesce(DR_SalesEstHrs,0)
Where TransheaderUserField.ID = 
      AND DR_Number = DR_No_Completed  --ID=
--Refresh the TransHeader and UDF tables with CHAPI so changes
--appear in dashboards and Estinates and Orders without reloading Control
select dbo.csf_chapi_refresh((Select ID
From TransHeaderUserField with(nolock) Where ID = ),10002,-1) --ID=
select dbo.csf_chapi_refresh((Select ID
From TransHeader with(nolock) Where ID = ),10000,-1)  --ID =
Update TransHeaderUserField
Set
TransHeaderUserField.SeqID = TransHeaderUserField.SeqID +1,
DR_Total_Hours =
   coalesce(Design_Hours,0) + coalesce(DR_Designer2_Hours,0) + coalesce(DR_Designer3_Hours,0),
DR_Designer1_Est_Hrs = 0,
DR_Designer2_Est_Hrs = 0,
DR_Designer3_Est_Hrs = 0,
DR_SalesEstHrs = 0,
DR_Est__Hours = 0,
DR_Designer1_ActiveLogged = 0,
DR_Designer2_ActiveLogged = 0,
DR_Designer3_ActiveLogged = 0,
DR_Designer1_ActiveHrsRemain = 0,
DR_Designer2_ActiveHrsRemain = 0,
DR_Designer3_ActiveHrsRemain = 0,
DR_Project=NULL,
DR_Purpose=NULL,
DR_Date_Needed=NULL,
DR_Firm_Date = 0,
DR_Is_Open = 0
Where TransheaderUserField.ID =  AND DR_Number = DR_No_Completed  --ID=
--Refresh the TransHeader and UDF tables with CHAPI so changes appear
--in dashboards and Estinates and Orders without reloading Control
select dbo.csf_chapi_refresh((Select ID
       From TransHeaderUserField with(nolock) Where ID = ),10002,-1) --ID=
select dbo.csf_chapi_refresh((Select ID
       From TransHeader with(nolock) Where ID = ),10000,-1)  --ID =
-------------------------- OPEN DR ------------------------------------------------------------------
--- Maintain all the calculated fields as Designers update
--- with revised estimated times or logged hours
----------------------------------------------------------------------------------------------------------
Update TransHeaderUserField
Set
TransHeaderUserField.SeqID = TransHeaderUserField.SeqID +1,
--Test if Des1 Est Hrs  has been entered by Designer.
--if not use sales estimate otherwise current value
DR_Designer1_Est_Hrs =
    CASE WHEN coalesce(DR_Designer1_Est_Hrs,0) = 0  THEN
         CASE WHEN DR_SalesEstHrs  0 THEN DR_SalesEstHrs ELSE 1 END
    ELSE DR_Designer1_Est_Hrs END,
-- If sales has not entered a due date default to 3 days
DR_Date_Needed =
   CASE WHEN DR_Date_Needed IS NULL
   THEN Cast(GetDATE()+3 AS DATE) ELSE DR_Date_Needed END,
DR_Is_Open = 1
Where TransheaderUserField.ID =
    AND DR_Number  DR_No_Completed  --ID=
select dbo.csf_chapi_refresh((Select ID
       From TransHeaderUserField with(nolock)
       Where ID = ),10002,-1) --ID=
select dbo.csf_chapi_refresh((Select ID
       From TransHeader with(nolock)
       Where ID = ),10000,-1)  --ID =
-- Calculate all the remaining hours for the WIP totals
Update TransHeaderUserField
Set
TransHeaderUserField.SeqID = TransHeaderUserField.SeqID +1,
DR_Designer1_ActiveHrsRemain =
   coalesce(DR_Designer1_Est_Hrs,0)-coalesce(DR_Designer1_ActiveLogged,0),
DR_Designer2_ActiveHrsRemain =
    coalesce(DR_Designer2_Est_Hrs,0)-coalesce(DR_Designer2_ActiveLogged,0),
DR_Designer3_ActiveHrsRemain =
    coalesce(DR_Designer3_Est_Hrs,0)-coalesce(DR_Designer3_ActiveLogged,0),
DR_Est__Hours =
   coalesce(DR_Designer1_Est_Hrs,0)
   + coalesce(DR_Designer2_Est_Hrs,0)
   + coalesce(DR_Designer3_Est_Hrs,0)
Where TransheaderUserField.ID =
      AND DR_Number  DR_No_Completed  --ID=
select dbo.csf_chapi_refresh((Select ID
       From TransHeaderUserField with(nolock)
       Where ID = ),10002,-1) --ID=
select dbo.csf_chapi_refresh((Select ID
       From TransHeader with(nolock)
       Where ID = ),10000,-1)  --ID =
--Put the DR UDFs in the Overridden UDF field
--of the TransHeader record so not to be overridden on a recalc
Update TransHeader
Set SeqID = SeqID + 1,
OverriddenUserFields = (
coalesce(OverriddenUserFields, '')
+ Char(13) + Char(10) + 'Design Hours'
+ Char(13) + Char(10) + 'DR Designer2 Hours'
+ Char(13) + Char(10) + 'DR Designer3 Hours'
+ Char(13) + Char(10) + 'DR Total Hours'
+ Char(13) + Char(10) + 'DR_TotalEstimatedAllDrss'
+ Char(13) + Char(10) + 'DR Designer1 Est_Hrs'
+ Char(13) + Char(10) + 'DR Designer2 Est_Hrs'
+ Char(13) + Char(10) + 'DR Designer3 Est_Hrs'
+ Char(13) + Char(10) + 'DR_SalesEstHrs'
+ Char(13) + Char(10) + 'DR SalesEst AllDrs'
+ Char(13) + Char(10) + 'DR Est. Hours'
+ Char(13) + Char(10) + 'DR_Designer1_ActiveLogged'
+ Char(13) + Char(10) + 'DR_Designer2_ActiveLogged'
+ Char(13) + Char(10) + 'DR_Designer1_ActiveHrsRemain'
+ Char(13) + Char(10) + 'DR_Designer2_ActiveHrsRemain'
+ Char(13) + Char(10) + 'DR_Designer3_ActiveHrsRemain'
+ Char(13) + Char(10) + 'DR Project'
+ Char(13) + Char(10) + 'DR Purpose'
+ Char(13) + Char(10) + 'DR Date_Needed'
+ Char(13) + Char(10) + 'DR Firm_Date'
+ Char(13) + Char(10) + 'DR Is_Open'
)
Where ID =  --ID=
;
--Refresh the TransHeader record
select dbo.csf_chapi_refresh(,10000,-1) --ID =

Steve Gillsipe: Acorn Sign Graphics with considerable assistance from Scott StCyr

Date: May 7, 2016

Version: Control 05.70.1602:1701

You could leave a comment if you were logged in.