Archives

Cycle Counting Revisted

In a previous article, I mentioned the inadequacy of Made2Manage cycle counting in that you cannot track your cycle counts over time and make that data available to your users. Well, true to my word, I have created the following customization so that you can do just that. Now would be a good time to post my standard disclaimer:

Important: All information on this site is provided “as is” for informational purposes only.
In particular, all code samples are provided “as is” without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

If you should decide to use any of the code from this site, make sure you test it thoroughly on a test company (or better yet a test server) before you ever use it on your live data.

The first step in this customization is to create a database to hold your cycle count data. I would recommend creating a new database with a name such as Customizations or CompanyName_Customizations. Substitute the name of your database into the script below to create the table which will contain your cycle count data. The table created is nearly identical to the INPHYINV table which temporarily holds cycle count tags in M2M with an additional field for cycle count date.



USE Customizations;
GO
SET  ANSI_NULLS ON;
GO
SET  QUOTED_IDENTIFIER ON;
GO

CREATE TABLE [dbo].[CycleCounts] (
[fcappby] char(3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fcarea] char(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fcbin] char(14) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fccountby] char(3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fcdescr] varchar(35) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fcentby] char(3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fcissuedto] char(3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fcjobno] char(10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fcloc] char(14) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fclot] char(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fcpartno] char(25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fcrev] char(3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fcprodcl] char(2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fcsite] char(10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fcstatus] char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fcstkuom] char(3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fcuom] char(3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fddate] datetime NOT NULL,
[fdexpdate] datetime NOT NULL,
[fliserror] bit NOT NULL,
[fliswip] bit NOT NULL,
[fnconvrate] numeric(11, 5) NOT NULL,
[fncount] numeric(17, 5) NOT NULL,
[fnoperno] numeric(4, 0) NOT NULL,
[fnonhand] numeric(17, 5) NOT NULL,
[fnpcnt] numeric(7, 2) NOT NULL,
[fnqtyapp] numeric(17, 5) NOT NULL,
[fntag] int NOT NULL,
[fnunitcost] numeric(17, 5) NOT NULL,
[flisprepop] bit NOT NULL,
[flisprinted] bit NOT NULL,
[fdfinish] datetime NOT NULL,
[timestamp_column] timestamp NULL,
[identity_column] int IDENTITY(1, 1) NOT NULL,
[fmdescr] text COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (' '),
[fac] char(20) NOT NULL,
[fdTaken] datetime NOT NULL,
[fcudrev] char(3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (' '))
ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY];
GO

/****** Object: Index [dbo].[area]    ******/
CREATE NONCLUSTERED INDEX [area]
ON [dbo].[CycleCounts]
([fcarea])
WITH
FILLFACTOR = 90
ON [PRIMARY];
GO
/****** Object: Index [dbo].[identity_column_idx1]    ******/
CREATE UNIQUE CLUSTERED INDEX [identity_column_idx1]
ON [dbo].[CycleCounts]
([identity_column])
WITH
FILLFACTOR = 90
ON [PRIMARY];
GO
/****** Object: Index [dbo].[issuedto]    ******/
CREATE NONCLUSTERED INDEX [issuedto]
ON [dbo].[CycleCounts]
([fcissuedto])
WITH
FILLFACTOR = 90
ON [PRIMARY];
GO
/****** Object: Index [dbo].[JOBORDER]    ******/
CREATE NONCLUSTERED INDEX [JOBORDER]
ON [dbo].[CycleCounts]
([fcjobno] , [fnoperno])
WITH
FILLFACTOR = 90
ON [PRIMARY];
GO
/****** Object: Index [dbo].[LOCATION]    ******/
CREATE NONCLUSTERED INDEX [LOCATION]
ON [dbo].[CycleCounts]
([fcloc] , [fcbin] , [fclot])
WITH
FILLFACTOR = 100
ON [PRIMARY];
GO
/****** Object: Index [dbo].[parttag]    ******/
CREATE NONCLUSTERED INDEX [parttag]
ON [dbo].[CycleCounts]
([fcpartno] , [fcrev] , [fntag] , [fac])
WITH
FILLFACTOR = 90
ON [PRIMARY];
GO
/****** Object: Index [dbo].[status]    ******/
CREATE NONCLUSTERED INDEX [status]
ON [dbo].[CycleCounts]
([fcstatus])
WITH
FILLFACTOR = 90
ON [PRIMARY];
GO
/****** Object: Index [dbo].[tagno]    ******/
CREATE UNIQUE NONCLUSTERED INDEX [tagno]
ON [dbo].[CycleCounts]
([fntag] , [fac])
WITH
FILLFACTOR = 90
ON [PRIMARY];
GO

Next since we will be accessing the database via VBA, you will need to follow my instructions for connecting to the M2M Database. Per the instructions, set up a default module in your m2vevents.prj with the appropriate database connection code. Use the following customized VBA code to populate your new database table with cycle count information. Every computer which needs to run this code (or really any VBA code) must have VBA for M2M installed. Instructions for doing so can be found here here.

Open M2M and make sure that you have a practice m2vevents.prj file loaded. You can do so by typing ALT-F8. Unload the standard, if there is one, and select your practice m2vevents.prj. Then close that window and navigate to Physical Inventory Tag control window via the Transfer Window by typing ALT-T and PHYTAG. Right click on the mnemonic and choose Activate. This will create the module to hold the customizations for the PHYTAG screen. Copy and Paste the following into it:


Public connstr As String



Public Function INIT() As Boolean
    If Trim(connstr) = "" Then
        connstr = GetCs1()
    End If
    
    INIT = True
End Function


Public Function cmdArchive_CLICK() As Boolean
Dim CN As ADODB.Connection
Dim SelectStr As String

    Set CN = New ADODB.Connection
    CN.Open connstr
    
    
        SelectStr = "INSERT INTO DATABASENAME.dbo.CycleCounts" + vbCrLf + _
        "      (fcappby, fcarea, fcbin, fccountby, fcdescr, fcentby, fcissuedto, fcjobno, fcloc, fclot, fcpartno, " + vbCrLf + _
        "      fcrev, fcprodcl, fcsite, fcstatus, fcstkuom, fcuom, fddate, fdexpdate, fliserror, fliswip, fnconvrate, " + vbCrLf + _
        "      fncount, fnoperno, fnonhand, fnpcnt, fnqtyapp, fntag, fnunitcost, flisprepop, flisprinted, fdfinish, fmdescr, fac, fdTaken, fcudrev)  " + vbCrLf + _
        "      SELECT fcappby, fcarea, fcbin, fccountby,  fcdescr,  fcentby,  fcissuedto, fcjobno,  fcloc,  fclot,  fcpartno,  fcrev, fcprodcl, fcsite," + vbCrLf + _
        " fcstatus, fcstkuom,  fcuom, fddate, fdexpdate, fliserror, fliswip, fnconvrate, fncount, fnoperno, fnonhand," + vbCrLf + _
        " fnpcnt,  fnqtyapp, fntag, fnunitcost, flisprepop, flisprinted, fdfinish, fmdescr, fac, DATEADD (dd, 0, DATEDIFF (dd, 0, getdate ())), fcudrev" + vbCrLf + _
        "     FROM INPHYINV"

            
            
   CN.Execute SelectStr

    CN.Close
    Set CN = Nothing

    cmdArchive_CLICK = True
End Function

Now, instead of M2M creating a relatively useless .MBK table and emptying the INPHYSIS table, the customized code will export the cycle count tag information into your custom table and that information can be kept as long as you wish.

In a future article, I’ll provide SQL scripts to analyze the data. Also, I’ve asked a friend to create a sample M2M report with to work with the data and I’ll provide that as well.

Related posts:

2 comments to Cycle Counting Revisted

Leave a Reply to Andrew

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>