Parent-Child/Hierarchial Problems.Please Help!

[novice04]

I have 1 table which deals with accounting. It is an Invoice/Payment situation. I am trying to find the avg days between the avg and payment.I need to link different rows. How do I get different rows to show up on one line. I am a real novice trying to break into the game.To complicate I have a parent/child situation in the same table.

Example:

Customer|Parent Field|Child Field|Amount
Row 1| W.Mart 100 0 $1000
Row 12| W.Mart 0 500 -$1000

I need the report to look like this.

Customer Parent Child Invoice Amt Pay Amt
-------------------------------------------------
Walmart 100 500 $1000 -$1000

If someone could just give me some direction I would be greatful. Thanks

 

[rosemaryl]

Dear Novice04,

Can you provide a little more information?

What version of CR, what database?

Also you say you need to calculate the average days between avg and payment, but the example data provided does not contain date fields???

Can you provide more information, the data fields, and what you are expecting to see as a result on the report?

Regards,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[synapsevampire]

Your example betrays anything to do with dates, so I'll address the example provided, and you've supplied little technical information from which to work.

Your customers likely have unique invoices, so group by the invoice number.

Now you can create 2 running totals (Insert->Field Object->Right click running totals and select new->name it), one to sum the invoice amount (which you called parent) which will be reset at the invoice group, and one to sum the paid amount (which you called child) .which will be reset at the invoice amount.

Place all of your fields in the invoice Group footer.

If you want the report grouped by Customer, make that the outer grouping.

If this doesn't resolve, as Ro suggests, supply technical information, not descriptions:

Crystal version
Database and connectivity used
Example data
Expected output

-k

 

[novice04]

Ok, I guess this is how you reply.Never done it before.

I have more information for you.

I use cystal 10
Database= ODBC(RDO)
I am using 1 table
My main fields that I can use are:
CustomerID
TransDate
TransType
TransNo
TransUniqueID
ApplyToType
ApplyToNo
ApplyToUniqueID
ReferToTransType
ReferToTransNO
ReferToTransUniqueID
TotalAmount
AppliedToItem
AppliedToAcct
DateDue

Ok What result I want from my report.
I want it grouped by CustomerID

Like this:

Customer|Invoice #|Invoice Amt|Inv.Date|Paid Date|Avg.Days
JoBob's 123456 $100.00 02/01/04 3/1/04 30
Billy's 123457 $200.00 02/2/04 3/2/04 30
--------------------------------------------------------
Total Avg. Days 30

ApplyToNo is the field that is the PARENT
TransNo is the CHILD field

ApplyToNo hold the invoice number and
TransNo is the payment number

I have been using those to fields plus, CustomerID, TotalAmount,TransDate, TransType(This holds values of either P(for payment) or I(for invoice).

The tricky thing for me is that TotalAmount hold the amounts for both the invoices and the payments. And the date field does the same.

Thanks for all your help.

 

[IdoMillet]

In Crystal, select Database, Add to Database.

This allows you to add the same table again to your report. Provide an alias of "Payment" and join the two tables on ApplyToNo (has to be the same) but also:
Restrict (record selection formula) the table on the right to Invoice type and Restrict the table on the left to Payment type.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com