Hi Michael,
Although Tim's solution will work be aware that it is not multi-user as
described... If multple users run this report concurrently you will have
problems, as two set of record might simultaneously be copied, or used in
the report or deleted.
One way to make Tim's solution multi-user would be to create a unique
identifier for the report (eg use Ralph Alvy's Moment "function" or similar,
and then attach the identifier to each copied record, and use that
identifier for filtering and deleting records (delete your temporary data
after the report has finished).
As an alternative you might permanently create a record in a new panel as a
pointer to the sources for each Payment.
I had a similar problem which, due to initial poor design, I created for
myself. I had 4 very different Products, whose records were created either
before or after a Customer order was created. The Products were so different
that they each had their own panel and subpanels to describe them. I also
had an Orders panel, or I should have had AN Order panel, but instead I had
4 different Order panels as data links from the Product panel... It worked
well, until the client wanted to produce invoices directly from the system
and to automate the generation of an invoice number. Also the accounts dept
wanted to reconcile invoicing from this system to their accounting system
every day or two or three. Since the Invoice Number had initially been
generated in another system, all I need initially to do was to have it as a
field in each Order panel, but when it not only became the source of the
invoice number, and needed to be reconciled, my original design was very
flawed. Nonetheless despite this flaw it was working very efficiently and
reliably for the myriad of other functions it was doing, and changing the
basic nature of how it worked was potentially going to be very disruptive.
By creating a new panel which kept the Invoice Number, Order ID, and Source
of the invoice (eg Product 1,2 3 or 4) and using 4 Panel links to each of
those separate Order panels, and using calculated fields to pull data from
the appropriate source Order Panel, I had a simple means of collating all
the invoicing information into one panel. The trick however was to get it
to create a unique invoice number and populate it back to the correct source
panel. To do this, I had various panel and data links with Auto Creates on
the Data Link, and Keep-A-Totals, which forced a new record to be created in
the Invoice Panel, and its Invoice Number to be pulled back in the source
Order panel. In other words, when I created an order in one of the various
Order Panels, it automatically created an invoice record in the Invoicing
Panel, with a unique Invoice Number which appeared back in the source Order
Panel. It was then possible for the accounts department to work solely with
the Invoice Panel (which thru a menu option stopped records being created
through Panel Links if they accidentally traversed the wrong link to view
the source record) an Exception List index allowed them to see new (or
unreconciled) invoices.
The overhead of creating the separate record was not too great because it
happened without user involvement, and only had the bare real field data to
tie it back to its source record or for indexes for reports or other
processes. Multiple users to report on, or view that data. The Order Panels
in terms of the operators using those panels were unchanged, except now they
did not need to enter an invoice number.
So although using a temporary table to collect the data for a report is one
way, if you are ever likely to need multi-user reporting, then instead of
the complexity of that perhaps it could be more efficiently implemented as
permantently stored data, and be able for use in other ways.
Regards
Brian
----- Original Message -----
From: "Tim Rude" <[EMAIL PROTECTED]>
To: "Dataperfect Users Discussion Group" <[email protected]>
Sent: Wednesday, May 02, 2007 12:59 AM
Subject: Re: [Dataperf] Totalling across Panels
Michael,
In order to be able to sort the report by the Receipt Number (regardless
of
which panel the payment record comes from), you're going to have to first
combine the records into one panel. However, this doesn't mean that you
have
to permanently do this or abandon your database structure as you have it
now.
Create a new panel to hold temporary records. No user need ever see this
panel so it doesn't have to be pretty. Include on this panel all of the
fields you need to produce your report (i.e. Receipt Number, Name, Payer
Type [Advertiser or Subscriber], Amount, etc.). Create an index to sort by
Receipt Number.
Then create a report based on this Temporary Panel that does the
following:
1) First delete all records (if any) in Temp Panel
2) Using virtual subreports, go through each of your two separate
Advertiser
and Subscriber panels and copy relevant fields from the records you wish
to
include in the report to records created in the Temp Panel
3) Generate the actual printed report from the records now in the Temp
Panel
4) Clean up Temp Panel by deleting all records from it (to conserve
database
size)
It will take a little fiddling to set it up, but once it's done the user
can
just run the report to complete the entire process seamlessly.
Tim Rude
----- Original Message -----
From: "Michael Iannantuoni" <[EMAIL PROTECTED]>
To: "DataPerfect Users Discussion Group" <[email protected]>
Sent: Tuesday, May 01, 2007 6:41 AM
Subject: [Dataperf] Totalling across Panels
I have a database in which I have a Contacts panel which has Panel Links
to two payment panels, one for Advertisers and one for Subscribers
(these are kept as separate panels because Advertiser & Subscriber
payments follow a different structure). Each payment in both sub panels
has a Receipt Number and I have indexes based on these Receipt Numbers
so that I can sort by Receipt Number and total payment from a given
Receipt Number up to the latest. This works fine within each sub panel
but what I would now like to do is set up a report to list in Receipt
Order & total payments across BOTH panels. ie to produce a list like
this:
*Receipt Name & Amount*
0221 Smith (Advertiser) £20
0222 Jones (Advertiser) £30
0223 Brown (Subscriber) £6
0224 Walker (Advertiser) £20
0225 Dexter (Subscriber) £6
Total: £82
Anyone any ideas on the best way to do this please?
TIA
Michael
----------------------------------------------------------------------------
----
_______________________________________________
Dataperf mailing list
[email protected]
http://lists.dataperfect.nl/mailman/listinfo/dataperf
_______________________________________________
Dataperf mailing list
[email protected]
http://lists.dataperfect.nl/mailman/listinfo/dataperf
_______________________________________________
Dataperf mailing list
[email protected]
http://lists.dataperfect.nl/mailman/listinfo/dataperf
