Problem with unwanted records

[agorjest]

Hello,
I'm using CR2008, SQL ODBC data source with 2 data tables (workorder, materials transactions), linked by a field called wonum (workorder number).
I'm trying to update a report that lists all parts & materials purchased for each workorder. Here is an example of how it displays:

GH1: Site
GH2: Manager
GH3: Workorder # 12345
GH4: Month: August 2009
Details:
Invoice #/Description Cost Invoice Date
2588 Connectors $12.49 8/26/09
2322 Five Inch Cables $32.87 8/13/09
2588 (Deleted) Connectors -$12.49 8/26/09

Total Costs for August 2009: $32.87

Note: Invoice #/Description is a single string field entered by the user, so I can't group the 2 transactions based on their invoice #2588, because their descriptions are also part of the same field and are slightly different for each one.

As you can see, the data entry application does not allow materials transactions to be deleted once saved, so if there is a data entry mistake made, you have to minus out the cost of the original entry on a separate transaction.

The problem for reporting purposes, there is no exact identifier in the data table that links one transaction to another. The invoice dates are the same, but there may be unrelated transactions with the same invoice date. Part of the description is the same, but usually the minused out transaction has the word "(Deleted)" contained in the description.

So if I do not want transactions that are mistakes (such as the 2 "connectors" transactions in the above example) to appear on the report, I can't just group on a field that links the 2 transactions I want to get rid of and suppress the group, since there aren't any true identifying fields that link them together.

Any ideas on how I could go about eliminating these kind of "mistake" transactions from the report?
I'd appreciate any suggestions. Thanks!

 

[lbass]

Why can't you group on a formula like this:

//{@inv#}:
left({table.field},instr({table.field}," ")-1)

If the inv# is always four digits, you could just use:

left({table.field},4)

Then you could use group selection (report->selection formula->GROUP) to eliminate groups where the amounts total zero, as in:

sum({table.cost},{@inv#}) <> 0

Then you would have to use running totals to sum across groups, since non-group selected records would contribute to the more usual inserted summaries.

-LB

 

[agorjest]

Hi LB,
Thanks for your ideas.
Your first suggestion,
creating a formula field left({table.field},instr({table.field}," ")-1).
This worked great with the majority of the records where the materials description field is formatted as "invoice # (space) description".
However, there are many records where the data in that field doesn't follow that exact format. Some examples of records that don't fit that format:

Invoice #/ Description Cost Inv.Date
(Deleted) 22446-00 Field Tech Svc -$200 8/2/09
K. Jones membership dues $30 8/1/09
1809594 $167 8/9/09
Vehicle depreciation $94 8/1/09

As you can see, some of the records have just an invoice # or no invoice #, in which case the left/instr formula wouldn't work.
Any suggestions on how I could group mistake entries for these records? Would I have to create a conditional formula for each situation?

Thanks for your help...

 

[lbass]

First you need a better managed database. Anyway, I can't tell from your last examples how you would tell what records went together so can't offer suggestions.

-LB

 

[agorjest]

You are right, it's not the best setup as far as the database and the data entry application. But it's what I have to work with at the moment.
I'll try to give you better examples:

Group: Workorder # 55111
Details:
Invoice #/ Description Cost Inv.Date
22446-00 Field Tech Svc $200 8/2/09
(Deleted) 22446-00 Field Tech Svc -$200 8/2/09
22447-01 Six foot cables $33 8/2/09
22449-01 Crimper tools $49 8/3/09


Group: Workorder # 56001
Details:
Invoice #/ Description Cost Inv.Date
K. Jones membership dues $30 8/1/09
12468-001 Misc Office supplies $42 8/26/09
(Deleted) K. Jones membership dues -$30 8/1/09

Group: Workorder # 56113
Details:
Invoice #/ Description Cost Inv.Date
1809594 $167 8/9/09
1809594 (Deleted) -$167 8/9/09
Vehicle depreciation $94 8/1/09
53324 Vehicle Insurance $149 8/15/09
(Deleted) Vehicle depreciation -$94 8/1/09

The invoice/description column is one string field, so depending on who entered the data there can be a variety of ways that the invoice numbers and descriptions are displayed.
What I want to do is eliminate records that meet all of the following criteria:
-Have the same invoice number
-If no invoice #, similar descriptions
-Have the same invoice dates
-When summed together, Costs add up to $0

Hope I described it a little better. Appreciate any ideas...

 

[lbass]

Create a formula like this:

stringvar x := replace(({table.field},"(Deleted)","");
stringvar array y := split(x," ");
numbervar i;
numbervar j := ubound;
stringvar z := "";
for i := 1 to j do(
if isnumeric(replace(y,"-","")) then
z := y;
);
if z = "" then
z := x;
trim(z);

Insert a group on this formula and then go to report->selection formula->GROUP and enter:

sum({table.amt},{@group}) <> 0 or
minimum({table.date},{@group}) <> maximum({table.date},{@group})

I'm not sure this will address all possibilities, but it might get you a little closer.

-LB