Frequently Asked Questions (FAQ)

Home               Features & Benefits                Testimonials                Technical Support                FAQ             Order Now

Table of Contents

 Click on the hyperlink below to position on the answer to your question. If you don’t see your question? then email support@ddpcorp.com

How Do I …. ?

Adding a new table to a data set           RW002

Adding a calculated field (also known as data item)       RW003

Adding a percentage of totals (and not a total of percentages)    RW005

Adding percentage of total sales for each item sold – how to create them           RW007

Calling Foxfire!/Pro Series from another program         RW010

Creating an aging report            RW015

Creating a year specific GL YTD amount data item       RW012

Creating a weekdays data item  RW017

Filtering for items that were not sold to a customer         RW011

Including unmatched records in a report (Outer Join)     RW008

Moving the Pro Series installation to a different directory –                                                                 

                                 How to correct Foxfire!/Pro Series paths            RW006

Running a batch of reports from the Pro Series custom menu pad           RW013

Running reports based on ship-to addresses      RW014

Setting relationships for TIW data table SOHEADER in Foxfire!/Pro Series     RW019

Using conditional variables in a report    RW021

Adding Custom Current History Tables  RW022

Sort on compid  RW026

For SQL Server how to remove the time on datetime fields RW027

For SQL Server what to use instead of IIF RW028

Errors, Problems 

Asterisks are displayed in a calculated data item that uses IIF in the expression RW018

Fonts not found where expected           RW020

Maximum number of users exceeded     RW001

Multiple records from AP invoices appear in reports      RW016

No data found when running a report    RW004

Sales Order reports printing deleted lines           RW009

Incorrect page numbers for groups       RW0023

Summary reports and aggregating operator  RW024

Reindex outside the application  RW025

Cannot activate ACTBAR error RW029

 

Detailed Answers

Date

August 1, 2003

Title

RW001 Maximum Number of Users Exceeded

 

 

Problem

The number of users currently in Foxfire! is less than the license allows for yet the last user is seeing an error message than the number of users is exceeded.

This could happen if someone exited Foxfire!/Pro Series through a method other than the normal exit process, which decrements the user counter.

Solution

First, clear flags and sessions in the System Manager for the application FR. Note that all other users must be out of Pro Series. Try going back into Foxfire!/ Pro Series.

If the problem still exists, you can manually clear the counter. Go to the command window of FoxPro. Use sysdata. There should only be one record in sysdata whose sysid begins with FR. Go to that record.

From the command window, type:     REPLACE actsess WITH “ ”

Go back into Pro Series, go into Foxfire!/ Pro Series and the problem will be gone.


 

Date

August 1, 2003

Title

RW002 How to add a table to a data set

 

 

Procedure

Select the data set and proceed to the Report Manager screen. Select Foxfire! from the Utilities menu at the top of the screen. If the Utilities menu does not appear, it is because you do not have that privilege.

From the menu select Edit Data Set.

Choose to edit, and then select the Files button under Advanced Options. The Files screen appears. In the middle of the screen, after Data Items Filter, you will see something similar to:

     INLIST(rpt_ditem.rid_alias1,”APCHCK”, “APMAST”,”APRECO”, “APVEND”)

APCHCK, APMAST, APRECO and APVEND are table names. You need to add your new table name to this list. For example, if we were to add APTRAN, we would have:

INLIST(rpt_ditem.rid_alias1,”APCHCK”, “APMAST”,”APRECO”, “APVEND”, “APTRAN”)

Proofread carefully before saving. Note that there should be no spaces after the commas.

The next step is to Edit File Relationship Info from the Utilities menu. This tells Foxfire! how to connect the new file to an existing one. In order to do this, you must know the connecting field names.

Study the existing records. You will need to add a record, similar to the existing records. Make the first file one that is already in the data set. The second file is your new table. The expression states how the first table is connected to the second table, and the correct expression depends on the particular tables.

Save your new record. Now when you go to the report editor and select data items, you should see your new table and its fields in the list.


 

Date

August 1, 2003

Title

RW003 Adding a calculated field (also known as data item)

 

 

Issue

The user wants to see a value on a report that is the result of a function of one or more fields existing in the data dictionary.

Example 1. Instead of having the full item description appear on a report on AR invoice line items, and taking up lots of space, the user only wants to see the first 15 characters of the description.

Example 2.  The user wants to see the gross profit amount for each line item on an invoice. The extended price is a field, but not extended cost or gross profit amount.

Solution

Add a data item. From the Report Manager screen, choose the appropriate data set. Then click on the Dictionary icon at the top of the screen. You are now in the data item editor screen. Select the List button and scroll down to one of the data fields on which the function will be perform, then press enter, then select to Save As.

Example 1. Use data set AR Invoices. After having selected the ARTRAN Item Description from the List, and having clicked the Save As button, edit the Data Item field to be “ARTRAN Item Short Descrip.”. Edit the expression to read “LEFT(ARTRAN.DESCRIP,15)”. Enter a unique value as the result field name, “ISDESCRIP”. Replaces the 60 Xs in the output picture with 15 Xs. Reduces the size of the output picture to 15. The user clicks on the Filter button, then replaces the 60 Xs in the input picture with 15 Xs, then clicks on OK. Click on Save, then Exit and return to the report manager. From now on ARTRAN Item Short Descrip will appear as a data item available for placing on a report.

Example 2. Use data set AR Invoices. After having selected the ARTRAN Item Total Ext.Price from the List, and having clicked the Save As button, edit the Data Item field to be “ARTRAN Gross Profit Amount.”. Edits description to read “gross profit. Edits the expression to read “ROUND((ARTRAN.EXTPRICE - (ARTRAN.QTYSHP * ARTRAN.COST)) / ARTRAN.EXTPRICE*100,2)”. Enters a unique value as the result field name, “GPAMOUNT”. Edit the first line of the column header to be “Gross Profit”. Edit the second line of the column header to be “Amount”. Click on Save, then Exit and return to the report manager. From now on ARTRAN Gross Profit Amount will appear as a data item available for placing on a report.

Additional Reference

Pro Series Technical Reference Manual, Data Dictionary Section; FoxPro Commands and Functions Manual for FoxPro functions and their syntax.


 

Date

August 3, 2003

Title

RW004 No data found when running a report

 

 

Issue

This message occurs because of the way the user has created the report, or because no data exists.

Solution

The user should first remove all filters and attempt to run the report again. If there is data, the problem is in how the filters were set. The user should review his filter conditions.

If there are no filters, but the report is run from multiple tables, the user should delete data items from the report so that there are only data items from a single table (same first six characters, e.g. ARMAST). Run the report. If there are still no data found, Foxfire!/Pro Series is not finding any records in the Pro Series data and the user needs to add some records.

If there is data with the single table, the user can then start adding back data items. Add from a second table, then run the report. Then add from a third table, and so on, until the user identifies at which point there are no data records.

The user is experiencing an outer join issue. Refer to the outer join FAQ.


 

Date

August 1, 2003

Title

RW005 How to create a percentage of totals (and not a total of percentages)

 

 

Issue

You have a data item that is a percentage calculation. On the group total line, you see a sum of the percentages. What you want to see is a percentage of the sums.

Example: You have added a data item to ARTRAN whose expression is gross profit divided by extended price multiplied by 100. It is call ARTRAN GP%. You create a new report which has the following data items: invoice number, extended price, gross profit amount, and GP%. You have the report sorting and grouping on the invoice number. When you print the report, you see a group footer line after each invoice with the invoice total extended price, invoice total gross profit amount and invoice total GP%. Instead of the total GP% (a sum of the GP% of each invoice line), you want to see the invoice total gross profit amount divided by the invoice total extended price multiplied by 100.

Solution

You need to weight the GP% data item by the extended price.

Edit data items, and select the GP% item. Click on Edit, then on the Calculations button. The default is Subtotals  Yes. Change this to Subtotals   Other Summary Options. Under Calculate, select Weighted Avg. You will be prompted for the item to weigh: choose the appropriate item (in our example it is extended price). Click on OK. Enter a report variable: it is not important what you call it. Then click on OK and Save the changes to the data item.

Now when you print the report you should see the percentage of the sums.


 

Date

August 1, 2003

Title

RW006 How to correct Foxfire!/Pro Series paths after moving the Pro Series to a different directory

 

 

Problem

User moves Pro Series to a different directory and edits sysdata, sycdata, sycpath records to reference new directory, or runs movepro. User can access other Pro Series applications OK, but gets error when going into Foxfire!/Pro Series.

Solution

Exit Foxfire!/Pro Series.

Create a file named fffirst.dat in the \fr directory. It does not matter what is in the file. You can copy a file in the \fr directory, then rename it to fffirst.dat. Foxfire!/ Pro Series checks for the existence of this file when starting up, resets  its paths if it is there, then deletes the file.

Open Foxfire!/ Pro Series.

Comments

You will know you have been successful when Foxfire!/ Pro Series starts updating the paths when it is started up.


 

Date

August 1, 2003

Title

RW007 How to get percentage of total sales for each item sold

 

 

Problem

User needs to create a report, listing each AR invoice and its percent of the total sales for invoices in the report.

Solution

This is a “two-pass” report. The report needs to go through the data once to total sales, then a second time to generate the report.

Edit data items, select ARMAST Total Without Tax, and Save As ARMAST Percent Total Sales. Make the expression be 00000000000000.00, and enter totsales as the result field name. Change the column heading to Percent of Total Sales. Save the record.

In your report, output, special processing, in the field for AFTER QUERY, EXECUTE type DO \pro71\fr\totsales (change to reflect your system’s path).

Add the data item ARMAST Percent of Total Sales to your report.

Create a FoxPro program in the fr directory called totsales.prg as follows:

LOCAL;

  lc_tempname, ;

   lc_wasalias, ;

   ln_totsales

lc_wasalias = ALIAS()

lc_tempname = SYS(3)

COPY ALL TO (lc_tempname)

USE (lc_tempname) ALIAS ac_sales

SUM ALL invamtn TO ln_totsales

REPLACE ALL totsales WITH ROUND(invamtn/ln_totsales*100,2)

SELECT * FROM sales INTO CURSOR &lc_wasalias

USE IN SELECT(“ac_sales”)

DELETE FILE (lc_tempname + ".dbf")

RETURN

This technique, using the appropriate fields, will work for other percentage of total amounts.


 

Date

August 1, 2003

Title

RW008 How to include unmatched records in a report (outer join)

 

 

Problem

You want to create a report that includes unmatched records. This requires what is called, in report writing terminology, an ‘outer join.’

For example, you define a report using data items from two tables, Customers  and Invoices. When you run the report, it lists the customers in the Customers table where there are one or more matching records in Invoices.  But if a customer has no invoices, the customer is not included in the report. (This output is the result of what is called an inner join.).  For each record in Customers there may be zero, one, or many matching records in Invoices. This is a one-to-many relationship, where Customers is the parent table (the table that “drives” the relationship) and Invoices  is the child table. The inner join matches parent records to child records, and omits records that don’t match.

But you want the report to list all customers, whether they have invoices or not. This requires what is called an outer join. Outer joins can be of two types:

Parent records with no children — customers with no invoices

                                    — OR—

Child records with no parents — invoices with no customers

Foxfire!/Pro Series makes it easy to create outer joins between two tables. With the Filter Builder’s Special Filter options you can choose to include unmatched records in a report.   This outer join feature is available only under the following conditions:

·         The source of the report comes from at most two tables.
Outer join options may not be offered in some reports, even though all the selected data items come from one or two tables. This is because filters, sorts and calculations can be based on data items not actually appearing in the output. If any of these data items depends on a third or fourth table, outer joins will not be offered.
If the report is using only a single table, Foxfire!/ Pro Series allows an outer join with another table so you can test for the existence of the linking data item in that other table. This is a convenient way to create exception reports for orphaned parent and child records.

·         The report must be a detail or label report.
Summary and cross-tab reports, and reports with aggregating expressions in any data item cannot be joined with an Outer Join.

If these limitations prevent you from using the Filter Builder’s outer join feature, there are other approaches to creating outer join results in Foxfire!/Pro Series.   The following solutions show you step by step how to create an outer join for a 2-table report and a 3-table report.

Solution

Creating a 2-Table Outer Join Report with a Union:

This method solves the problem by breaking it down into two parts:

1. Create one report to list customers with invoices

2.  Create a second report to list customers with no invoices and combine it with the first report.

First, let’s create the report that list customers with invoices:

1.        Using the AR Invoices data set, add a new detail report, giving it the name CUST_A, and description “Customers with Invoices.”

2.        Select the following data items:

ARCUST Company Number

ARCUST Company Name

ARMAST Invoice Number

ARMAST Total with Tax

3.        Choose to sort and group by ARCUST Company Number.

CUST_A is a straightforward report which gets its information from ARCUST and ARMAST.  However, as explained above, customers with no invoices can't be included this way.  So you need to create a second report (which we will name CUST_B) which will list customers with no invoices.  Then the two reports will be combined (union).

To create the second report that lists customers with no invoices:

1.        While in CUST_A, select Save As, and save the report as CUST_B, with a description of “Customers with No Invoices.”

2.        Return to the Report Manager and click on the Dictionary icon at the top of the screen.
In the Data Item Editor you need to create the following calculated data items:

A Filter Only data item. The Filter Only data item is a calculated data item that, when added to the secondary report, limits retrieval of parent records to those with no child records (in this case, customers with no invoices). The Filter Only data item consists of a filter that specifies a nested SQL SELECT statement (called a subquery).  Whenever you include this special data item in a report, Foxfire!/ Pro Series adds this clause to the resulting SQL SELECT statement, limiting retrieval of customers to those with no invoices.

Placeholder data items.  The placeholders are data items you must create with expressions that return blank values of exactly the same  data type and result width as they would have had if they were populated with data.

1.        To create the Filter Only data item:

In the Data Item Editor, select Add, and complete the fields as follows:
       Data Item: Customers with No Invoices

Expression: (subquery)

Data Type: Logical

Primary Source File: (ARCUST)

Primary Source Alias: ARCUST

2.        Select Filter, and complete the following fields:

Allow Filter: Only

Filter (Where) Expression:

ARCUST.CUSTNO NOT IN (SELECT ARMAST.CUSTNO AS CUSTNOR8 FROM (ARMAST)ARMAST)

3.        To create a placeholder data item for a character-type data item:

In the Data Item Editor, select Add, and complete the fields as follows:

Data Item: BLANK Invoice Number

Expression: SPACE(10)

Data Type: Character

Result Field Name: XINVNOR8

Primary Source File: (ARCUST)

Primary Source Alias: ARCUST

Column Header: Invoice

                           Number

Output Picture: XXXXXXXXXX

Output Width: 10

4.        To create a placeholder data item for a numeric-type data item:

In the Data Item Editor, select Add, and complete the fields as follows:

Data Item: BLANK Total with Tax

Expression: 000000000000.00

Data Type: Numeric

Result Field Name: XINVAMT

Primary Source File: (ARCUST)

Primary Source Alias: ARCUST

Column Header: Invoice

                               Total

Output Picture: 999999999999.99

Output Width: 15, 2

Return to the Report Manager, and select CUST_B.

1.        Select Data Items, and remove from the Selected list the data items ARMAST Invoice Number ARMAST Total with Tax.  From the Available list, select BLANK Invoice Number and BLANK Total with Tax.  Save.



2.        Select Filter.  Select Add, and select the data item Customers with No Invoices.  Save.


3.        So the report CUST_B is created with new data items, the filter-only data item, “Customers with No Invoices” and two “placeholder” data items, BLANK Invoice Number and BLANK Total with Tax.  The next step is to join the two reports.

Before you join, or merge, the results of the two reports, run each report separately to see that it produces the desired results.  Run CUST_A and confirm that it lists customers with invoices.  Then run CUST_B and confirm that it lists customers with no invoices. 


4.        To merge the results of the two reports:

Select the report CUST_A. 

Select Filter.  In the section Merge with Other Query Results (Union), select Run Report, and enter CUST_B as the name of the report to merge with.

While this solution requires many steps, the advantage is that it produces results with relatively fast performance.  The downside is that you must assure that any filter conditions (outside of the filter-only data item) are applied to BOTH reports.  Changes to the filter in one of the reports are not automatically reflected in the other.  Note that the Sort and Group settings in the parent report control the results.  The Sort and Group settings in the child report are ignored.

Creating a 3-Table Outer Join Report by Editing the SQL:

What if your report consists of three (or more) tables instead of two?  This increases the complexity of the outer join and is best approached by editing and saving the SQL.

First you must examine the logic of your report.  What unmatched records are to be included?  For our example, we are going to create a inventory report using the tables ICITEM, ICSUPL, and ICIQTY.  The parent table is ICITEM (Inventory Master file), and we want to list all parent records (all items) in ICITEM, whether or not they have child records in ICSUPL (Inventory Supplier file) or ICIQTY (Item Qty at Location file).

What are all the possible combinations of matching and not matching among the three tables?  For every item in ICITEM, there may be none, one or many matching records in ICSUPL. For every item in ICITEM, there may be none, one or many matching records in ICIQTY.  You want the report to include an item from the parent table (ICITEM) when there is a matching record in both of the child tables, in either of the child tables, or in neither of the child tables.  In the report’s SQL statement, you need to create a SELECT statement for each possible case:

Item is in:          ICITEM           ICIQTY           ICSUPL

Case 1: records match in                x              x                   x

Case 2: records match in                x              x

Case 3: records match in                x                                   x

Case 4: records match in                x

In Case 1, which is really the inner join, the report includes an item when the item is in all three tables.  In Case 2, the report includes an item if it is only in ICITEM and ICIQTY (and not in ICSUPL).  In Case 3, the report includes an item if it is only in ICITEM and ICSUPL (and not in ICIQTY).  In Case 4, the report includes an item if it is found in ICITEM only.

After you have identified all the cases for which SELECT statements need to be created, you are ready to begin creating the report.

F  Be sure you have a report data set that contains the three tables you need for your report.  If one does not exist, you can edit an existing one to add the necessary table(s), or you can use the Setup Wizard to create one.  Refer to the manual for instructions.

Case 1:

Since the first case is for items that are in all three tables, which Foxfire!/ Pro Series does automatically when you create a report, we will begin by creating a normal, detail report:

1.        In the Report Manager, select New.

2.        Select Detail as the report type.

3.        In the Report Editor, Complete the report name and description:
Name: IC3X
Description: Items with Supplier and Serial/Store/Bin Information

4.        In the Data Item Selector, choose the following items:
ICITEM Item Number
ICITEM Standard Cost
ICSUPL Vendor Part Number
ICSUPL Vendor Number
ICIQTY OH Qty

5.    Move to the Output Selector, and select Special Processing Options.  Under Query Generation Options, select Stored SQL, and have Foxfire!/ Pro Series generate the SQL.  It should look like the following statement without the line numbers:

line
no.             SQL SELECT statement
1  SELECT ALL;
2        ICITEM.ITEM                    AS ITEMI7,;
3        ICITEM.STDCOST,;
4        ICSUPL.VPARTNO,;
5        ICSUPL.VENDNO,;
6        ICIQTY.QONHAND;
7  FROM (ICITEM) ICITEM,;
8        (ICSUPL) ICSUPL,;
9        (ICIQTY) ICIQTY;
10  INTO CURSOR IC3X;
11  WHERE ICITEM.ITEM = ICIQTY.ITEM;
12   
AND ICITEM.ITEM = ICSUPL.ITEM

Case 2:

Now you need to edit the SQL to add a SELECT statement for case 2, where the ICITEM item has a matching record in ICIQTY but not in ICSUPL. Because the second SELECT statement will be selecting records from ICITEM and ICIQTY only, you must edit the statement to remove selection of ICSUPL items and replace them with blank placeholders (refer to the blank data items created in the example for the 2-table outer join report, above):

1.        Edit line 1 (first line of the first SELECT statement) to change it from SELECT ALL to SELECT DISTINCT.  We will be combining the results of several SELECT statements,which would cause duplication of some records.  By selecting DISTINCT, FoxPro will remove duplicates from the result.

2.        On line 12, add a semi-colon (;) after the last word.

3.        On line 13, after the first SELECT statement, type:
UNION ALL;

4.        On line 14, type:
SELECT
then copy lines 2 through 12 and paste them immediately after SELECT.

5.    Edit the list of data items in the second SELECT statement to replace ICSUPL.VPARTNO with SPACE(15) and replace ICSUPL.VENDNO with SPACE(6).

6.        In the FROM section of the SELECT… FROM statement, remove the line: (ICSUPL) ICSUPL

7.        Replace the concluding WHERE statement with a WHERE clause to include ICITEM items that are not in ICSUPL:
WHERE .T.;
       
AND ICITEM.ITEM = ICIQTY.ITEM;
       
AND ((Icitem.Item);
         NOT IN (SELECT DISTINCT;
                  Icsupl.Item;
                 FROM (ICSUPL) ICSUPL))


8.        At this point, your SQL statement should look like this:
SELECT DISTINCT;
        ICITEM.ITEM                    AS ITEMI7,;
        ICITEM.STDCOST,;
        ICSUPL.VPARTNO,;
        ICSUPL.VENDNO,;
        ICIQTY.QONHAND;
  FROM (ICITEM) ICITEM,;
        (ICSUPL) ICSUPL,;
        (ICIQTY) ICIQTY;
  INTO CURSOR IC3X;
  WHERE ICITEM.ITEM = ICSUPL.ITEM;
   
AND ICITEM.ITEM = ICIQTY.ITEM;
  UNION
ALL;
SELECT ICITEM.ITEM                    AS ITEMI7,;
        ICITEM.STDCOST,;
        SPACE(15),;
        SPACE(6),;
        ICIQTY.QONHAND;
        FROM (ICITEM) ICITEM,;
        (ICIQTY) ICIQTY;
  WHERE .T.;
       
AND ICITEM.ITEM = ICIQTY.ITEM;
       
AND ((Icitem.Item);
        NOT IN (SELECT DISTINCT;
                  Icsupl.Item;
                 FROM (ICSUPL) ICSUPL))


Case 3:

For case 3, the report includes the item if it is only in ICITEM and ICSUPL.  To add a SELECT statement for case 3, repeat the process described for case 2, including:

·         Remove selection of items from ICIQTY, replacing ICIQTY data items with blank placeholders.

·         Add a WHERE clause for items in ICITEM that are not in ICIQTY

When complete, you should have added a the SELECT statement for case 3 that should look like the following:

UNION ALL;

SELECT ICITEM.ITEM,;

        ICITEM.STDCOST,;

        ICSUPL.VPARTNO,;

        ICSUPL.VENDNO,;

        000000000.00;

        FROM (ICITEM) ICITEM,;

             (ICSUPL) ICSUPL;

WHERE .T.;

        AND ICITEM.ITEM = ICSUPL.ITEM;

        AND ((Icitem.Item);

         NOT IN (SELECT DISTINCT;

                     ICIQTY.ITEM;

                     FROM (ICIQTY) ICIQTY))

Case 4:

For case 4, the report includes an item if it is only in ICITEM.  To add a SELECT statement for case 4, repeat the process described for case 2, including:

·         Remove selection of data items from ICIQTY and ICSUPL, including replacing ICIQTY and ICSUPL data items with blank placeholders

·         Add a WHERE clause for items in ICITEM that are not in ICIQTY and ICSUPL.

When complete, the entire SQL should look like the following:

SELECT DISTINCT;

        ICITEM.ITEM                    AS ITEMI7,;

        ICITEM.STDCOST,;

        ICSUPL.VPARTNO,;

        ICSUPL.VENDNO,;

        ICIQTY.QONHAND;

  FROM (ICITEM) ICITEM,;

        (ICSUPL) ICSUPL,;

        (ICIQTY) ICIQTY;

  INTO CURSOR IC3X;

  WHERE ICITEM.ITEM = ICSUPL.ITEM;

    AND ICITEM.ITEM = ICIQTY.ITEM;

  UNION ALL;

SELECT ICITEM.ITEM                    AS ITEMI7,;

        ICITEM.STDCOST,;

        SPACE(15),;

        SPACE(6),;

        ICIQTY.QONHAND;

        FROM (ICITEM) ICITEM,;

        (ICIQTY) ICIQTY;

  WHERE .T.;

        AND ICITEM.ITEM = ICIQTY.ITEM;

        AND ((Icitem.Item);

         NOT IN (SELECT DISTINCT;

                  Icsupl.Item;

                 FROM (ICSUPL) ICSUPL));

  UNION ALL;

SELECT ICITEM.ITEM,;

        ICITEM.STDCOST,;

        ICSUPL.VPARTNO,;

        ICSUPL.VENDNO,;

        000000000.00;

        FROM (ICITEM) ICITEM,;

             (ICSUPL) ICSUPL;

WHERE .T.;

        AND ICITEM.ITEM = ICSUPL.ITEM;

        AND ((Icitem.Item);

         NOT IN (SELECT DISTINCT;

                     ICIQTY.ITEM;

                     FROM (ICIQTY) ICIQTY));

  UNION ALL;

SELECT ICITEM.ITEM,;

        ICITEM.STDCOST,;

        SPACE(15),;

        SPACE(6),;

        000000000.00;

        FROM (ICITEM) ICITEM;

WHERE .T.;

        AND ((Icitem.Item);

         NOT IN (SELECT DISTINCT;

                     ICIQTY.ITEM;

                     FROM (ICIQTY) ICIQTY));

        AND ((Icitem.Item);

         NOT IN (SELECT DISTINCT;

                     ICSUPL.ITEM;

                     FROM (ICSUPL) ICSUPL))

F  When editing the SQL, be careful to to add commas, semi-colons, and parentheses as appropriate.  Failure to add them appropriately will result in an error message when you run the report.

F  Do not use BOTH current and history files when running a report that uses a saved SQL.


 

Date

August 1, 2003

Title

RW009 Sales Order reports printing deleted lines

 

 

Problem

Items were deleted from a sales order but they continue to appear on the printed reports.

Solution

In the Pro Series sotran tables, the item Status is flagged with an X but is not deleted when a user deletes a line from a sales order. The user must add the filter ‘SOTRAN Status of Sales Order Is Not Like X’ to the report. 


 

Date

August 1, 2003

Title

RW010 Calling Foxfire!/Pro Series from another program

 

 

Problem

How to call a Foxfire!/ Pro Series report from the Pro Series custom menu pad

Solution

Use the following steps:

1.      In System Manager choose Maintain from the Menu bar.

2.      Then select Setup, System Installation, which will bring up the System Installation window.

3.      From the System Installation Menu Options choose Custom.

4.      From the Custom Menu Options choose Edit.

5.      Under Menu Text use a meaningful name or description.

6.      Under the Command Line add a command.

Example:

DO  \pro71\fr\rw.fxp WITH “AR Customer”,“.RQST”,“CUST”,“99”

In the above example, “AR Customer” is the data set name, “CUST” is the report name, and the company to report on is 99.

8.  Save.

Comments

If the report doesn’t run, it is possible that the data set or report name is typed incorrectly.

For Pro 6.5, in which the main program is fr.fxp:

DO  \pro71\fr\fr.fxp WITH “AR Customer”,“.RQST”,“CUST”,“99”


 

Date

August 1, 2003

Title

RW011 Filtering for items which were not sold to a customer

 

 

Problem

Need a report that shows which items were not sold to a specific customer.  For this example, the customer number is ABC001.

Solution

Create a Filter Only Data Item in the Data Item Editor.  This Filter Only Data Item will then be used in the Filter Builder screen of the report.

7.      In the Data Item Editor Choose a similar data item and choose Save As.  In this situation, select ICITEM Item Number.

8.      Change the Data Item name so it is unique but meaningful to you.  For this example we’ll call it ICITEM Number Not ABC001.

9.      Data Type needs to be Logical.

10.  It doesn’t matter what is in the Column Header, Output Picture, Output Width and Sample Data.

11.  Under Special Features Choose Filter.

12.  Check Only under Allow Filter.

13.  The Filter (Where) Expression will look like the following example.

Example:

Icitem.item not in (select artran.item from (artran) artran where artran.custno=“ABC001”)

14.  Choose OK.  Now the Expression in the Report Editor will be (subquery).

15.  While in the Report Editor screen choose Filter and pick the new data item that was just created. Choose any other filter conditions desired and then OK.

16.  To have an invoice date range change the Filter (Where) Expression as follows:

Example:

Icitem.item not in (select artran.item from (artran) artran where artran.custno=“ABC001” and BETWEEN(CTOD(artran.invdte),{06/01/97},{06/30/97}));

17.  To change the Customer Number and Invoice date at “runtime” it will have to be done in the SQL statement.

a)      While at the Report Manager screen go to the top menu bar and click on the Foxfire!/Pro Series menu option and select Show SQL.

b)      Press the F6 key to edit the SQL statement.

c)      Edit the Custno. and/or the dates.

d)      Close the SQL window by hitting Ctrl+w, Ctrl+F4 or by going to the upper left hand corner of the window, click once, and choose Close.

e)      Then choose save when the window pops up.

f)        Now run the report as usual.

If there is an error with the SQL that was just made, go back to the SQL window and press F7 to clear the last changes made and return to the previously saved SQL statement.  Note that a misplaced comma will cause an error, so you must edit carefully.


 

Date

August 1, 2003

Title

RW012 Creating year specific GL YTD amount data item

 

 

Problem

Need a report that shows GL YTD Amount for two or more different years.

Solution

Create Data Items in the Data Item Editor for each year. In this example the GLBALS table is opened twice and it is given a different alias each time.  In this case GLBALS02 and GLBALS03.

 

ADD NEW TABLES TO DATA SET

1.      GLBALS02 and GLBALS03 need to be added to the Data Set table.

a)      From the Report Manager screen select Foxfire!/SBT from the top menu bar.  Then choose Utilities, then Edit Data Set.

b)      Choose Edit, then from Advanced Options select Files.

c)      In the Files screen, at about the middle of the screen,  you will see INLIST(Rpt_ditem.rid_alias1,“GLACNT”,“GLBALS”,…).  To this list “GLBALS02” and “GLBALS03” need to be added. 

CREATE FILE RELATIONSHIPS

1.      To create new relationships choose Utilities from the menu bar while in the Report Manager screen. 

2.      Choose Edit File Relationship Information.

3.      In the File Relationship (Join) Editor choose GLBALS and Save As.

4.      For File1:

a)      Alias is GLBALS02.

b)      Descriptive name is GL Account Balances Records.

c)      File name is (GLBALS).

18.  For File2:

a)      Alias is GLACNT.

b)      Descriptive name is GL Accounts.

c)      File name is (GLACNT).

7.      The Relationship Expression in this example will be:

GLBALS02.glacnt=glacnt AND LEFT(GLBALS02.YEARPRD,4)=“2002”

8.      Repeat #3 and #4 for GLBALS03.

CREATE NEW DATA ITEMS

1.      In the Data Item Editor Choose a GL YTD Amount data item and choose Save As.

2.      Change the Data Item name so it is unique but meaningful to you.  For this example we’ll call it GLBALS02 Year and Period.

3.      Expression should look like the following example.

4.      Example:

5.      RIGHT(GLBALS02.YEARPRD,2)

6.      The Description for our example is Yearprd.

7.      Data Type needs to be Character.

8.      The Result Field Name in our case will be YEARPRD02.

9.      Under Primary Source:  File remains the same, in our example (GLBALS).  Alias is changed to GLBALS02.

10.  For our example Column Header is MM, Output Picture is XX, Output Width = 2 and the Sample Data is 02.

11.  Then choose Save.  Repeat the above steps for the year 2003 or any year desired.

12.  Next we need to create a YTD for 2002 Data Item.

13.  For this we will choose the Data Item that was just created and Save AS.

14.  Change the Data Item name so it is unique but meaningful to you.  For this example we’ll call it GLBALS02 YTD 2002.

15.  The Description in this case is Ytdcr.

16.  Expression should look like the following example.

17.  Example:

18.  GLBALS02.YTDCR-GLBALS02.FYBGBAL

19.  Data Type needs to be Numeric.

20.  The Result Field Name in our case will be GLYTD02.

21.  Under Primary Source:  File remains the same, in our example (GLBALS).  Alias is changed to GLBALS02.

22.  For our example Column Header is YTD 02 Amount, Output Picture is 9999999.99, Output Width = 10, 2 and the Sample Data is 337400.94.

23.  Then choose Save.  Repeat the above steps for the year 1903 or any year desired.

24.  Return to the Report Editor screen. Choose the new Data Items desired. In our example they would be GLBALS02 YTD 2002 and GLBALS03 YTD 2003.

25.  While in the Report Editor screen choose Filter and pick the new data item GLBALS02 Year and Period that was just created. Filter it as IS LIKE and then the month you desire or ask at runtime.  Repeat for GLBALS03 Year and Period.

26.  Choose any other filter conditions desired and then OK.

27.  Sort and Group as desired.

28.  At this point it is recommended that a report header be prompted at run time for you report. To do this, see the following steps.

a)      At the Report Editor screen, choose Output.

b)      Next choose Options listed under Output Type.

c)      Under Formatting check the box for always prompt for Header.

d)      OK

e)      OK

Then choose Save.


 

Date issued

August 1, 2003

Title

RW013 How to run a batch of reports from the Pro Series custom menu pad

 

 

Problem

User wants to have a batch of reports run at night, unattended.

Solution

Use the following steps:

 

1.      Create a batch of reports. In the Report Manager screen, select Batch Builder from the menu, choose which reports are to be in the batch, then save the batch to a file, for example weekly.dbf

 

2.      In System Manager choose Maintain from the Menu bar.

3.      Then select Setup, System Installation, which will bring up the System Installation window.

4.      From the System Installation Menu Options choose Custom.

5.      From the Custom Menu Options choose Edit.

6.      Under Menu Text use a meaningful name or description.

7.      Under the Command Line add a command.

8.      Example: DO fr\rw WITH “fr\out\weekly",".BATCH", "",   "01", “CURRENT","KHIG"

9.      Save.

 

Comments

Only reports from the same data set can be included in a batch.

 

You may edit the record(s) in the batch.dbf (for example fr\out\weekly.dbf) table to store ask at runtime values for report(s) with one or more ask at runtime filters.

 

Store filter values in a array named: AskValues[] with filter values for all ask at runtime filter values in order. Use "*IGNORE*" to ignore optional ask at runtime values. Use *DEFAULT* to accept the default for lines with a default.

 

EXAMPLE: A request has 10 filter lines with required ask at runtime values

on lines 2 & 4 and optional ask at runtime values on lines 6 & 8. To set filter lines 2,4 & 8 and treat line 6 as ignored, enter the commands below at the command window:

 

  DIMENSION AskValues[4]

  AskValues[1] = <filter line 2 value>

  AskValues[2] = <filter line 4 value>

  AskValues[3] = "*IGNORE*"

 

If any ask at runtime values are not specified or have mismatched data types, the user will be prompted for the ask at runtime value.


 

Date

August 1, 2003

Title

RW014 How to run reports based on ship to addresses

 

 

Problem

User wants to run reports based on ship to addresses rather than the regular customer address.

Solution

Resellers frequently encounter the issue of needing a report with customer, invoice and invoice shipping information. This is tricky because of the need for an outer join, since araddr (and soaddr) have records only for invoices (and sales orders) with alternate ship to addresses.

A reseller can simply instruct the end-user to always use the alternate ship to address, even when shipping directly to the customer. But what if the end-user forgets?

One solution is to create a short program to check araddr (or soaddr) for records for each invoice (or sales order) and add records that are missing. This program can be run before the report query.  The steps are below.

1.       Create and compile a program using FoxPro. For our example, we will call it shipto.prg and we will save it in the s:\pro71\fr directory. The program is:

PRIVATE lc_oldAlias

Lc_oldAlias = ALIAS()

SELECT armast.custno, armast.invno, ;

  arcust.company, arcust.address1, ;

  arcust.address2, arcust.city, arcust.state, ;

  arcust.zip, armast.custno AS cshipno ;

  FROM s:\pro71\ardata\armast01 armast,;

  s:\pro71\ardata\arcust01 arcust;

  INTO CURSOR shipto;

  WHERE armast.custno = arcust.custno AND ;

  armast.custno + invno NOT IN (SELECT custno + invno AS cinv;

  FROM s:\pro71\ardata\araddr01 araddr)

IF _TALLY > 0

  SELECT araddr

  IF FLOCK()

    APPEND FROM shipto

  ENDIF

ENDIF

USE IN SELECT(“armast”)

USE IN SELECT(“araddr”)

SELECT arymst.custno, arymst.invno, ;

  arcust.company, arcust.address1, ;

  arcust.address2, arcust.city, arcust.state, ;

  arcust.zip, arymst.custno AS cshipno ;

  FROM s:\pro71\ardata\arymst01 arymst, ;

    s:\pro71\ardata\arcust01 arcust;

  INTO CURSOR shipto;

  WHERE arymst.custno = arcust.custno AND ;

   invno NOT IN (SELECT invno ;

  FROM s:\pro71\ardata\aryadr01 aryadr)

IF _TALLY > 0

SELECT aryadr

  IF FLOCK()

    APPEND FROM shipto

  ENDIF

ENDIF

USE IN SELECT(“arymst”)

USE IN SELECT(“aryadr”)

USE IN SELECT(“arcust”)

USE IN SELECT(“shipto”)

SELECT (lc_oldAlias)

RETURN

Note: you can substitute somast/soymst and soaddr/soyadr for sales orders.

2.  Create or edit the report with ship to information. In the output screen, click Special Processing Options. In the before query execute, enter DO s:\pro71\fr\shipto.fxp.

3. While editing the report, go to the filter builder screen and add a filter for armast invoice status NOT like "V" to filter out void records. The above program does not handle void records correctly because of a second occurrence of the invoice number in armast.

4. Run the report.

Comments

The advantage to this approach is that it is simple and it works. The disadvantage is that you must have FoxPro to compile the .prg, it requires a knowledgeable person to set it up, and, unlike any other function of Foxfire!/Pro Series, it causes the Pro Series data to be changed.


 

Date

August 1, 2003

Title

RW015 How to create an aging report in Foxfire!/Pro Series

 

 

Problem

User wants to create aging reports in Foxfire! rather than using the report provided in the Pro Series.

Solution

The easiest way to do this is to create a short program to define the aging parameters desired for the report.  The steps are below.

2.       Create and compile a program using FoxPro. For our example, we will call it arage.prg and we will save it in the s:\pro71\fr directory. The program is:

PARAMETERS pd_invdte, pn_pnet

LOCAL ll_return

ll_return = "00"

DO CASE

  CASE gd_sdate - (pd_invdte + pn_pnet)  > 90

    ll_return = "90"

  CASE BETWEEN(gd_sdate - (pd_invdte + pn_pnet),61,89)

    ll_return = "60"

  CASE BETWEEN(gd_sdate - (pd_invdte + pn_pnet),31,59)

    ll_return = "30"

  OTHERWISE

    ll_return = "00"

ENDCASE

RETURN ll_return

3.       A new Data Item will need to be created using the Data Item Editor.

a)      Choose edit a Data Item from ARMAST that is a character Data Type and choose Save As.

b)      Change the Data Item name to something meaningful to you, in this case we choose ARMAST Aging bucket.

c)      The Description in this case is blank.

d)      Change the Expression to be:

arage(armast.invdte,armast.pnet)  

e)       Change the Result Field Name to ARAGEB.

f)        Change to Column Header to something appropriate for your purposes.

g)       Edit the Output Picture to: XX

h)       Change the Output Width to: 2

i)         Save.

3.       For our report we wanted to show the due date for the invoice so we created another Data Item to handle this.

a)      Choose edit a Data Item from ARMAST that is a date Data Type and choose Save As.

b)      Change the Data Item name to something meaningful to you, in this case we choose ARMAST Due Date (D).

c)      Description should be: Invdte

d)      Change the Expression to be:

armast.invdte + armast.pnet 

e)       Change the Result Field Name to DUEDAY.

f)        Change to Column Header to something appropriate for your purposes.

g)       Edit the Output Picture to: XXXXXXXXXX

h)       Change the Output Width to: 10

i)         Save.

4.       Now create your report including the new data items.


 

Date

August 1, 2003

Title

RW016 Foxfire!/Pro Series report is showing multiple records of AP invoices

 

 

Problem

User creates a report in Foxfire!/Pro Series AP Checking or AP Invoices and the report shows multiple listings for some invoices.

Solution

Since the Pro Series flags the invoices as void and then allows the user to re-enter that invoice again under the same invoice number, there are multiple records with that invoice number in APMAST and APDIST.  There are the records for the voided invoice and for the corrected version of that invoice.

To prevent the appearance of the multiple listings on the report, filters need to be added.

1.       In the Report Editor screen click on the Filter button.

2.       Add APMAST Invoice Status Is Not Like V.

3.       Add APDIST Inv. Status ID Is Not Like V.

4.       Choose OK.

Now the report should show the invoices without duplications.


 

Date

August 1, 2003

Title

RW017 How to create a weekdays data item

 

 

Problem

A reseller was trying to create a data item that would indicate the number of week days it took to ship an order.  All orders and shipments will be a week day date.  But if the order was taken on Friday and shipped on the following Monday, that is a one day turn-around, not 3 days.  The reseller tried using an expression, but was not producing the desired results.

Solution

This task is complicated enough that it requires the use of a UDF (user-defined function).  To create the Weekdays data item:

1.       Create a FoxPro program called weekdays.prg (below), and place in the \fr directory. 

2.       *-- weekdays.prg

*--used to determine the number of weekdays between two days

*-- does not allow for holidays

*--requires two parameters, begin date and end date

*--if parameters are character type, this will convert them to date type

*--example: WEEKDAYS (armast.invdte, armast.ordate)

*--will return number of weekdays to ship and invoice an order

PARAMETERS ld_bdate, ld_edate

IF TYPE(“ld_bdate”) =”C”

ld_bdate = CTOD(ld_bdate)

ENDIF

IF TYPE(“ld_edate”) =”C”

ld_edate = CTOD(ld_edate)

ENDIF

ln_bdow = DOW (ld_bdate)

ln_edow = DOW(ld_edate)

DO CASE

CASE ln_bdow = 7

ld_bdate = ld_bdate + 2

CASE ln_bdow = 1

ld_bdate = ld_bdate + 1

ENDCASE

DO CASE

CASE ln_edow = 7

ld_edate = ld_edate - 1

CASE ln_edow = 1

ld_edate = ld_edate – 2

ENDCASE

ln_exdays = MOD(ld-edate – ld_bdate,7)

ln-numdaysINT((ld-edateld_bdate)/7)*5

ln_numdays = ln_numdays = ln_exdays

RETURN(ln-numdays)

3.      In the Data Item Editor, create the data item.  In the Expression field, use WEEKDAYS followed by two parameters enclosed in parentheses and separated by a comma: the begin date and the end date. For example:

WEEKDAYS(armast.invdte,armast.ordate)

This example will return the number of weekdays to ship and invoice an order.


 

Date

August 1, 2003

Title

RW018 How to avoid asterisks as a result for a calculated data item that uses IIF in the expression

 

 

Problem

The result of a newly created data item, which is using an IIF statement, is ***.** on your report.

Solution

When a data item called with an IIF statement place holding zeros for the data items output width must be included in the statement.

Examples for a data item with an output width and picture width of 15,2

4.       Example of an incorrect IIF statement:

IIF(sotran.rqdate>=sotran.shipdate,ROUND(sotran.qtyshp*sotran.price,2),0)

5.      Example of a correct IIF statement:

IIF(sotran.rqdate>=sotran.shipdate,ROUND(sotran.qtyshp*sotran.price,2),000000000000.00)

Comments

When FoxPro executes an SQL command and creates a cursor, it sizes the columns based on the first fields.  If the first record is 0, the column is then too small for subsequent values.  You should do this with all IIF statements.


 

Date

August 1, 2003

Title

RW019 How set relationships for TIW data table SOHEADER in Foxfire!/Pro Series

 

 

Problem

Setting up relationships for TIW data table SOHEADER.

Solution

To set up the relationship in the Edit File Relationships from the Utilities menu type in the following expression:

PADL(ALLTRIM(SOHEADER.SALESNO),10) + SOHEADER.PARTNO =

SOTRAN.SONO + SOTRAN.ITEM


 

Date

August 1, 2003

Title

RW020 Fonts not found where expected error

 

 

Problem

After installing Foxfire! you receive the error “Fonts not found where expected” when entering Foxfire!/Pro Series.

Solution

There are three font files that need to be installed into \Windows\Fonts. The files will be found in the \FR directory and are: foxfont.fon, ffdix25a.fot and ffdix25a.ttf. Copy the three files into your \Windows\Fonts directory, then go to the Windows Control Panel (Start | Settings | Control Panel) and select Fonts.  Select File | Install New Font, and add FoxFont and Dixon Mono Thin.


 

Date issued

August 1, 2003

Title

RW021 Allows user use conditional variable in report

 

 

Problem

User wants to run a report where a field used in the select statement can be modified to allow the user alter the way the report selects information. In this instance, the enduser wants to run a PTD column where the user selects the month (period) to be used.

Solution

LN_REPTMONTH is the numerical variable that stores the month(number) the user entered; this is compared to the month from artran.invdte

When the report is run a program (in this instance getmonth.prg) is called (using the g_getfld routine) it prompts for a month to be entered (01-12). This is compared, in the FoxFire! data dictionary, to the month in artran.invdte. Matches are made in the select statement, this line

SUM(IIF(ln_reptmonth = MONTH(artran.invdte), artran.extprice, 0000000000.00))

is entered in the FF data dictionary.  For this example, use PRCURNO as the Result Field Name in the data dictionary.

The enduser must do a Save As on a similar data item and edit it to match the underlined area above.

Use this program, which should be in your \fr directory:

*-- getmonth.prg

RELEASE ln_reptmonth

PUBLIC ln_reptmonth

ln_reptmonth = MONTH(gd_sdate)

= g_getfld("ln_reptmonth","99",".T.",.T.,.T.,"Enter month")

RETURN

Insert the line

      DO getmonth.fxp

 into the Execute Before Query, in the Special Processing/Options/Output.

 

 

Table of Contents

 

 

 

RW001 Maximum Number of Users Exceeded. 2

RW002 How to add a table to a data set 3

RW003 Adding a calculated field (also known as data item) 4

RW004 No data found when running a report 5

RW005 How to create a percentage of totals (and not a total of percentages) 6

RW006 How to correct Foxfire!/Pro Series paths after moving the Pro Series to a different directory. 7

RW007 How to get percentage of total sales for each item sold. 8

RW008 How to include unmatched records in a report (outer join) 9

RW009 Sales Order reports printing deleted lines. 16

RW010 Calling Foxfire!/Pro Series from another program.. 17

RW011 Filtering for items which were not sold to a customer 18

RW012 Creating year specific GL YTD amount data item.. 20

RW013 How to run a batch of reports from the Pro Series custom menu pad. 22

RW014 How to run reports based on ship to addresses. 24

RW015 How to create an aging report in Foxfire!/Pro Series. 26

RW016 Foxfire!/Pro Series report is showing multiple records of AP invoices. 28

RW017 How to create a weekdays data item.. 29

RW018 How to avoid asterisks as a result for a calculated data item that uses IIF in the expression. 31

RW019 How set relationships for TIW data table SOHEADER in Foxfire!/Pro Series. 32

RW020 Fonts not found where expected error 33

RW021 Allows user use conditional variable in report 34

RW022 Adding custom current history tables. 35


Foxfire!
Report Writer for the Pro Series

Frequently Asked Questions

Date

August 1, 2003

 

Title

RW001 Maximum Number of Users Exceeded

 

 

Problem

The number of users currently in Foxfire! is less than the license allows for yet the last user is seeing an error message than the number of users is exceeded.

 

This could happen if someone exited Foxfire!/Pro Series through a method other than the normal exit process, which decrements the user counter.

 

Solution

First, clear flags and sessions in the System Manager for the application FR. Note that all other users must be out of Pro Series. Try going back into Foxfire!/ Pro Series.

 

If the problem still exists, you can manually clear the counter. Go to the command window of FoxPro. Use sysdata. There should only be one record in sysdata whose sysid begins with FR. Go to that record.

 

From the command window, type:     REPLACE actsess WITH “ ”

 

Go back into Pro Series, go into Foxfire!/ Pro Series and the problem will be gone.

 

 


Foxfire! Report Writer for the Pro Series

Frequently Asked Questions

Date

August 1, 2003

Title

RW002 How to add a table to a data set

 

 

Procedure

Select the data set and proceed to the Report Manager screen. Select Foxfire! from the Utilities menu at the top of the screen. If the Utilities menu does not appear, it is because you do not have that privilege.

 

From the menu select Edit Data Set.

 

Choose to edit, and then select the Files button under Advanced Options. The Files screen appears. In the middle of the screen, after Data Items Filter, you will see something similar to:

 

     INLIST(rpt_ditem.rid_alias1,”APCHCK”, “APMAST”,”APRECO”, “APVEND”)

 

APCHCK, APMAST, APRECO and APVEND are table names. You need to add your new table name to this list. For example, if we were to add APTRAN, we would have:

 

INLIST(rpt_ditem.rid_alias1,”APCHCK”, “APMAST”,”APRECO”, “APVEND”, “APTRAN”)

 

Proofread carefully before saving. Note that there should be no spaces after the commas.

 

The next step is to Edit File Relationship Info from the Utilities menu. This tells Foxfire! how to connect the new file to an existing one. In order to do this, you must know the connecting field names.

 

Study the existing records. You will need to add a record, similar to the existing records. Make the first file one that is already in the data set. The second file is your new table. The expression states how the first table is connected to the second table, and the correct expression depends on the particular tables.

 

Save your new record. Now when you go to the report editor and select data items, you should see your new table and its fields in the list.

 


Foxfire! Report Writer for the Pro Series

Frequently Asked Questions

Date

August 1, 2003

Title

RW003 Adding a calculated field (also known as data item)

 

 

Issue

The user wants to see a value on a report that is the result of a function of one or more fields existing in the data dictionary.

 

Example 1. Instead of having the full item description appear on a report on AR invoice line items, and taking up lots of space, the user only wants to see the first 15 characters of the description.

 

Example 2.  The user wants to see the gross profit amount for each line item on an invoice. The extended price is a field, but not extended cost or gross profit amount.

 

Solution

Add a data item. From the Report Manager screen, choose the appropriate data set. Then click on the Dictionary icon at the top of the screen. You are now in the data item editor screen. Select the List button and scroll down to one of the data fields on which the function will be perform, then press enter, then select to Save As.

 

Example 1. Use data set AR Invoices. After having selected the ARTRAN Item Description from the List, and having clicked the Save As button, edit the Data Item field to be “ARTRAN Item Short Descrip.”. Edit the expression to read “LEFT(ARTRAN.DESCRIP,15)”. Enter a unique value as the result field name, “ISDESCRIP”. Replaces the 60 Xs in the output picture with 15 Xs. Reduces the size of the output picture to 15. The user clicks on the Filter button, then replaces the 60 Xs in the input picture with 15 Xs, then clicks on OK. Click on Save, then Exit and return to the report manager. From now on ARTRAN Item Short Descrip will appear as a data item available for placing on a report.

 

Example 2. Use data set AR Invoices. After having selected the ARTRAN Item Total Ext.Price from the List, and having clicked the Save As button, edit the Data Item field to be “ARTRAN Gross Profit Amount.”. Edits description to read “gross profit. Edits the expression to read “ROUND((ARTRAN.EXTPRICE - (ARTRAN.QTYSHP * ARTRAN.COST)) / ARTRAN.EXTPRICE*100,2)”. Enters a unique value as the result field name, “GPAMOUNT”. Edit the first line of the column header to be “Gross Profit”. Edit the second line of the column header to be “Amount”. Click on Save, then Exit and return to the report manager. From now on ARTRAN Gross Profit Amount will appear as a data item available for placing on a report.

Additional Reference

Pro Series Technical Reference Manual, Data Dictionary Section; FoxPro Commands and Functions Manual for FoxPro functions and their syntax.

 


Foxfire! Report Writer for the Pro Series

Frequently Asked Questions

Date

August 3, 2003

Title

RW004 No data found when running a report

 

 

Issue

This message occurs because of the way the user has created the report, or because no data exists.

 

Solution

The user should first remove all filters and attempt to run the report again. If there is data, the problem is in how the filters were set. The user should review his filter conditions.

 

If there are no filters, but the report is run from multiple tables, the user should delete data items from the report so that there are only data items from a single table (same first six characters, e.g. ARMAST). Run the report. If there are still no data found, Foxfire!/Pro Series is not finding any records in the Pro Series data and the user needs to add some records.

 

If there is data with the single table, the user can then start adding back data items. Add from a second table, then run the report. Then add from a third table, and so on, until the user identifies at which point there are no data records.

 

The user is experiencing an outer join issue. Refer to the outer join FAQ.

 

 


Foxfire! Report Writer for the Pro Series

Frequently Asked Questions

Date

August 1, 2003

Title

RW005 How to create a percentage of totals (and not a total of percentages)

 

 

Issue

You have a data item that is a percentage calculation. On the group total line, you see a sum of the percentages. What you want to see is a percentage of the sums.

 

Example: You have added a data item to ARTRAN whose expression is gross profit divided by extended price multiplied by 100. It is call ARTRAN GP%. You create a new report which has the following data items: invoice number, extended price, gross profit amount, and GP%. You have the report sorting and grouping on the invoice number. When you print the report, you see a group footer line after each invoice with the invoice total extended price, invoice total gross profit amount and invoice total GP%. Instead of the total GP% (a sum of the GP% of each invoice line), you want to see the invoice total gross profit amount divided by the invoice total extended price multiplied by 100.

 

Solution

You need to weight the GP% data item by the extended price.

 

Edit data items, and select the GP% item. Click on Edit, then on the Calculations button. The default is Subtotals  Yes. Change this to Subtotals   Other Summary Options. Under Calculate, select Weighted Avg. You will be prompted for the item to weigh: choose the appropriate item (in our example it is extended price). Click on OK. Enter a report variable: it is not important what you call it. Then click on OK and Save the changes to the data item.

 

Now when you print the report you should see the percentage of the sums.

 


Foxfire! Report Writer for the Pro Series

Frequently Asked Questions

Date

August 1, 2003

Title

RW006 How to correct Foxfire!/Pro Series paths after moving the Pro Series to a different directory

 

 

Problem

User moves Pro Series to a different directory and edits sysdata, sycdata, sycpath records to reference new directory, or runs movepro. User can access other Pro Series applications OK, but gets error when going into Foxfire!/Pro Series.

Solution

Exit Foxfire!/Pro Series.

Create a file named fffirst.dat in the \fr directory. It does not matter what is in the file. You can copy a file in the \fr directory, then rename it to fffirst.dat. Foxfire!/ Pro Series checks for the existence of this file when starting up, resets  its paths if it is there, then deletes the file.

Open Foxfire!/ Pro Series.

Comments

You will know you have been successful when Foxfire!/ Pro Series starts updating the paths when it is started up.

 


Foxfire! Report Writer for the Pro Series

Frequently Asked Questions

Date

August 1, 2003

Title

RW007 How to get percentage of total sales for each item sold

 

 

Problem

User needs to create a report, listing each AR invoice and its percent of the total sales for invoices in the report.

Solution

This is a “two-pass” report. The report needs to go through the data once to total sales, then a second time to generate the report.

 

Edit data items, select ARMAST Total Without Tax, and Save As ARMAST Percent Total Sales. Make the expression be 00000000000000.00, and enter totsales as the result field name. Change the column heading to Percent of Total Sales. Save the record.

 

In your report, output, special processing, in the field for AFTER QUERY, EXECUTE type DO \pro71\fr\totsales (change to reflect your system’s path).

 

Add the data item ARMAST Percent of Total Sales to your report.

 

Create a FoxPro program in the fr directory called totsales.prg as follows:

 

LOCAL;

  lc_tempname, ;

   lc_wasalias, ;

   ln_totsales

 

lc_wasalias = ALIAS()

lc_tempname = SYS(3)

 

COPY ALL TO (lc_tempname)

USE (lc_tempname) ALIAS ac_sales

 

SUM ALL invamtn TO ln_totsales

 

REPLACE ALL totsales WITH ROUND(invamtn/ln_totsales*100,2)

SELECT * FROM sales INTO CURSOR &lc_wasalias

USE IN SELECT(“ac_sales”)

DELETE FILE (lc_tempname + ".dbf")

RETURN

 

This technique, using the appropriate fields, will work for other percentage of total amounts.

 


Foxfire! Report Writer for the Pro Series

Frequently Asked Questions

Date

August 1, 2003

Title

RW008 How to include unmatched records in a report (outer join)

 

 

Problem

You want to create a report that includes unmatched records. This requires what is called, in report writing terminology, an ‘outer join.’

 

For example, you define a report using data items from two tables, Customers  and Invoices. When you run the report, it lists the customers in the Customers table where there are one or more matching records in Invoices.  But if a customer has no invoices, the customer is not included in the report. (This output is the result of what is called an inner join.).  For each record in Customers there may be zero, one, or many matching records in Invoices. This is a one-to-many relationship, where Customers is the parent table (the table that “drives” the relationship) and Invoices  is the child table. The inner join matches parent records to child records, and omits records that don’t match.

 

But you want the report to list all customers, whether they have invoices or not. This requires what is called an outer join. Outer joins can be of two types:

 

Parent records with no children — customers with no invoices

                                    — OR—

Child records with no parents — invoices with no customers

 

Foxfire!/Pro Series makes it easy to create outer joins between two tables. With the Filter Builder’s Special Filter options you can choose to include unmatched records in a report.   This outer join feature is available only under the following conditions:

·        The source of the report comes from at most two tables.
Outer join options may not be offered in some reports, even though all the selected data items come from one or two tables. This is because filters, sorts and calculations can be based on data items not actually appearing in the output. If any of these data items depends on a third or fourth table, outer joins will not be offered.
If the report is using only a single table, Foxfire!/ Pro Series allows an outer join with another table so you can test for the existence of the linking data item in that other table. This is a convenient way to create exception reports for orphaned parent and child records.

·        The report must be a detail or label report.
Summary and cross-tab reports, and reports with aggregating expressions in any data item cannot be joined with an Outer Join.

 

If these limitations prevent you from using the Filter Builder’s outer join feature, there are other approaches to creating outer join results in Foxfire!/Pro Series.   The following solutions show you step by step how to create an outer join for a 2-table report and a 3-table report.

Solution

Creating a 2-Table Outer Join Report with a Union:

 

This method solves the problem by breaking it down into two parts:

1. Create one report to list customers with invoices

2.  Create a second report to list customers with no invoices and combine it with the first report.

 

First, let’s create the report that list customers with invoices:

1.      Using the AR Invoices data set, add a new detail report, giving it the name CUST_A, and description “Customers with Invoices.”

2.      Select the following data items:

ARCUST Company Number

ARCUST Company Name

ARMAST Invoice Number

ARMAST Total with Tax

3.      Choose to sort and group by ARCUST Company Number.

 

CUST_A is a straightforward report which gets its information from ARCUST and ARMAST.  However, as explained above, customers with no invoices can't be included this way.  So you need to create a second report (which we will name CUST_B) which will list customers with no invoices.  Then the two reports will be combined (union).

 

To create the second report that lists customers with no invoices:

1.      While in CUST_A, select Save As, and save the report as CUST_B, with a description of “Customers with No Invoices.”

2.      Return to the Report Manager and click on the Dictionary icon at the top of the screen.
In the Data Item Editor you need to create the following calculated data items:

A Filter Only data item. The Filter Only data item is a calculated data item that, when added to the secondary report, limits retrieval of parent records to those with no child records (in this case, customers with no invoices). The Filter Only data item consists of a filter that specifies a nested SQL SELECT statement (called a subquery).  Whenever you include this special data item in a report, Foxfire!/ Pro Series adds this clause to the resulting SQL SELECT statement, limiting retrieval of customers to those with no invoices.

Placeholder data items.  The placeholders are data items you must create with expressions that return blank values of exactly the same  data type and result width as they would have had if they were populated with data.

 

1.      To create the Filter Only data item:

In the Data Item Editor, select Add, and complete the fields as follows:
       Data Item: Customers with No Invoices

Expression: (subquery)

Data Type: Logical

Primary Source File: (ARCUST)

Primary Source Alias: ARCUST

 

2.      Select Filter, and complete the following fields:

Allow Filter: Only

Filter (Where) Expression:

ARCUST.CUSTNO NOT IN (SELECT ARMAST.CUSTNO AS CUSTNOR8 FROM (ARMAST)ARMAST)

 

3.      To create a placeholder data item for a character-type data item:

In the Data Item Editor, select Add, and complete the fields as follows:

 

Data Item: BLANK Invoice Number

Expression: SPACE(10)

Data Type: Character

Result Field Name: XINVNOR8

Primary Source File: (ARCUST)

Primary Source Alias: ARCUST

Column Header: Invoice

                           Number

Output Picture: XXXXXXXXXX

Output Width: 10

 

4.      To create a placeholder data item for a numeric-type data item:

In the Data Item Editor, select Add, and complete the fields as follows:

 

Data Item: BLANK Total with Tax

Expression: 000000000000.00

Data Type: Numeric

Result Field Name: XINVAMT

Primary Source File: (ARCUST)

Primary Source Alias: ARCUST

Column Header: Invoice

                               Total

Output Picture: 999999999999.99

Output Width: 15, 2

 

Return to the Report Manager, and select CUST_B.

 

1.      Select Data Items, and remove from the Selected list the data items ARMAST Invoice Number ARMAST Total with Tax.  From the Available list, select BLANK Invoice Number and BLANK Total with Tax.  Save.


2.      Select Filter.  Select Add, and select the data item Customers with No Invoices.  Save.

3.      So the report CUST_B is created with new data items, the filter-only data item, “Customers with No Invoices” and two “placeholder” data items, BLANK Invoice Number and BLANK Total with Tax.  The next step is to join the two reports.

Before you join, or merge, the results of the two reports, run each report separately to see that it produces the desired results.  Run CUST_A and confirm that it lists customers with invoices.  Then run CUST_B and confirm that it lists customers with no invoices. 

4.      To merge the results of the two reports:

Select the report CUST_A. 

Select Filter.  In the section Merge with Other Query Results (Union), select Run Report, and enter CUST_B as the name of the report to merge with.

 

While this solution requires many steps, the advantage is that it produces results with relatively fast performance.  The downside is that you must assure that any filter conditions (outside of the filter-only data item) are applied to BOTH reports.  Changes to the filter in one of the reports are not automatically reflected in the other.  Note that the Sort and Group settings in the parent report control the results.  The Sort and Group settings in the child report are ignored.

 

Creating a 3-Table Outer Join Report by Editing the SQL:

 

What if your report consists of three (or more) tables instead of two?  This increases the complexity of the outer join and is best approached by editing and saving the SQL.

 

First you must examine the logic of your report.  What unmatched records are to be included?  For our example, we are going to create a inventory report using the tables ICITEM, ICSUPL, and ICIQTY.  The parent table is ICITEM (Inventory Master file), and we want to list all parent records (all items) in ICITEM, whether or not they have child records in ICSUPL (Inventory Supplier file) or ICIQTY (Item Qty at Location file).

 

What are all the possible combinations of matching and not matching among the three tables?  For every item in ICITEM, there may be none, one or many matching records in ICSUPL. For every item in ICITEM, there may be none, one or many matching records in ICIQTY.  You want the report to include an item from the parent table (ICITEM) when there is a matching record in both of the child tables, in either of the child tables, or in neither of the child tables.  In the report’s SQL statement, you need to create a SELECT statement for each possible case:

 

Item is in:                    ICITEM     ICIQTY        ICSUPL

Case 1: records match in                x              x                   x

Case 2: records match in                x              x

Case 3: records match in                x                                   x

Case 4: records match in                x

 

In Case 1, which is really the inner join, the report includes an item when the item is in all three tables.  In Case 2, the report includes an item if it is only in ICITEM and ICIQTY (and not in ICSUPL).  In Case 3, the report includes an item if it is only in ICITEM and ICSUPL (and not in ICIQTY).  In Case 4, the report includes an item if it is found in ICITEM only.

 

After you have identified all the cases for which SELECT statements need to be created, you are ready to begin creating the report.

 

F  Be sure you have a report data set that contains the three tables you need for your report.  If one does not exist, you can edit an existing one to add the necessary table(s), or you can use the Setup Wizard to create one.  Refer to the manual for instructions.

Case 1:

Since the first case is for items that are in all three tables, which Foxfire!/ Pro Series does automatically when you create a report, we will begin by creating a normal, detail report:

 

1.      In the Report Manager, select New.

2.      Select Detail as the report type.

3.      In the Report Editor, Complete the report name and description:
Name: IC3X
Description: Items with Supplier and Serial/Store/Bin Information

4.      In the Data Item Selector, choose the following items:
ICITEM Item Number
ICITEM Standard Cost
ICSUPL Vendor Part Number
ICSUPL Vendor Number
ICIQTY OH Qty

5.  Move to the Output Selector, and select Special Processing Options.  Under Query Generation Options, select Stored SQL, and have Foxfire!/ Pro Series generate the SQL.  It should look like the following statement without the line numbers:

line
no.             SQL SELECT statement
1  SELECT ALL;
2        ICITEM.ITEM                    AS ITEMI7,;
3        ICITEM.STDCOST,;
4        ICSUPL.VPARTNO,;
5        ICSUPL.VENDNO,;
6        ICIQTY.QONHAND;
7  FROM (ICITEM) ICITEM,;
8        (ICSUPL) ICSUPL,;
9        (ICIQTY) ICIQTY;
10  INTO CURSOR IC3X;
11  WHERE ICITEM.ITEM = ICIQTY.ITEM;
12   
AND ICITEM.ITEM = ICSUPL.ITEM

 

Case 2:

Now you need to edit the SQL to add a SELECT statement for case 2, where the ICITEM item has a matching record in ICIQTY but not in ICSUPL. Because the second SELECT statement will be selecting records from ICITEM and ICIQTY only, you must edit the statement to remove selection of ICSUPL items and replace them with blank placeholders (refer to the blank data items created in the example for the 2-table outer join report, above):

1.      Edit line 1 (first line of the first SELECT statement) to change it from SELECT ALL to SELECT DISTINCT.  We will be combining the results of several SELECT statements,which would cause duplication of some records.  By selecting DISTINCT, FoxPro will remove duplicates from the result.

2.      On line 12, add a semi-colon (;) after the last word.

3.      On line 13, after the first SELECT statement, type:
UNION ALL;

4.      On line 14, type:
SELECT
then copy lines 2 through 12 and paste them immediately after SELECT.

5.  Edit the list of data items in the second SELECT statement to replace ICSUPL.VPARTNO with SPACE(15) and replace ICSUPL.VENDNO with SPACE(6).

6.      In the FROM section of the SELECT… FROM statement, remove the line: (ICSUPL) ICSUPL

7.      Replace the concluding WHERE statement with a WHERE clause to include ICITEM items that are not in ICSUPL:
WHERE .T.;
       
AND ICITEM.ITEM = ICIQTY.ITEM;
       
AND ((Icitem.Item);
         NOT IN (SELECT DISTINCT;
                  Icsupl.Item;
                 FROM (ICSUPL) ICSUPL))

8.      At this point, your SQL statement should look like this:
SELECT DISTINCT;
        ICITEM.ITEM                    AS ITEMI7,;
        ICITEM.STDCOST,;
        ICSUPL.VPARTNO,;
        ICSUPL.VENDNO,;
        ICIQTY.QONHAND;
  FROM (ICITEM) ICITEM,;
        (ICSUPL) ICSUPL,;
        (ICIQTY) ICIQTY;
  INTO CURSOR IC3X;
  WHERE ICITEM.ITEM = ICSUPL.ITEM;
   
AND ICITEM.ITEM = ICIQTY.ITEM;
  UNION
ALL;
SELECT ICITEM.ITEM                    AS ITEMI7,;
        ICITEM.STDCOST,;
        SPACE(15),;
        SPACE(6),;
        ICIQTY.QONHAND;
        FROM (ICITEM) ICITEM,;
        (ICIQTY) ICIQTY;
  WHERE .T.;
       
AND ICITEM.ITEM = ICIQTY.ITEM;
       
AND ((Icitem.Item);
        NOT IN (SELECT DISTINCT;
                  Icsupl.Item;
                 FROM (ICSUPL) ICSUPL))

Case 3:

For case 3, the report includes the item if it is only in ICITEM and ICSUPL.  To add a SELECT statement for case 3, repeat the process described for case 2, including:

·        Remove selection of items from ICIQTY, replacing ICIQTY data items with blank placeholders.

·        Add a WHERE clause for items in ICITEM that are not in ICIQTY

When complete, you should have added a the SELECT statement for case 3 that should look like the following:

 

UNION ALL;

SELECT ICITEM.ITEM,;

        ICITEM.STDCOST,;

        ICSUPL.VPARTNO,;

        ICSUPL.VENDNO,;

        000000000.00;

        FROM (ICITEM) ICITEM,;

             (ICSUPL) ICSUPL;

WHERE .T.;

        AND ICITEM.ITEM = ICSUPL.ITEM;

        AND ((Icitem.Item);

         NOT IN (SELECT DISTINCT;

                     ICIQTY.ITEM;

                     FROM (ICIQTY) ICIQTY))

 

Case 4:

For case 4, the report includes an item if it is only in ICITEM.  To add a SELECT statement for case 4, repeat the process described for case 2, including:

·        Remove selection of data items from ICIQTY and ICSUPL, including replacing ICIQTY and ICSUPL data items with blank placeholders

·        Add a WHERE clause for items in ICITEM that are not in ICIQTY and ICSUPL.

When complete, the entire SQL should look like the following:

 

SELECT DISTINCT;

        ICITEM.ITEM                    AS ITEMI7,;

        ICITEM.STDCOST,;

        ICSUPL.VPARTNO,;

        ICSUPL.VENDNO,;

        ICIQTY.QONHAND;

  FROM (ICITEM) ICITEM,;

        (ICSUPL) ICSUPL,;

        (ICIQTY) ICIQTY;

  INTO CURSOR IC3X;

  WHERE ICITEM.ITEM = ICSUPL.ITEM;

    AND ICITEM.ITEM = ICIQTY.ITEM;

  UNION ALL;

SELECT ICITEM.ITEM                    AS ITEMI7,;

        ICITEM.STDCOST,;

        SPACE(15),;

        SPACE(6),;

        ICIQTY.QONHAND;

        FROM (ICITEM) ICITEM,;

        (ICIQTY) ICIQTY;

  WHERE .T.;

        AND ICITEM.ITEM = ICIQTY.ITEM;

        AND ((Icitem.Item);

         NOT IN (SELECT DISTINCT;

                  Icsupl.Item;

                 FROM (ICSUPL) ICSUPL));

  UNION ALL;

SELECT ICITEM.ITEM,;

        ICITEM.STDCOST,;

        ICSUPL.VPARTNO,;

        ICSUPL.VENDNO,;

        000000000.00;

        FROM (ICITEM) ICITEM,;

             (ICSUPL) ICSUPL;

WHERE .T.;

        AND ICITEM.ITEM = ICSUPL.ITEM;

        AND ((Icitem.Item);

         NOT IN (SELECT DISTINCT;

                     ICIQTY.ITEM;

                     FROM (ICIQTY) ICIQTY));

  UNION ALL;

SELECT ICITEM.ITEM,;

        ICITEM.STDCOST,;

        SPACE(15),;

        SPACE(6),;

        000000000.00;

        FROM (ICITEM) ICITEM;

WHERE .T.;

        AND ((Icitem.Item);

         NOT IN (SELECT DISTINCT;

                     ICIQTY.ITEM;

                     FROM (ICIQTY) ICIQTY));

        AND ((Icitem.Item);

         NOT IN (SELECT DISTINCT;

                     ICSUPL.ITEM;

                     FROM (ICSUPL) ICSUPL))

 

F  When editing the SQL, be careful to to add commas, semi-colons, and parentheses as appropriate.  Failure to add them appropriately will result in an error message when you run the report.

F  Do not use BOTH current and history files when running a report that uses a saved SQL.

 


Foxfire! Report Writer for the Pro Series

Frequently Asked Questions

Date

August 1, 2003

Title

RW009 Sales Order reports printing deleted lines

 

 

Problem

Items were deleted from a sales order but they continue to appear on the printed reports.

Solution

In the Pro Series sotran tables, the item Status is flagged with an X but is not deleted when a user deletes a line from a sales order. The user must add the filter ‘SOTRAN Status of Sales Order Is Not Like X’ to the report. 

 


Foxfire! Report Writer for the Pro Series

Frequently Asked Questions

Date

August 1, 2003

Title

RW010 Calling Foxfire!/Pro Series from another program

 

 

Problem

How to call a Foxfire!/ Pro Series report from the Pro Series custom menu pad

Solution

Use the following steps:

 

1.      In System Manager choose Maintain from the Menu bar.

2.      Then select Setup, System Installation, which will bring up the System Installation window.

3.      From the System Installation Menu Options choose Custom.

4.      From the Custom Menu Options choose Edit.

5.      Under Menu Text use a meaningful name or description.

6.      Under the Command Line add a command.

Example:

DO  \pro71\fr\rw.fxp WITH “AR Customer”,“.RQST”,“CUST”,“99”

In the above example, “AR Customer” is the data set name, “CUST” is the report name, and the company to report on is 99.

8.  Save.

 

Comments

If the report doesn’t run, it is possible that the data set or report name is typed incorrectly.

 


Foxfire! Report Writer for the Pro Series

Frequently Asked Questions

Date

August 1, 2003

Title

RW011 Filtering for items which were not sold to a customer

 

 

Problem

Need a report that shows which items were not sold to a specific customer.  For this example, the customer number is ABC001.

Solution

Create a Filter Only Data Item in the Data Item Editor.  This Filter Only Data Item will then be used in the Filter Builder screen of the report.

7.      In the Data Item Editor Choose a similar data item and choose Save As.  In this situation, select ICITEM Item Number.

8.      Change the Data Item name so it is unique but meaningful to you.  For this example we’ll call it ICITEM Number Not ABC001.

9.      Data Type needs to be Logical.

10.  It doesn’t matter what is in the Column Header, Output Picture, Output Width and Sample Data.

11.  Under Special Features Choose Filter.

12.  Check Only under Allow Filter.

13.  The Filter (Where) Expression will look like the following example.

Example:

Icitem.item not in (select artran.item from (artran) artran where artran.custno=“ABC001”)

14.  Choose OK.  Now the Expression in the Report Editor will be (subquery).

15.  While in the Report Editor screen choose Filter and pick the new data item that was just created. Choose any other filter conditions desired and then OK.

16.  To have an invoice date range change the Filter (Where) Expression as follows:

Example:

Icitem.item not in (select artran.item from (artran) artran where artran.custno=“ABC001” and BETWEEN(CTOD(artran.invdte),{06/01/97},{06/30/97}));

17.  To change the Customer Number and Invoice date at “runtime” it will have to be done in the SQL statement.

a)      While at the Report Manager screen go to the top menu bar and click on the Foxfire!/Pro Series menu option and select Show SQL.

b)      Press the F6 key to edit the SQL statement.

c)      Edit the Custno. and/or the dates.

d)      Close the SQL window by hitting Ctrl+w, Ctrl+F4 or by going to the upper left hand corner of the window, click once, and choose Close.

e)      Then choose save when the window pops up.

f)        Now run the report as usual.

If there is an error with the SQL that was just made, go back to the SQL window and press F7 to clear the last changes made and return to the previously saved SQL statement.  Note that a misplaced comma will cause an error, so you must edit carefully.

 


Foxfire! Report Writer for the Pro Series

Frequently Asked Questions

Date

August 1, 2003

Title

RW012 Creating year specific GL YTD amount data item

 

 

Problem

Need a report that shows GL YTD Amount for two or more different years.

Solution

Create Data Items in the Data Item Editor for each year. In this example the GLBALS table is opened twice and it is given a different alias each time.  In this case GLBALS02 and GLBALS03.

 

ADD NEW TABLES TO DATA SET

1.      GLBALS02 and GLBALS03 need to be added to the Data Set table.

a)      From the Report Manager screen select Foxfire!/SBT from the top menu bar.  Then choose Utilities, then Edit Data Set.

b)      Choose Edit, then from Advanced Options select Files.

c)      In the Files screen, at about the middle of the screen,  you will see INLIST(Rpt_ditem.rid_alias1,“GLACNT”,“GLBALS”,…).  To this list “GLBALS02” and “GLBALS03” need to be added. 

 

CREATE FILE RELATIONSHIPS

1.      To create new relationships choose Utilities from the menu bar while in the Report Manager screen. 

2.      Choose Edit File Relationship Information.

3.      In the File Relationship (Join) Editor choose GLBALS and Save As.

4.      For File1:

a)      Alias is GLBALS02.

b)      Descriptive name is GL Account Balances Records.

c)      File name is (GLBALS).

18.  For File2:

a)      Alias is GLACNT.

b)      Descriptive name is GL Accounts.

c)      File name is (GLACNT).

7.      The Relationship Expression in this example will be:

GLBALS02.glacnt=glacnt AND LEFT(GLBALS02.YEARPRD,4)=“2002”

8.      Repeat #3 and #4 for GLBALS03.

 

CREATE NEW DATA ITEMS

1.      In the Data Item Editor Choose a GL YTD Amount data item and choose Save As.

2.      Change the Data Item name so it is unique but meaningful to you.  For this example we’ll call it GLBALS02 Year and Period.

3.      Expression should look like the following example.

4.      Example:

5.      RIGHT(GLBALS02.YEARPRD,2)

6.      The Description for our example is Yearprd.

7.      Data Type needs to be Character.

8.      The Result Field Name in our case will be YEARPRD02.

9.      Under Primary Source:  File remains the same, in our example (GLBALS).  Alias is changed to GLBALS02.

10.  For our example Column Header is MM, Output Picture is XX, Output Width = 2 and the Sample Data is 02.

11.  Then choose Save.  Repeat the above steps for the year 2003 or any year desired.

12.  Next we need to create a YTD for 2002 Data Item.

13.  For this we will choose the Data Item that was just created and Save AS.

14.  Change the Data Item name so it is unique but meaningful to you.  For this example we’ll call it GLBALS02 YTD 2002.

15.  The Description in this case is Ytdcr.

16.  Expression should look like the following example.

17.  Example:

18.  GLBALS02.YTDCR-GLBALS02.FYBGBAL

19.  Data Type needs to be Numeric.

20.  The Result Field Name in our case will be GLYTD02.

21.  Under Primary Source:  File remains the same, in our example (GLBALS).  Alias is changed to GLBALS02.

22.  For our example Column Header is YTD 02 Amount, Output Picture is 9999999.99, Output Width = 10, 2 and the Sample Data is 337400.94.

23.  Then choose Save.  Repeat the above steps for the year 1903 or any year desired.

24.  Return to the Report Editor screen. Choose the new Data Items desired. In our example they would be GLBALS02 YTD 2002 and GLBALS03 YTD 2003.

25.  While in the Report Editor screen choose Filter and pick the new data item GLBALS02 Year and Period that was just created. Filter it as IS LIKE and then the month you desire or ask at runtime.  Repeat for GLBALS03 Year and Period.

26.  Choose any other filter conditions desired and then OK.

27.  Sort and Group as desired.

28.  At this point it is recommended that a report header be prompted at run time for you report. To do this, see the following steps.

a)      At the Report Editor screen, choose Output.

b)      Next choose Options listed under Output Type.

c)      Under Formatting check the box for always prompt for Header.

d)      OK

e)      OK

Then choose Save.

 


Foxfire! Report Writer for the Pro Series

Frequently Asked Questions

Date issued

August 1, 2003

Title

RW013 How to run a batch of reports from the Pro Series custom menu pad

 

 

Problem

User wants to have a batch of reports run at night, unattended.

Solution

Use the following steps:

 

1. Create a batch of reports. In the Report Manager screen, select Batch Builder from the menu, choose which reports are to be in the batch, then save the batch to a file, for example weekly.dbf

 

2.      In System Manager choose Maintain from the Menu bar.

3.      Then select Setup, System Installation, which will bring up the System Installation window.

4.      From the System Installation Menu Options choose Custom.

5.      From the Custom Menu Options choose Edit.

6.      Under Menu Text use a meaningful name or description.

7.      Under the Command Line add a command.

8.      Example: DO f:\pro30\fr\rw WITH “\pro71\fr\out\weekly",".BATCH", "",      "01", "CURRENT","KHIG"

9.      Save.

 

Comments

Only reports from the same data set can be included in a batch.

You may edit the record(s) in the batch.dbf table to store ask at runtime values for report(s) with one or more ask at runtime filters.

 

Store filter values in a array named: AskValues[] with filter values for all ask at runtime filter values in order. Use "*IGNORE*" to ignore optional ask at runtime values. Use *DEFAULT* to accept the default for lines with a default.

 

EXAMPLE: A request has 10 filter lines with required ask at runtime values

on lines 2 & 4 and optional ask at runtime values on lines 6 & 8. To set filter lines 2,4 & 8 and treat line 6 as ignored, enter the commands below at the command window:

 

  DIMENSION AskValues[4]

  AskValues[1] = <filter line 2 value>

  AskValues[2] = <filter line 4 value>

  AskValues[3] = "*IGNORE*"

  AskValues[4] = <filter line 8 value>

  SAVE TO MEMO AskAtRun ALL LIKE AskValues

 

If any ask at runtime values are not specified or have mismatched data types, the user will be prompted for the ask at runtime value.

 


Technical Support — Frequently Asked Questions

Date

August 1, 2003

Title

RW014 How to run reports based on ship to addresses

 

 

Problem

User wants to run reports based on ship to addresses rather than the regular customer address.

Solution

Resellers frequently encounter the issue of needing a report with customer, invoice and invoice shipping information. This is tricky because of the need for an outer join, since araddr (and soaddr) have records only for invoices (and sales orders) with alternate ship to addresses.

 

A reseller can simply instruct the end-user to always use the alternate ship to address, even when shipping directly to the customer. But what if the end-user forgets?

 

One solution is to create a short program to check araddr (or soaddr) for records for each invoice (or sales order) and add records that are missing. This program can be run before the report query.  The steps are below.

 

1.       Create and compile a program using FoxPro. For our example, we will call it shipto.prg and we will save it in the s:\pro71\fr directory. The program is:

 

PRIVATE lc_oldAlias

 

Lc_oldAlias = ALIAS()

 

SELECT armast.custno, armast.invno, ;

  arcust.company, arcust.address1, ;

  arcust.address2, arcust.city, arcust.state, ;

  arcust.zip, armast.custno AS cshipno ;

  FROM s:\pro71\ardata\armast01 armast,;

  s:\pro71\ardata\arcust01 arcust;

  INTO CURSOR shipto;

  WHERE armast.custno = arcust.custno AND ;

  armast.custno + invno NOT IN (SELECT custno + invno AS cinv;

  FROM s:\pro71\ardata\araddr01 araddr)

 

IF _TALLY > 0

  SELECT araddr

  IF FLOCK()

    APPEND FROM shipto

  ENDIF

ENDIF

 

USE IN SELECT(“armast”)

USE IN SELECT(“araddr”)

 

SELECT arymst.custno, arymst.invno, ;

  arcust.company, arcust.address1, ;

  arcust.address2, arcust.city, arcust.state, ;

  arcust.zip, arymst.custno AS cshipno ;

  FROM s:\pro71\ardata\arymst01 arymst, ;

    s:\pro71\ardata\arcust01 arcust;

  INTO CURSOR shipto;

  WHERE arymst.custno = arcust.custno AND ;

   invno NOT IN (SELECT invno ;

  FROM s:\pro71\ardata\aryadr01 aryadr)

 

IF _TALLY > 0

SELECT aryadr

  IF FLOCK()

    APPEND FROM shipto

  ENDIF

ENDIF

 

USE IN SELECT(“arymst”)

USE IN SELECT(“aryadr”)

USE IN SELECT(“arcust”)

USE IN SELECT(“shipto”)

 

SELECT (lc_oldAlias)

RETURN

 

Note: you can substitute somast/soymst and soaddr/soyadr for sales orders.

 

2.  Create or edit the report with ship to information. In the output screen, click Special Processing Options. In the before query execute, enter DO s:\pro71\fr\shipto.fxp.

 

3. While editing the report, go to the filter builder screen and add a filter for armast invoice status NOT like "V" to filter out void records. The above program does not handle void records correctly because of a second occurrence of the invoice number in armast.

 

4. Run the report.

 

Comments

The advantage to this approach is that it is simple and it works. The disadvantage is that you must have FoxPro to compile the .prg, it requires a knowledgeable person to set it up, and, unlike any other function of Foxfire!/Pro Series, it causes the Pro Series data to be changed.

 


Foxfire! Report Writer for the Pro Series

Frequently Asked Questions

Date

August 1, 2003

Title

RW015 How to create an aging report in Foxfire!/Pro Series

 

 

Problem

User wants to create aging reports in Foxfire! rather than using the report provided in the Pro Series.

Solution

The easiest way to do this is to create a short program to define the aging parameters desired for the report.  The steps are below.

 

2.       Create and compile a program using FoxPro. For our example, we will call it arage.prg and we will save it in the s:\pro71\fr directory. The program is:

 

PARAMETERS pd_invdte, pn_pnet

 

LOCAL ll_return

ll_return = "00"

 

DO CASE

  CASE gd_sdate - (pd_invdte + pn_pnet)  > 90

    ll_return = "90"

  CASE BETWEEN(gd_sdate - (pd_invdte + pn_pnet),61,89)

    ll_return = "60"

  CASE BETWEEN(gd_sdate - (pd_invdte + pn_pnet),31,59)

    ll_return = "30"

  OTHERWISE

    ll_return = "00"

ENDCASE

RETURN ll_return

 

3.       A new Data Item will need to be created using the Data Item Editor.

a)      Choose edit a Data Item from ARMAST that is a character Data Type and choose Save As.

b)      Change the Data Item name to something meaningful to you, in this case we choose ARMAST Aging bucket.

c)      The Description in this case is blank.

d)      Change the Expression to be:

arage(armast.invdte,armast.pnet)  

e)       Change the Result Field Name to ARAGEB.

f)        Change to Column Header to something appropriate for your purposes.

g)       Edit the Output Picture to: XX

h)       Change the Output Width to: 2

i)         Save.

3.       For our report we wanted to show the due date for the invoice so we created another Data Item to handle this.

a)      Choose edit a Data Item from ARMAST that is a date Data Type and choose Save As.

b)      Change the Data Item name to something meaningful to you, in this case we choose ARMAST Due Date (D).

c)      Description should be: Invdte

d)      Change the Expression to be:

armast.invdte + armast.pnet 

e)       Change the Result Field Name to DUEDAY.

f)        Change to Column Header to something appropriate for your purposes.

g)       Edit the Output Picture to: XXXXXXXXXX

h)       Change the Output Width to: 10

i)         Save.

4.       Now create your report including the new data items.

 

 

 


Foxfire! Report Writer for the Pro Series

Frequently Asked Questions

Date

August 1, 2003

Title

RW016 Foxfire!/Pro Series report is showing multiple records of AP invoices

 

 

Problem

User creates a report in Foxfire!/Pro Series AP Checking or AP Invoices and the report shows multiple listings for some invoices.

Solution

Since the Pro Series flags the invoices as void and then allows the user to re-enter that invoice again under the same invoice number, there are multiple records with that invoice number in APMAST and APDIST.  There are the records for the voided invoice and for the corrected version of that invoice.

 

To prevent the appearance of the multiple listings on the report, filters need to be added.

 

1.       In the Report Editor screen click on the Filter button.

2.       Add APMAST Invoice Status Is Not Like V.

3.       Add APDIST Inv. Status ID Is Not Like V.

4.       Choose OK.

 

Now the report should show the invoices without duplications.

 


Foxfire! Report Writer for the Pro Series

Frequently Asked Questions

Date

August 1, 2003

Title

RW017 How to create a weekdays data item

 

 

Problem

A reseller was trying to create a data item that would indicate the number of week days it took to ship an order.  All orders and shipments will be a week day date.  But if the order was taken on Friday and shipped on the following Monday, that is a one day turn-around, not 3 days.  The reseller tried using an expression, but was not producing the desired results.

Solution

This task is complicated enough that it requires the use of a UDF (user-defined function).  To create the Weekdays data item:

 

1.       Create a FoxPro program called weekdays.prg (below), and place in the \fr directory. 

2.       *-- weekdays.prg

*--used to determine the number of weekdays between two days

*-- does not allow for holidays

*--requires two parameters, begin date and end date

*--if parameters are character type, this will convert them to date type

*--example: WEEKDAYS (armast.invdte, armast.ordate)

*--will return number of weekdays to ship and invoice an order

PARAMETERS ld_bdate, ld_edate

IF TYPE(“ld_bdate”) =”C”

ld_bdate = CTOD(ld_bdate)

ENDIF

IF TYPE(“ld_edate”) =”C”

ld_edate = CTOD(ld_edate)

ENDIF

ln_bdow = DOW (ld_bdate)

ln_edow = DOW(ld_edate)

DO CASE

CASE ln_bdow = 7

ld_bdate = ld_bdate + 2

CASE ln_bdow = 1

ld_bdate = ld_bdate + 1

ENDCASE

DO CASE

CASE ln_edow = 7

ld_edate = ld_edate - 1

CASE ln_edow = 1

ld_edate = ld_edate – 2

ENDCASE

ln_exdays = MOD(ld-edate – ld_bdate,7)

ln-numdaysINT((ld-edateld_bdate)/7)*5

ln_numdays = ln_numdays = ln_exdays

RETURN(ln-numdays)

 

3.      In the Data Item Editor, create the data item.  In the Expression field, use WEEKDAYS followed by two parameters enclosed in parentheses and separated by a comma: the begin date and the end date. For example:

WEEKDAYS(armast.invdte,armast.ordate)

This example will return the number of weekdays to ship and invoice an order.

 

 


Foxfire! Report Writer for the Pro Series

Frequently Asked Questions

Date

August 1, 2003

Title

RW018 How to avoid asterisks as a result for a calculated data item that uses IIF in the expression

 

 

Problem

The result of a newly created data item, which is using an IIF statement, is ***.** on your report.

Solution

When a data item called with an IIF statement place holding zeros for the data items output width must be included in the statement.

 

Examples for a data item with an output width and picture width of 15,2

4.       Example of an incorrect IIF statement:

IIF(sotran.rqdate>=sotran.shipdate,ROUND(sotran.qtyshp*sotran.price,2),0)

 

5.      Example of a correct IIF statement:

IIF(sotran.rqdate>=sotran.shipdate,ROUND(sotran.qtyshp*sotran.price,2),000000000000.00)

 

Comments

When FoxPro executes an SQL command and creates a cursor, it sizes the columns based on the first fields.  If the first record is 0, the column is then too small for subsequent values.  You should do this with all IIF statements.

 


Foxfire! Report Writer for the Pro Series

Frequently Asked Questions

Date

August 1, 2003

Title

RW019 How set relationships for TIW data table SOHEADER in Foxfire!/Pro Series

 

 

Problem

Setting up relationships for TIW data table SOHEADER.

Solution

To set up the relationship in the Edit File Relationships from the Utilities menu type in the following expression:

 

PADL(ALLTRIM(SOHEADER.SALESNO),10) + SOHEADER.PARTNO =

SOTRAN.SONO + SOTRAN.ITEM

 

 


Foxfire! Report Writer for the Pro Series

Frequently Asked Questions

Date

August 1, 2003

Title

RW020 Fonts not found where expected error

 

 

Problem

After installing Foxfire! you receive the error “Fonts not found where expected” when entering Foxfire!/Pro Series.

Solution

There are three font files that need to be installed into \Windows\Fonts. The files will be found in the \FR directory and are: foxfont.fon, ffdix25a.fot and ffdix25a.ttf. Copy the three files into your \Windows\Fonts directory, then go to the Windows Control Panel (Start | Settings | Control Panel) and select Fonts.  Select File | Install New Font, and add FoxFont and Dixon Mono Thin.

 

 

 


Foxfire! Report Writer for the Pro Series

Frequently Asked Questions

Date issued

August 1, 2003

 

Title

RW021 Allows user use conditional variable in report

 

 

 

Problem

User wants to run a report where a field used in the select statement can be modified to allow the user alter the way the report selects information. In this instance, the enduser wants to run a PTD column where the user selects the month (period) to be used.

 

Solution

 

 

 

 

 

 

 

 

 

LN_REPTMONTH is the numerical variable that stores the month(number) the user entered; this is compared to the month from artran.invdte

 

 

 

 

When the report is run a program (in this instance getmonth.prg) is called (using the g_getfld routine) it prompts for a month to be entered (01-12). This is compared, in the FoxFire! data dictionary, to the month in artran.invdte. Matches are made in the select statement, this line

 

SUM(IIF(ln_reptmonth = MONTH(artran.invdte), artran.extprice, 0000000000.00))

 

is entered in the FF data dictionary.  For this example, use PRCURNO as the Result Field Name in the data dictionary.

 

 

The enduser must do a Save As on a similar data item and edit it to match the underlined area above.

 

Use this program, which should be in your \fr directory:

 

*-- getmonth.prg

RELEASE ln_reptmonth

PUBLIC ln_reptmonth

ln_reptmonth = MONTH(gd_sdate)

 

= g_getfld("ln_reptmonth","99",".T.",.T.,.T.,"Enter month")

RETURN

 

Insert the line

      DO getmonth.fxp

 into the Execute Before Query, in the Special Processing/Options/Output.

 


Date issued

October 16, 2003

 

Title

RW022 Adding custom current history tables

 

 

Problem

A pair of custom tables, current and history, have been added. How does one direct Foxfire! to use the appropriate table depending upon the current/history/both setting?

 

Solution

 

 

 

 

 

 

 

 

 

 

 

 

 

 There is a metadata table, frdata\frtabl which Foxfire!/ACCPAC uses to “know” about the current table and its corresponding history table. Add a record to frtabl. The field tablid should contain the name of the current table, htablid should contain the name of the corresponding history file.

 

The third field, tiw, is used to indicate the table is associated with the TIW application and so the company number is not used as a suffix on the table name. Ignore this, or set it to .f. unless it the table the preceding is true.

 

If you add a custom table that has no corresponding history table, adding a record to the frdata\frtabl is optional.

 

 

 

Date issued

December 15, 2003

 

Title

RW023 Incorrect page numbers for groups

 

 

Problem

On a report with grouping, in which the formatting option for reset page number when group changes has been chosen, and the first group has data spanning 3 pages, the page numbers are 1, 1, 2 instead of 1, 2, 3

Solution

 

 

 

 

 

 

 

 

 

 

 

 

 

 This is a VFP bug. The page number is set by the VFP system variable _pageno. If the title band is removed, the pagination is correct.

 

A workaround is to not reset page numbers on group breaks.

 

Another workaround is to edit the layout, drag the fields in the header band to the page header band, and remove the header band by click Report, Title/Summary, and de-selecting the title band.

 

 

Date issued

December 15, 2003

 

Title

RW024 Summary reports and aggregating operator

 

 

Problem

User selects summary report and does not choose an aggregating operator with numeric data items, then receives unexpected results.

Solution

 

 

 

 

 

 

 

 

 

 

 

 

 

This is the single most common mistake with summary reports.

 

A summary report generates a query which will have a result set of one row per unique set of non-aggregating values. For an example, a report on invoice number, customer number, invoice date will result in a query like:

 

SELECT invno, custno, invdte ;

GROUP BY invno, custno, invdte

 

Lets add a numeric (currency) data item, artran.extprice. You will get:

 

SELECT invno, custno, invdte, extprice ;

GROUP BY invno, custno, invdte, extprice

 

which gives you one row for each unique line item price. That is probably not what you want.

 

You probably want to aggregate the price. You must select SUM (or some other aggregrating operator like average). Then you get something like:

 

SELECT invno, custno, invdte, SUM(extprice) AS sm_extpric ;

GROUP BY invno, custno, invdte

 

Remember on a summary reports as a general rule, choose an aggregating operator for numeric (currency) data items.

 

 

 

Date issued

December 16, 2003

 

Title

RW025 Reindex outside the application

 

 

Problem

Cannot access the application because of bad or deleted indexes

Solution

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Change to the \fr directory. Run ffreindex.fxp.

 

 

 

 

Date issued

December 17, 2003

 

Title

RW026 Sort on compid

 

 

Problem

Data from multiple companies is in the report and user needs it sorted by compid

Solution

 

 

 

 

 

 

 

 

 

 

 

 

 

The field compid is added to the result set for all queries, except cross-tabs. If you need the company in a cross-tab type of report, try a summary report output to Excel, and then use Excel’s pivot table wizard.

 

If you want to see the company which is the source of the data, edit the report layout and add a field whose expression is compid.

 

If you want to sort on the company, add a data item to the data dictionary whose expression is SPACE(2) and whose result field name is compid. See the example below. This is easiest accomplished by selecting a character data item from a primary table, clicking on Save As, then editing the fields.

 

 

Add compid to your report as a data item, and add it as a sort field.

 

 

 

Date issued

April 21, 2004

 

Title

RW027 For SQL Server how to remove the time on datetime fields

 

 

Problem

With SQL Server, the date fields all have the time on there and the user does not want to see it

Solution

 

 

 

 

 

 

 

 

 

 

 

 

In VFP, use frdata\frpritem and type the following command

 

REPLACE ALL rdi_frxexp WITH "TTOD(" + ALLTRIM(rdi_fldnam) + ")" FOR rdi_fldtyp = "D"

 

 

The time will continue to be in the result set but not appear on the output report.

 

Alternatively, the user can edit a date item in the data dictionary, click on the Advanced button, and edit the layout expression by inserting TTOD( in front of the result field name and ) after it.

 

 

Date issued

February 9, 2004

 

Title

RW028 For SQL Server what to use instead of IIF

 

 

Problem

The user wants an AR aging report, so it is necessary to creat a data item with a conditional expression but IIF doesn’t work with SQL Server

Solution

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Use the Case statement.

 

Syntax example from the SQL Server help file:

SELECT
   CASE type
      WHEN 'popular_comp' THEN 'Popular Computing'
      WHEN 'mod_cook' THEN 'Modern Cooking'
      WHEN 'business' THEN 'Business'
      WHEN 'psychology' THEN 'Psychology'
      WHEN 'trad_cook' THEN 'Traditional Cooking'
      ELSE 'Not yet categorized'
   END AS Category,
CONVERT(varchar(30), title) AS "Shortened Title",
price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY 1

 

Syntax example provided by Colleen Lyke:

CASE WHEN ARMAST.INVDTE<getdate()-30 AND ARMAST.INVDTE>=getdate()-45 THEN ROUND(ARMAST.BBAL,2) ELSE 000000000.00 END

 

Note that it is necessary to use getdate() instead of DATE() or gd_sdate.

 

 

Date issued

June 5, 2007

 

Title

RW029 Cannot activate ACTBAR error

 

 

Problem

When you first start up Foxfire!, it displays a message that says Cannot Activate Bar or it may have a problem even just starting up.

Solution

 

 

 

 

 

 

1. Log in as Administrator.
2. Start REGEDIT (Choose Run from the Start menu and enter REGEDIT)
3. Find the key HKEY_CLASSES_ROOT\Licenses.
4. Right-click on the key and choose Permissions.
5. Set the permissions for Users to Full Control. (I'm not sure why Read is selected but Write is not but Full Control was all that was available on my end - if you can just set it to Read then you may be able to get by with this).
6. Close the Registry.
7. Re-login as a regular user.