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

Tool to schedule Accpac Backup

Hello Everybody,
This is my second post to this blog. Today I am posting one small but very useful tool for Sage Accpac ERP. I am getting requests for some clients for scheduling automatic backup in the Accpac Server. Here I developed a tool for all those who are looking for such kind of functionality.

This is simple yet very effective tool for scheduling backups in Accpac ERP. What you need to do is copy this tool to the Macro folder of Sage Accpac Folder on the server. This tool is design only for the server. Also you need to run Accpac before you run this macro. Now create schedule using Window scheduler for the time you want. After this run the macro first time manually to set some parameters. If you don’t run it then it will ask the parameters when it will run from windows scheduler. When you run it you will see the following screen:

Here organization Id is nothing but your Accpac company ex. SAMLTD. System Database id is nothing but system database name like SAMSYS. And directory path is the path where you want to keep the backup ex "C:\Program Files\Sage Software".
Once you finish click “Save Settings” Button. And it will start taking backup of the set company. This tool will automatically run at the set time and create the new folder each time it runs with date and time. It will put dump in newly created folder.
To Download the tool follow the link:
Accpac_DataDump_Scheduler.rar
Hope that this tool helps Accpac Users worldwide. If you have any concern, Request or suggestions then please don’t hesitate to comment me.

Regards,
Aggyey

Tool to schedule Day End in Accpac

Hello,


Today I am posting one tool for scheduling Day End process in Accpac.
This is a simple but useful macro for all those who want to automatically schedule the Accpac Day end Process. Here is the solution that I am giving to betterment there life. Only what you need to do is, download the file from below mentioned link and put it in the macros folder inside Sage Accpac folder. After it just go to the Windows scheduler in control panel and generate a schedule as per your requirements.

Schedule_Day_End.rar

This is free of cost macro. It only run automatically for the company that is already logged in.
I will try to give some more macros and programs that will help Accpac user world wide.

Thank you all
Aggyey