Hi Michael,
That sounds a good plan to me if you do not need multiuser access to the
report
----- Original Message -----
From: "Michael Iannantuoni" <[EMAIL PROTECTED]>
To: "Dataperfect Users Discussion Group" <[email protected]>
Sent: Wednesday, May 02, 2007 5:41 PM
Subject: Re: [Dataperf] Totalling across Panels
Hi Brian,
Thanks for an ever full explanation of your solution. Multi-users are not
involved in this case so Tim's solution will work.
I had considered something along the lines of your Invoice Number panel
but was worried about circular links between the Payments panels and the
new Receipts panel.
The database was set up to replace someone else's scribbled figures &
notes in various notebooks. Not only did it replace that but we took over
the system from that other person so were not aware of all of the demands.
It has been running successfully for just over a year now although it has
been tweaked and had bits added to it as extra demands were put on it. (I
wonder how many other databases are re-thought after April tax/audits
summaries. :-) ).
I think under these circumstances, it is reasonable to reassess things so
will use Tim's solution for now but will redesign the payments panels over
the next few weeks.
Regards,
Michael
Brian Hancock wrote:
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
_______________________________________________
Dataperf mailing list
[email protected]
http://lists.dataperfect.nl/mailman/listinfo/dataperf
_______________________________________________
Dataperf mailing list
[email protected]
http://lists.dataperfect.nl/mailman/listinfo/dataperf
