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
