AR example in Xbasic Book HELP

[wlk8791]

In the XBasic book sold by Alpha, there is an accounts receivable example. This example shows how an aging report can be created. I think this is a great example, but I would like to do more with it. Can someone help?

This is what I would like to do:

Instead of just creating an aging report, I would like to create an aging table which will either be viewed on a form or update a permanent table on a form. Not sure about the best way to approach this though.

I have a form which shows a customer's unpaid invoices. I would like the form to view the same as an aging report. For example, I want to show a list of all unpaid invoices with fields (current balance, total balance, 30 days, 60 days, 90 days, 120+ days).

How could I create this? How can this be updated once an aging report is ran at billing date?

[Keith Hubert]

Re: AR example in Xbasic Book HELP

Hi Kristie,

Whilst this can be done, I would suggest that you let the report display the outstanding invoices in their respective periods.

How would you display 100 invoices, the form would be unreadable?

You could include the report within the form as a Supercontrol->Report Preview.

[Garry Flanigan]

Re: AR example in Xbasic Book HELP

It is certainly possible to create records for review in a "temporary" table, but I am curious as to why you want to do this. In V9 you can cretae reports with drill-down capability, which may be another solution, depending on what you want to achieve.
Garry

[Tom Cone Jr]

Re: AR example in Xbasic Book HELP

Kristie, I share the misgivings others have expressed. This kind of "aging" data is like a snapshot. It shows the status of open invoices only on the day in which it is computed. The next day it would be wrong for any invoice that was on a 30 day cusp the day before, or if a payment is received, or if a further invoice is issued. To make this work you'd wind up having to recompute and store the current "snapshot" for all customers every night, and then update the second table whenever a new transaction occurred during the current day. While this can be done, most would agree that it's more efficient to compute this for a customer only when you want to see a current snapshot, and don't go to the trouble of creating snapshots for all customers every day.

[wlk8791]

Re: AR example in Xbasic Book HELP

I used a software program that applied the AR aging in the following way:

On the 1st of the month, I would do the following:

1. Ran a service charge.
2. Ran an aging report.
3. Ran customer statements. I would enter the date range, then the program would print customer statements for ALL customers at one time.
4. In the A/R Review for each customer, what I described was on the form. (Unpaid invoices with aging balances)
Each time a new invoice for the current month would be created, it would be listed in the unpaid invoices and total added to the current and total balance fields. The aging balances would remain the same (other than the current and total balances when new invoices were entered) so the age of invoices were not recomputed until the 1st of the following month.

[Tom Cone Jr]

Re: AR example in Xbasic Book HELP

Kristie, none of what you describe requires the creation of a new table to store the aging data you mentioned at the outset. Take a look at the Tablesum() function in the helps, and study the Tablecount() documentation for lots of examples using filters. You should be able to easily create a calc display field in your form that will show the sum of all invoices for the current customer that fall within (between) any desired date range. The typical solution includes separate tablesum() calc display fields for Current, 30, 60, 90 and over 90 date ranges.

Tablesum() is not especially fast. If you have lots of invoices to deal with you may want to build a custom solution that would run faster by stepping through the customer's invoices only once.

[wlk8791]

Re: AR example in Xbasic Book HELP

Thanks a lot, Tom. I'll study it and work on it. Sounds much simpler using the "sum" instead of writing all that code and then fixing errors.

[Peter.Greulich]

Re: AR example in Xbasic Book HELP

I
For example, I want to show a list of all unpaid invoices with fields (current balance, total balance, 30 days, 60 days, 90 days, 120+ days).

In addition to what Tom said, you could have a form (based upon your invoice table) w. an embedded browse showing all the outstanding invoices (Balance>0). Use color equations (e.g. Date() - Inv_Date <=30, etc) to differentiate 30,60,90,...

[Keith Hubert]

Re: AR example in Xbasic Book HELP

Hi Kristie,

As Garry has suggested you will need a temp table to hold data of the unpaid invoices.

You will need to collect the data with at least two operations. One to summarize and another to post to the temp table.

It all depends what fields you have in the invoice items table.

This routine can be run on demand. As Tom has said, this data will be a snapshot each time the routine is run.

If you could post a sample of your Invoice set with dummy data I'm sure we can come up with a working example of 30, 60, 90 and 120 day report of oustanding invoices.

This temp table could be both displayed as a browse for each customer and for a full report of all unpaid Invoices.

[wlk8791]

Re: AR example in Xbasic Book HELP

Keith, I'm trying to attach my files. Site says there is an error on the page.

Can I send via private message or email until this is corrected?

[wlk8791]

Re: AR example in Xbasic Book HELP

Ok, I see I cant attach to private message.

Here are the details:

Sets:
Customer 1:M Invoice Header, Invoice Header 1:1 Payment

and

Invoice Header 1:1 Customer, Invoice Header 1:1 Payment

Tables:

Invoice Header
Invoice Number
Customer ID
Invoice Date
Employee ID
Invoice Total
Invoice Balance

Payments
Customer ID
Invoice Number
Payment Date
Payment Type
Total Cash Payment
Checkonedate
Checkoneamount
Checkonenumber
Checktwodate
Checktwoamount
Checktwonumber
Totalcheckpayment
CC Number
CC Expiration
Cardholdername
Totalccpayment
Totalpayment
Invoicebalance

Customer Table has many fields, but my key linking field would be Customer ID which also in invoiceheader and payments.

I reversed the set so that I could have a tabbed customer form with all info which would make it easier to view by user.
Tabbed form will have invoices paid and unpaid, aged invoices, etc.

[wlk8791]

Re: AR example in Xbasic Book HELP

Using my other laptop to upload these files.

Here are the zipped tables and sets for the data I'm working with for the AR feature.

[Peter.Greulich]

Re: AR example in Xbasic Book HELP

Unfortunately, I don't have time to look at your tables, but attached is a simple demo (from some years back) of a sample/simple aging report...

[Tom Cone Jr]

Re: AR example in Xbasic Book HELP

Kristie, I took a quick look at your upload. As you know there are no sample data in the invoices or the payment tables. Would you be good enough to populate the tables with some sample data for us, and then please tell us which form you have in mind for the display of aged balance totals. Thanks.

[Keith Hubert]

Re: AR example in Xbasic Book HELP

Here is my take on what was asked for using the sample data from Alphasports Invoice Items. This does not take into account Tax and Discounts, but these are minor posting adjustments.

App opens to Menu. Update Invoices button runs routines to update Invoice Header table.

I have not done the report, this would be the same as Peter created but with calculated fields based on different periods and probably todays Date().