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