Unique Record selection

[beauman]

I am writting in 8.5 against a progress data base. My main report totals CashReceipts based on posting date. Report is grouped on CustomerID.
i.e.

123456(CustomerID)

ReferenceID CashReceived ItemType
9001 500 Inv
9025 50 CAdj
9035 10 WriteOff
440

This reports tells me the total amount the customer has paid (440).
The referenceID field is representative of transactions that have taken place against a jobs. For instance ReferenceID 9001 and 9035 belong to a job with a number 23541, whereas ReferenceID 9025 belongs to job 55555.

The job information is in another table. (Subreport)

ReferenceID JobNo Job Description
9001 23541 Cards
9035 23541 Cards
9025 55555 Paper

The only link to the job info is the ReferenceID. If I select from the job table with reference ID I get multiple hits on the customer. How can I select the job info uniquely?

 

[Ngolem]

I see your problem....You need another group based on JobNo but that is in the subreport so you cannot group on it.

Is it not possible to link the table in the main report and eliminate the subreport....you show no details of the tables involved so it is hard to tell

To keep the structure of your report the same as it is right now you can produce your report but you would do it in the footer of the Customer ID Group and would involve stroing the data in arrays for later printing which is doable but complicated.

The feasibilty of this depends a lot on how many Job nos/customer you anticipate /report

Jim Broadbent

 

[beauman]

The table in the main report {CashRecTrans} does not have any unique field to link to the {JobTrans} table
CashRecTrans contains a referenceID (one or many)that correspond to the "referenceID in the {JobTrans} table.

{CashRecTrans}
ReferenceID CashReceived ItemType
9001 500 Inv
9025 50 CAdj
9035 10 WriteOff

{JobTrans}
ReferenceID JobNo Job Description
9001 23541 Cards
9035 23541 Cards
9025 55555 Paper

I have no problem dumping the subrpt if necessary.
If I link by ReferenceID I will get 2 hits on JobNo 23541.
If I had a selection formula in my rpt that would look at JobNo being returned and compare that with the previous JobNo returned and move on to the next record if it they were the same.

 

[lbass]

As Jim suggests, try eliminating the subreport, and link the job table to another table in your main report. Group on Customer ID and then on Job No. If you don't have duplicate rows because of your links, you might be able to just insert summaries on your amount field. If the results are inaccurate because of duplicate rows, use running totals, with results displayed in the Job group footer and/or the customer group footer.

For additional help with summaries, it would be useful to see what your data looks like with the Jobs group added, and to see what you would like your results to look like.

-LB

 

[Ngolem]

don't you want 2 hits for JobNo 23541?? Or am I missing something here??

If you group on Customer ID and then JobNo then with the tables linked you will be able to separate the data.

Or is this too early in the morning for me?

Jim Broadbent

 

[beauman]

The object of the report is to calculate commission for individual salepeople. If I get multiple hits on JobNo i.e. 50001 and 50001, the commission will be calculated twice. Unfortunately the {JobTrans} table is oriented toward the production info for the job. Conversely the {CashRecTrans} table is oriented toward the Customer info.
There is a one to many relationship between JobNo and ReferenceID. Linking the tables on ReferenceID gives me multiple hits in the main report. I thought if I used a subreport, I could find a way to do away with the multiple hits and just pass the Job info to the main report once.
I know this sounds like I am a little nuts, but my boss thinks this stuff is easy.

 

[lbass]

That's what running totals are for--you can do sums, etc., based on change of {JobID} so you eliminate duplicates, or you can conditionally count, etc. There is very likely a way of doing this without subreports. Have you tried grouping on CustID and then JobID? If you show us the results, and what you want the results to look like, someone could give you the help you need here...

-LB

 

[Ngolem]

I am having trouble - and I was a sales manager once so I understand double commissions - understanding how these tables interact.

If the Job TransAction table relates to Production information....how does that relate to commissions of a salesman???

Jim Broadbent