Multiple lines on Report

[Keith Hubert]

Help please can any one show/tell me how to make a report come out like this.

Q U O T A T I O N

1st Floor Bedroom
2 Pairs Net Curtains

Will require 5 metres Voile �35.75
To Make Only 2 Pairs Net Curtains �35.00
To Supply & Fix 2 Corded Curtain Tracks �45.00


1st Bath Room
Fabric Valance

Will require 1.5 metres fabric Client Supply
1.5 metres Lining �5.25
To make only 1 Fabric Valance �35.00

2nd Bath Room
Fabric Valance & Roller Blind

Will require 1.5 metres fabric Client Supply
1.5 metres Lining �5.25
To make only 1 Fabric Valance �35.00
To Supply & Fit 1 Roller Blind �75.00


Master Bedroom
2 Pairs Net Curtains & 2 Blackout Roller Blinds

Will require 5 metres Voile �35.75
To Make Only 2 Pairs Net Curtains �50.00
To Supply & Fix 2 Corded Tracks �45.00
2 Blackout Roller Blinds �150.00


Top Floor Bedroom
1 Pair Net Curtains

Will require 6.5 metres Voile �42.90
To Make Only 1 Pair Net Curtains �22.50
To Supply & Fix 2 Corded Curtain Tracks �35.00


All the � amounts should line up.

Is this possible?

Keith Hubert
London.

[French]

RE: Multiple lines on Report

It's absolutely possible. A lot depends upon the structure of your database in the first place, but at a minimum, you'd need a field for the room, another field for the description of each priced item, and a third field for the price of the item.

For your report, you would sort and group by room, then in the detail section, place your description field to the left, and your price field far enough to the right that you have room for your longest description.

This is a very simplified version of what you probably want to do, but what you describe can be achieved several ways.

[JohnZaleski]

RE: Multiple lines on Report

Paul explained this very well. What you might also want is a sequence field ( eg 001 002 003 ) to force the order of certain lines in your printed quotation rather than relying on the order that you originally enter them. Group by room and sort by the sequence number. You may forget to enter the description line without a price that preceeds the lines with amounts. If you go back and enter it, you want a way to force that line to print prior to the lines with prices. As Paul mentioned, the look of the report depends mostly on a well structured database.

[Keith Hubert]

RE: Multiple lines on Report

Paul and John

Thanks for your replies. You both say that I should have a well structured data base, I think that is also where I am having a problem. Do I have lots of small tables or just 2 or 3 big ones. As You can see from my Quotation report we like to show the variouse elements that go into the cost of making drapes etc.

Thanks again for your advice.

Keith Hubert
London

[forskare]

RE: Multiple lines on Report

Keith,

The size of the table isn't as important as the size of the set. Some tables are only 2 fields while others are quite large. Alpha can accomodate unlimited records and 1,024 fields in a table. (How big is your hard drive?) A lot depends on what data you need to track and it's relationship to the "customer".

Take a look at the invoice application that comes with A5 and examine the tables and set. This will jog some thoughts on how you should structure your application. You might want to check out the 1st chapter of the Users Guide. Even if you've read it a few times, it's always good to 'go back to the basics'.

No doubt, others will have additional (and better) advise.

kenn

[Stephen Williams]

RE: Multiple lines on Report

Keith -
one of my big AHA's in figuring out databasing was screens and reports do not have to have the same tables and sets. A screen form can look and work great, but not report well.

What I did was lay out the report I wanted (as you have) then figure the underlying table that would make this report a piece of cake to make, then figure how to swap data from the tables that support the forms to the table that support the report. This sounds complicated (and I hate to say it does take a little work) but it RUNS like a dream. I zap the report table, then fill it with an append operation, flesh it out with an update operation, then produce the report...

[French]

RE: Multiple lines on Report

The first thing to do is sit down with a pad of paper and make a list of the discrete pieces of information you wish to store. Any time you have a piece of information and you can project that you will want to retrieve, calculate, sort, or otherwise manipulate only part of it, you should break it in to two or more fields. For example, rather than having NAME as a discrete piece, it's helpful to have a separate field for FIRSTNAME and another for LASTNAME. When storing the address, it's best to have separate fields for STREET, (and probably a second field for more information for more complex addresses), CITY, STATE, COUNTRY, and POSTALCODE.

As you list each bit of information you wish to store, you can start to identify which of Alpha 5's field types would be appropriate and determine how large the field should be. In general, unless you plan to do calculations on numeric data (in which case numeric field type would be best), the character field type is a good choice. For dates, obviously, the date field type is best.

If you set up what you need with a single table and find (or can anticipate) that certain fields are filled with the same data as the same fields in other records, you'll want to consider taking those fields and creating a separate table and then creating a set to link the tables together.

It works best to start with a simple, basic design and enough data to test what you're trying to do and then build more complexity into your design as you try different ways to accomplish what you want to do and find the way that is best for you.

[Keith Hubert]

RE: Multiple lines on Report

Hi Stephen

Thanks for your reply.
The reason I was asking about whether to have one table with many fields apart from the customer table, which of course would be the parent, was, the report that came out was very strange. First it gave me the details of one child table then the other child. I had all the fields in the detail section in a rich text box. So now I will try it again with all the data in one table. The other problem I found was that I could not make a calculated field in field rules using a field from another table even though it was in the same set. Any thoughts?

Keith Hubert
London.

[French]

RE: Multiple lines on Report

"First it gave me the details of one child table then the other child."

Just the behavior described in the manual for a situation in which you have two child databases feeding into the detail section of a report. The manual suggests using subrerports in this situation.

"I could not make a calculated field in field rules using a field from another table even though it was in the same set."

Reconsider where you're trying to set up a calculated field. From the control panel, click on the set for which you wish to create a calculated field. Click on the Design button, and from the field definition screen, click on the button on the toolbar with the "xy". This opens a window to allow you to create calculated fields for the set, and you can use any fields from the parent or child databases. As a bonus, fields you define this way will be available in the drag and drop list for any forms, reports, letters, or other layouts you create for this set.

[Stephen Williams]

RE: Multiple lines on Report

Keith -

create the single table without a thought to the parent child tables. BUT use fields from those tables (you will fill them with an append). OK, now you can make your report based on this table.

Next you need to create an append operation that will post the data that you want from the child table.

Next create an update to fill the fields from the parent table using lookup functions based on the linking key theat ties the parent to the child.

Then put it together with a script. You can do it with an action script.
1. Zap report table.
2. append from child. (you may need a little extra code to create the filter as needed)
3. update it if necessary
4. prodsuce the report.

Stev

[Keith Hubert]

RE: Multiple lines on Report

Hi Paul

Thank you for your help it is very helpful, I have so much still to learn about Alpha.

I have tried using a subreport but I think there is a small problem somwhere, probably me. When I preview a report with a Subreport in the detail section, I get the attached error message. Your help please.

Keith Hubert
London.