Using sets with query and table list

[wlk8791]

I need help with designing sets.

I am designing a program that keeps customer information, performs accounts receivable tasks (invoicing, posting payments, prints statements, etc), accounts payable tasks, employee records, etc.

I would prefer NOT to use a tabbed approach as the user would prefer an individual form for each task for easier use.

I want a form that shows a browse of a customer's account information including invoices (unpaid and paid):

Current balance, balance, 30 days, 60 days. I also want to be able to apply a service charge and print customer statements. I want a field where the customer id can be typed in a field on the form and the browse or table also viewed on the form shows that particular customer's list of invoices and other info above.

How do I design the set relationship to perform these tasks?

I've worked with Access, but it seems Alpha uses a different approach.
Please help....thanks so much

[Tom Cone Jr]

Re: Using sets with query and table list

Kristie, the set structures should match your overall database design. A good beginning point is to think about where you plan to store your data. What tables will be needed? How should they be related to each other? Then think about the processes that the user will need to use. What information should be stored when a new customer is added? Where should it be stored? What should be stored when an invoice is produced? Where should it be stored? How are payments going to be applied? and so on. We can't give you meaningful help on set structures without understanding the specifics of what you have in mind.

If you want to study a useful example of an invoicing module, check out the AlphaSports database in the Samples folder that is installed with Alpha Five. You can examine the set structures employed there. The design notes help explain how the forms are designed.

-- tom

[wlk8791]

Re: Using sets with query and table list

Ok, I have attached images of my tabels, sets, and forms lists.

I've also attached the Form (Paymentpost) which is just what I have so far in regards to posting payment for an invoice.
I want to create a table or browse for a form which contains customer account information as follows. This is just a browse form, no information will be entered on this form by the user.

How should I design the set/tabels, etc?
How do I create the searchable customer ID field which "queries" the payments and invoices based on the customer ID field?

Form with a:
Field used to type in Customer ID which will produce an embedded browse which shows customer account activity including invoices (paid and not paid), payment information, current balance, 30-day balance column, 60-day balance column, and 90-day balance column.
I want all to be read-only except the field of customer ID which will be used to pull up all account activity for that customer. I'm thinking of it as an Account Receivables Review.

Does this information help you help me?

[wlk8791]

Re: Using sets with query and table list

Here's a screenshot of what I would like to replicate from another program:

[Tom Cone Jr]

Re: Using sets with query and table list

Kristie, thanks for the additional information. Your goal requires a synthesis of pieces of information from several different tables. To answer it well would require us to study your database, and to learn how you process invoice and payment transactions. If you keep the open (unpaid) balance in each invoice header record you could produce most of what's in the "Accounts Receivable Review" form with a simple two table set: Customers primary, linked one to many to Invoice Headers, assuming each Invoice Header record contains the customer's id.

It's not clear to me where you store payment information, or how you handle split payments, or installment payments. Are payments tied to invoices? i.e. when you record a payment do you also record the invoice (or invoices) that it covers? On the "Accounts Receivable Review" form does the Payment column represent a sum of payments, or the last payment, or what? Similar questions occur for the Pay Date and Check_no columns.

If I may, permit me to suggest that you consider buying a couple hours of consultation time from an experienced developer. Getting started on the right foot will save you a lot of headaches down the line.

-- tom

[wlk8791]

Re: Using sets with query and table list

Thanks, Tom.

I am storing payment information in a payments table. The invoice header does have the customer id field.

I haven't gotten to partial payments or installments yet.

The AR review form I wish to replicate has payments posted in a much more complex way.

Payments are posted in a Cash Receipt batch and the form has some sort of browse where you can fill in certain fields.

I' ve attached some screenshots of the Cash Receipt process. Obviously this software is much more complex, but I think I could possible get the same result with less complication....I hope.

In the attached, you'll get an idea of the method used to post payments in the example software which I'd like to replicate the AR review form