avoid duplicate records

[Drus]

I have created an accounts receivable aging report based on a column of data. I have established the aging categories by using an IF THEN ELSE formula in like manner...

if {@InvDueDate}-{AR4_OpenInvoice.InvoiceDueDate}>119 then {AR4_OpenInvoice.Balance} else 0

It is [randomly] returning two records. In other words, for invoice A, it is returning the right answer twice - one below the other. Any ideas on how to eliminate the duplication? There seems to be no rhyme or reason.

Thanks,

Andrew
Andrew Hagenbach
OPUS SYSTEMS, INC.
916.503.3173
ahagenbach@opus-sys.com

 

[synapsevampire]

Sounds like a join issue, if the relationship is one to one, use an =, not a left outer.

Or you may have duplicates in the table if it's just a single table, in which case, select Database->Select Distinct (if using 8.5).

Let's hear if that doesn't resolve.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Drus]

Thanks for the reply. I am not sure I understand your solution. Maybe I can clarify. There are multiple tables linked together in this file. My question to you is...
1. What do you mean use an =, not a left outer? Where?
2. When choosing "Select Distinct" I now get an error message of "Database error .dll". I'll go back to my saved copy for now.

-Andrew Andrew Hagenbach
OPUS SYSTEMS, INC.
916.503.3173
ahagenbach@opus-sys.com

 

[synapsevampire]

Check for the type of *link* (join) being used between the tables.

Selecting distinct from the menu should NOT produce a database error, this is ominous.

Please copy the SQL and record selection from the report and paste it here.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Drus]

Here is the record selection ...

{AR4_OpenInvoice.Balance} <> 0.00 and
{AR6_TransPaymentHistory.TransDate}<={SY0_CompanyParameters.ARModuleDate} Andrew Hagenbach
OPUS SYSTEMS, INC.
916.503.3173
ahagenbach@opus-sys.com

 

[Ngolem]

show us how your tables are linked

eg.


table1 table2

field1====>field3
field2

give the field names and if the links are equal joins or not....

Also give us the structure of your report

eq

Group1 (Table1.field1)
Group2 (@formula} give us the complete formula here
dteails
footer2
footer1

the more detail you give the easier it is to tel you what is wrong Jim Broadbent

 

[synapsevampire]

You can get the SQL statement by selecting:

Database->Show SQL Query

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com