New Day End Scheduler for Accpac Inventory Control

Hello Accpac guys, Today I am posting one more tool to schedule Day End. The new feature in it is, that you do not need to keep your Accpac Session open as in earlier versions. This can be separately being applied on the server to do the day end on the scheduled time.

You just need to copy the Schedule Day End.exe(Download from Day End Scheduler) at the server at any location and schedule it using window scheduler. For the first time it will ask you some login information and do you’re processing. The information screen is as follows:

 
It is nothing but companies Accpac Company ID, System ID and admin password.
Once you run and save the settings this will automatically run the day end as per the window scheduler.

This application would be more useful for the companies having large database and where Inventory transactions are more. So, if Day End Process takes more time we can schedule it for the evening or in the night so that it will automatically run and system will be ready to use next day without waiting for the Day End in the office timings.
Hope to give new post soon.
Thanks and Regards,

Aggyey

ALL ACCPAC ITEMS OPTIONAL FIELDS IN ONE GO

Hello,


Today I was just working on some report and for the requirement I design a query that give all optional fields attached in item master in one go. We simply need to run the query and we can get the result in one table where we can get the result like Item number, item description, Optional fields as columns and there respective values.
Run the following query in any Accpac Database that contain Inventory data.


DECLARE @i INT

DECLARE @STR VARCHAR(2000)

Declare @MAINSTR VARCHAR(8000)

DECLARE @COLNAM VARCHAR(100)

DECLARE @OPTNAM VARCHAR(100)

DECLARE @TEMPTABLE VARCHAR(8000)

BEGIN

SET @i = 0

SET @MAINSTR = 'INSERT INTO ICIO select Itemno,[Desc]'

SET @TEMPTABLE ='CREATE TABLE ICIO(Itemno VARCHAR(22),[Desc] VARCHAR(100)'

PRINT @MAINSTR

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ICIO]') AND type in (N'U'))

DROP TABLE [dbo].[ICIO]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[COLNAM]') AND type in (N'U'))

DROP TABLE [dbo].[COLNAM]

CREATE TABLE [dbo].[COLNAM] (COLNAM VARCHAR(200))

WHILE (@i < (select COUNT(distinct(OPTFIELD)) from icitemo))

BEGIN

DELETE FROM COLNAM

SET @STR = 'INSERT INTO COLNAM SELECT TOP 1 OPTFIELD FROM ICITEMO

WHERE

OPTFIELD = (SELECT DISTINCT TOP 1 OPTFIELD FROM ICITEMO WHERE OPTFIELD NOT IN

(SELECT DISTINCT TOP ' + convert(varchar(2), @i) + ' OPTFIELD FROM ICITEMO ORDER BY OPTFIELD)ORDER BY OPTFIELD)

ORDER BY OPTFIELD'

EXECUTE (@STR)

SELECT @OPTNAM = COLNAM FROM COLNAM

SET @MAINSTR = @MAINSTR+', (select [value] from icitemo op1 where op1.itemno=ICITEM.ITEMNO and

OPTFIELD=''' + @OPTNAM +''') as ' + @OPTNAM

SET @TEMPTABLE = @TEMPTABLE + ', '+ @OPTNAM + ' VARCHAR(100)'

SET @i = @i + 1

END

SET @MAINSTR = @MAINSTR + ' from ICITEM'

SET @TEMPTABLE = @TEMPTABLE + ')'

EXECUTE (@TEMPTABLE)

EXECUTE (@MAINSTR)

SELECT * FROM ICIO

END
It will give you the result. Now if lets say we want to use it in a report i.e crystal report. We can not use it directly. So simply create a store procedure of this query and use it in your report.
create proc ICIOPTVAL AS

DECLARE @i INT

DECLARE @STR VARCHAR(2000)

Declare @MAINSTR VARCHAR(8000)

DECLARE @COLNAM VARCHAR(100)

DECLARE @OPTNAM VARCHAR(100)

DECLARE @TEMPTABLE VARCHAR(8000)

BEGIN

SET @i = 0

SET @MAINSTR = 'INSERT INTO ICIO select Itemno,[Desc]'

SET @TEMPTABLE ='CREATE TABLE ICIO(Itemno VARCHAR(22),[Desc] VARCHAR(100)'

PRINT @MAINSTR

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ICIO]') AND type in (N'U'))

DROP TABLE [dbo].[ICIO]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[COLNAM]') AND type in (N'U'))

DROP TABLE [dbo].[COLNAM]

CREATE TABLE [dbo].[COLNAM] (COLNAM VARCHAR(200))

WHILE (@i < (select COUNT(distinct(OPTFIELD)) from icitemo))

BEGIN

DELETE FROM COLNAM

SET @STR = 'INSERT INTO COLNAM SELECT TOP 1 OPTFIELD FROM ICITEMO

WHERE

OPTFIELD = (SELECT DISTINCT TOP 1 OPTFIELD FROM ICITEMO WHERE OPTFIELD NOT IN

(SELECT DISTINCT TOP ' + convert(varchar(2), @i) + ' OPTFIELD FROM ICITEMO ORDER BY OPTFIELD)ORDER BY OPTFIELD)

ORDER BY OPTFIELD'

EXECUTE (@STR)

SELECT @OPTNAM = COLNAM FROM COLNAM

SET @MAINSTR = @MAINSTR+', (select [value] from icitemo op1 where op1.itemno=ICITEM.ITEMNO and

OPTFIELD=''' + @OPTNAM +''') as ' + @OPTNAM

SET @TEMPTABLE = @TEMPTABLE + ', '+ @OPTNAM + ' VARCHAR(100)'

SET @i = @i + 1

END

SET @MAINSTR = @MAINSTR + ' from ICITEM'

SET @TEMPTABLE = @TEMPTABLE + ')'

EXECUTE (@TEMPTABLE)

EXECUTE (@MAINSTR)

SELECT * FROM ICIO

END
Hope this will simplify the work of adding lot of optional field in a report.
So, will be back soon with some new thing to share till then bye.
Aggyey

Filtering Optional Field Values on Crystal Report from Accpac Database

Hello,

Hope you all are doing great with Accpac implementations and customizations. Recently I came to know that some of my colleagues and crystal report writers are facing issues at the time on fetching the optional fields form the Accpac database and showing it on the reports. It is may be because the way Accpac keeping the entries in the Database. Ok, now lets see how Accpac maintaining the optional fields and how we can easily filter it into our reports.

When we create any optional field, Accpac create a new entry in the optional field table. Let’s take Inventory control there is the master table "ICITEM" where all items will be saved. Now if we want to attach optional fields with the inventory then first we have to create optional fields from the Common Services “Optional Fields” form. Optional fields and there values will be stored in the optional field table “CSOPTFH” and “CSOPTFD” table. Further when we link it to inventory control module this information save into “ICOFD” table. Now at the time of creating new item we attach already created optional field with the new item and assign optional field value to it. Then, Accpac will store optional field values for each item in “ICITEMO” table with respective item numbers. I will try to explain this by diagrammatic representation:

From the above structure we can figure it out that In “ICITEMO” table the Item and optional field are coming from “ICITEM” and “ICOFD” tables.

Now the problem comes when we are trying fetching this information on the crystal report table where we have to show Optional fields horizontally. If we are putting condition like where optional field is equal to OPT1 then it will not bring OPT2 value on the report and if we not give any condition then it will duplicate the record. To solve this dilemma use duplicate tables instead of trying to retrieve data from one table. Here I am talking about “ICITEMO” table.

For example we need crystal report as follows

Here we can see that optional value one and two for each item is coming in front of respective item. How to do this easily? Let’s see. We know that the optional values are coming from one table “ICITEMO”. So we need to attach this table on the report and link it with “ICITEM”. But as I stated earlier if we give the condition for the optional value then either some optional value will not come or it will duplicate the data. Now to solve this, make and link alias of “ICITEMO” table on crystal report. Take as many number of “ICITEMO” tables as optional fields are. Like, here two optional values are there so we will take the Alias of “ICITEMO” table two times. Further we put the condition for each optional field with separate table. Let’s see how. Following is the crystal report link screen:

Here we can see that the ICITEMO table is used twice and linked to the “itemno” column of “ICITEM” table. Now this is how we can create the alias of the same table with different alias names. Here it is “ICITEMO” and “ICITEMO_1”. We can also give the alias name as the name of the optional field to make it easy. Just note down here that each alias is going to give one optional field value. Now after making such kind of link we have to set the condition also to retrieving the right value. So we will put the condition for each alias.

Now see the fig above, we can see that we are putting the condition that opt1 value should match with “ICITEMO” table and OPT2 should match with “ICITEMO_1” table. After doing this report is ready only we need to put right field at right place.

Like this we can take Optional value one from table “ICITEMO” and optional value two from “ICITEMO_1”.

Hope that this post will help the people how to take optional fields and make the report right. Further to add every person has its own way of doing things and each of them thinks he is right. But I want to say the option that I am giving will solve your problem and this is the easiest way among the couple of other solutions that I have. Even thought if any of you have any better one please comment and share with us.

Thanks and Regards
Aggyey Mishra