#14 Filtering Optional Field Values on Crystal Report from Accpac Database

03 Jul

Date: 03 July 2011

Recently I came to know that some of my colleagues and crystal report writers are facing issues at the time of 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.



6 responses to “#14 Filtering Optional Field Values on Crystal Report from Accpac Database

  1. Nicole

    July 9, 2012 at 8:20 am

    your way of thinking is totally right, however, it poses a big problem that you have to be aware of. in case the optional field is not filled, then the entire row will no show.
    in case i have an item which does not have all optional fields filled, and we use your method, then, the whole row will not show. and the item is not listed.

  2. Geoff

    August 22, 2012 at 2:40 pm

    Thank you for the article, its the only comprehensive explanation of this problem I have found on the web! Excellent work.

    Perhaps you can assist with the following problem: Having implemented your solution I am able to generate a perfect report in Crystal Reports 11 using the database connection and all Optional Fields display 100% correctly.
    When I run the exact same report in Accpac live environment, the Optional field all pull ONLY the information from the 1st Optional Field instead of from the 2nd and 3rd fields like they do in the preview in CR?? Any ideas?

    • Geoff

      August 24, 2012 at 7:21 am

      Just in case it helps anyone else, I solved my problem above.
      The only way that I could get the optional fields to pull though in a live Accpac report was to use Subreports to insert the optional fields into the report (NB SEPERATE subreports for each optional field). i.e use the Record Selector to create the link to the optional field in each subreport.

  3. Fahd

    February 11, 2013 at 2:38 pm

    Hi Nicole,

    Did you find a solution in case one of the optional field is empty

    • Aggyey

      March 12, 2013 at 1:57 am

      Yes, Use Left outer Join in case some of the optional fields do not have values.

  4. Fahd

    March 12, 2013 at 7:46 am

    Yes I already did that Thanks!


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: