Novice Question

[fsinger]

I guess I'm a novice now that I've shifted from Filemaker to Alpha 7. Does it really have to be so darned hard??

I don't know whether to give it up or not - you've all helped me before, and I purchased Alpha based on that experience.

I have a two table (for now) database with Parent table BORROWERS containing all of a bank's borrowers with key field BORROWER_NBR. The one-to-many child is NOTES table, where one borrower can have multiple notes. The notes table contains BALANCE, DATE, PAYMENT, ETC., the borrowers table contains BORROWER_NBR, NAME, SHORTNAME, ETC.

I just need to know if I can easily do this:

I want an interactive report or browse so I can look at it an choose certain borrowers. I want several fields to show based on the BORROWERS table. I want to include items from both tables, but I do NOT want to show every record in both tables. Every time I design a SET, I get all the records from both tables - that doesn't help. I want the following to show only line by line:
BORROWERS:BORROWER_NBR
BORROWERS:NAME
BORROWERS:SHORTNAME
NOTES:TOTAL (OR SUMMARY) NOTES (FOR EACH BORROWER_NBR)
NOTES:TOTAL (OR SUMMARY) BALANCE (FOR EACH BORROWER_NBR)
I also want a boolean checkbox for "SCOPE" at the BORROWERS level, which I can check to review that borrower or not.

I have built this successfully in two databases, Approach and Filemaker. I can't get anywhere near it in Alpha!!

Please help if you can. I'm at a choice point here again, and may just write off my purchase of Alpha. I'm not a whiner, really, but I shouldn't be spending hours and hours trying to get this thing done!

Thank you,
Fred

[Bob Houle]

Every time I design a SET, I get all the records from both tables

Query by Form is your friend. I have placed Query buttons on most of my major forms to allow users to 'build' special reports, like the one you are trying to do.

The 'Query by Form' feature allows you to filter the database any way you like and if you base your reports on the current selection (filter), only those records are selected.

For example... to find Borrowers between number 12 and 20 you would place the form in query mode and insert the numbers 12..20 in the BORROWER_NBR field and then run the query. Your form (and reports) are now filtered to display only these borrowers.

You could add more criteria, such as: BORROWERS:NAME field being filtered at the same time such as: Smit* which would include only names such as Smith, Smithy, etc.

Queries are the real strength of Alpha Five. Access and Filemaker do not even come close in this area.


HTH,
--Bob

[fsinger]

Thank you Bob. No, I have been using queries for a long time, and understand the different types of queries (by form, etc).

I'm going to attach the report I designed in Filemaker. Even being new to FileMaker at the time, this report took me only a few hours to figure out and perfect. I've spent three times the effort on Alpha, and haven't gotten anywhere close!

Please see attached report with comments on it - (a pdf file) to see what I am trying to do. I don't need someone to do it for me, I just need some initial direction. I know a browse won't do it. Do I need to use Xbasic?? And if so, why?

[Peter.Greulich]

Your explanation is not really clear. It sounds like the problem you are having is querying? Apparently you are having trouble limiting the display to certain records only? If so, exactly which records do you wish to display?

P.S. Alpha can easily do many things. I think somehow you may not be seeing the forest for the trees?

[fsinger]

You are right, I wasn't clear. I have attached a sample of the report that I want to my reply to Bob (above). I limited it to a few records, but I want to display ALL RECORDS from the parent table, with certain summary information from the child table displayed with each parent record. This is an easy task in FileMaker.

Then I will visually go down the display (browse?) of all records and use a boolean checkbox to pick the ones I want to review. I may query at the start, but I want to visually pick from the screen.

The issue is not query. I will later query on the Checkbox field to get all the records I have selected - and report that to the bank.

Fred

[Tom Cone Jr]

Fred, this looks pretty straightforward.

Design your report layout based on the one to many set. Insert a group break based on the borrower number. Do not include a details band. Use only a Page header band for your firm's information, a group footer band for the line by line borrower summaries, and a report footer for the report wide totals. In the group footer band include no white space above or below a single line of fields. Drag and drop new calc / summary fields into the group footer band to display the total number of notes and total balance figures from the child table. Use a report footer band for your report wide totals. Drag the new calc summary fields into the report footer band and define them there.

This really shouldn't take you very long. I expect the greatest trouble will lie in gettting the new calc/summary fields defined correctly, but that's pretty easy for someone who understands his data as well as you. Sorry you waited so long to ask for help.

-- tom

ps. Your Loan Officer field belongs in the borrower table if there's just one loan officer assigned to each borrower, as your sample report suggests.

[Tim Kiebert]

Reports vs forms in Filemaker

Tom and any others who may want to help Fred,

You may not be aware that Filemaker (FM) does not have a reporting structure the way Alpha 5 does. A report in FM is basically a layout (equivalent to a Alpha form) designed for the job. FM combines report and form characteristics in the one layout.

So when Fred says he has a report in Filemaker he is really saying (in Alpha language) that he has a form designed to view his data that becomes a report when printed. That is why he speaks of an interactive report. The fields in his report layout are still accessable the same way fields and variables are in an Alpha form.

Hopefully this helps Fred by others adjusting their advice to take this into account. In the mean time I'll try and come up with something as well but I have not yet had a lot of experience with Alpha reports.

tim (ex/current Filemaker user)

[Peter.Greulich]

aaahhhhh...

Thanks Tim. That is very enlightening.

Fred, in light of the difference between Alpha & Filemaker, you may have to study Tom's advice rather carefully. Since the approach between the two systems is conceptually different.

[Tom Cone Jr]

Tim, thanks. You're correct about my lack of knowledge/experience with Filemaker. My suggestion to him would lead to a saved report layout that could be previewed or printed, but would not be "live" or "interactive".

If he wants something live then he will need to define calculated fields in a form layout that use tablesum() or dbsum() to total the number of loans and the total of all loan balances, using filter expressions in each that limit the summation to child table records that contain the correct borrower number. These could be displayed in an embedded browse object. However, he should not count on being able to print this form layout. Print support for forms is rudimentary. In Alpha Five the report engine is a much better choice for hard copy printouts.

If Fred supplies some sample data this would make it much easier for us to give him a specific example.

-- tom

[fsinger]

to Tom and Tim,
Thank you both very much for the lively input. I'm going to spend tomorrow trying out the advice. I'm afraid that since I only have used two databases in my life, that the concept of Alpha is a bit of a hurdle. Both Approach and FileMaker use "reports" that are like forms when you use them, and reports when you print them. This is an extremely useful tool. Thank you Tim for expressing that so well for me!

I'm going to stick with Alpha to see if I can make the concept hurdle. Besides, I love the challenge (to a point).

Is there any good reading material? I've printed out the 907 page pdf file and have read a lot of it, but it's REALLY BAD!! Are there some good resources?

Fred

[Tim Kiebert]

Fred,

Here is a sample to help you over the first hurdle. It is only the form side of the equation which allows you to view your data and select the records via the 'Discuss' field. A very quick way to filter the records after selecting all the ones you want with the discuss field would be to right click on the discuss field in any record that has discuss checked and choose 'Quick Filter'

If you were to create a report on this set as well then you could use the form to filter the records and run the report using the currently displayed records.

As far as resources go the main one I use is the large download of the 'AlphaFiveHelp.chm' which is the same as the online help. Also check out the tutorials on the Alpha Software site, the newsletter archives, and Dr Peter Wayne's learn alpha.com. Oh, I almost forgot, the Alphasports sample file. Many times you will be refered to it by others to demonstrate a technique so you might as well get familiar with it. Study it and disect it. Make a clean copy of it before doing much with it. That way you can play with it till it breaks and then if it is to far gone delete it and start again.

I would encourage you to persevere, you won't regret it.

[Ohlen Cartmell]

If there's any problem with documentation it's that there's so much of it and it's hard to know where to start. Be sure to work the tutorials. Also, look at the products available from Alpha Press.

Susan Hussey Bush has made her books available in the monthly newsletter. Since I wasn't in a hurry I downloaded each chapter of the version 5 book and made a desk reference (thanks Susan). She's made her version 6 book available, also. Lastly, Dr. Peter Wayne, a frequent contributor to these forums, had a book on alpha 5 that really helped me. I see it's no longer available in the products page, but you could contact him directly www.learn alpha.com.

[fsinger]

Tim,
Wow! Thank you for the file and the advice. I'm going to spend time today teasing it apart and learning more about Alpha.

Luckily I'm between bank jobs for Xmas, so I can take some time to give Alpha a real try. Normally I'm going from bank to bank, and can't waste time learning anymore, but I scheduled a couple of weeks with nothing to do. Perfect timing for learning Alpha.

I hope I can repay by helping someone else after I learn enough to get my own work done.

Fred