relationship with master file has many records on relational file..

[markdesign]

Hi..

I have a Master file A, withe one key field, I have several matching records in relational data base B.

The problem is I need to list those matches in the file B and list it in Master file A, and be able to print it for faxing. I have no problem listing the matching records in a portal, but as you know portal does not work well for printing if it goes over the lines limit.

To show you the example of the problem, the master file A is a fax out database that shows part number, description, quantity, and price. The relational database is a RFQ database with vendor name as the key field with the part number, descripition, quantity, and price, per record.

so when i type the key field of vendor in Master file A (fax out database) I would like an expanding body of all the matching records from file B without having to use portals (each record containing many fields).

Please help with suggestions..

[markdesign]

Thanks for the help...but,

Ok, so i made the script going back to the relational database B, but how do i display all those fields with so many records. The portal has no difficulties displaying multi field, multi record.

I purchased the filemaker 5 bible, and that book doesn't cover how to display multi record matches other then portal.

I'm a bit new to scripting so please bear with me.. =P

Thanks again for the help.. =)

[markpro]

Create a columnar report in File B. This will allow you to list multiple records. If there is information in File A that you want to have print in the header of File B establish a relationship from "B" to "A" and place the related records fields in the header.

[markdesign]

ok, I've tried doing the columnar report, but it only shows the first record of the find, not all the record related to the key field. The columnar report works fine if it is in the same database, but during relational database it only shows one. Also what the portal allows me to do is have additional info on the top (e.g. customer name, address etc..) and able to edit them, but with the columnar report, those data are placed in the header and cannot be edited.

I'm a bit of a newbie with filemaker, but I did read the manual, and the Filemaker bible 5, cover to cover, but I am lacking field experience =(

Any more thoughts would be appreciated, other wise, i'm going back to the book to see what I missed =P

Thank you.

[ July 26, 2001: Message edited by: markdesign ]

[Kurt Knippel]

quote:

---

Originally posted by markdesign:

Hi..

I have a Master file A, withe one key field, I have several matching records in relational data base B.

The problem is I need to list those matches in the file B and list it in Master file A, and be able to print it for faxing. I have no problem listing the matching records in a portal, but as you know portal does not work well for printing if it goes over the lines limit.

To show you the example of the problem, the master file A is a fax out database that shows part number, description, quantity, and price. The relational database is a RFQ database with vendor name as the key field with the part number, descripition, quantity, and price, per record.

so when i type the key field of vendor in Master file A (fax out database) I would like an expanding body of all the matching records from file B without having to use portals (each record containing many fields).

Please help with suggestions..

---

Create a script in file A that uses the Go To Related Record command. Chose a relationship to file B, same one as the portal. Make sure to check the "Show Only Related Records" option so that only those records in the portal are part of the found set.

[rhakka]

if printing is the only problem, make two layouts, one is data entry with a normal sized portal, and one is for printing, which has a portal larger than you would ever need on it with sliding/printing enabled for it. the print button would go to the print layout and bring the user back again after the print is executed.

[markdesign]

wow...that did work, But sometimes the portal gets cut half of a sentence, but that can be played with to barely come out right at the bottom.

The only problem with this method is that the top portion of the fax sheet need the customer, phone, fax, invoice number, to show on every page and this does not work with the extra long portal.

Can anyone direct me to a good example website that has an example of showing relational match of multi fields / with multi records to show like an invoice sheet?

[markdesign]

Nevermind, it worked fine. looks kind of odd, having the scroll print but at least it works with less scripting then necessary,

Thanks to eveyone for their great input

[LiveOak]

Calm down a bit. It's a poor programmer who blames his tools...

CaptKurt gave you the answer to the problem. You need to preform the printing from the related file and not try to use a portal. In file A, create a print script with the script step Go to Related Record (show only related records, your relationship to , it will create a found set in B consisting of just the records showing in the portal. In file B, create a reverse relationship using the same fields as you relationship from A to B. Also in file B, create a layout with a:

Header

Subsummary when sorted by (Invoice No.?) leading

Body

Subsummary when sorted by (Invoice No.?) trailing

Footer

In the leading subsummary, place the related fields from your invoice in file A (name, address, terms, etc.). In the body part, place the detail for your invoice line items. Set the layout to be a list view (done in Browse mode). Create summary fields to add up the subtotals from each line item and place the summary fields in the trailing subsummary.

We had a lunch time discussion about the page break printing problem the other day. It turns out to be a VERY difficult technical issue because of how printing is accomplished and what constitutes a printed object as the print driver renders the page. We couldn't think of a way around the problem.

-bd

[sneddens]

Forgive me, I have not read these postings in detail, but it looks just like a problem I have recently solved for myself. Portals are great for data entry but terrible for printing. So I make a report in the database with the related fields. It has a leading "sub-summary when sorted by" the master field. The body has the related fields I am looking for. Wherever it's convenient I make a button to call up this report and attach a script which finds just the master record I'm interested in. You would probably do this on the form that has your portal.

[LiveOak]

Sounds exactly correct to me!

-bd

[markdesign]

It worked!! hahahah!!! thank you thank you!.. Took me 2 weeks to figure it out but it was worth it,

My apologizes to LiveOak and others for my ill mannered temper. I am an idiot..

The extra effort in explanation helped. I still have no Idea how it works but following it step by step did do the job. And it looks great! Maybe one day, I will know how it worked...

[markdesign]

dang! problems again, it definitely is not printing correctly. It gets cut half of a sentence, when printing. And it moves around on different printers.

Sorry markpro, I understand what you are saying but columnar report does not work when you want to display multiple records from a related DB as compared to a self related DB.

The only other way is through complex scripting, which I do not have enough experience in. I assumed this is what most people use filemaker for, (invoice Database) but I don't understand how stupid this company is to make this part so complex.

In my opinion, Filemaker is a piece of garbage that needs serious update to make it more user friendly. And fix the basic thing as printing problem. even the preview moves around when zomming in and out, whats the point of having a preview when it moves?

FILEMAKER SUCKS!!

[markdesign]

just a small problem....

Everything is looking great! except when there are numerous invoice to print, the first print out in the related database looks fine, but the second page does not have the new header as the invoice. The body is correct since they are in after the "Subsummary when sorted by."

The header only shows the information for the first invoice, and the rest of the print out has the correct body, but the header continues to show just the first entry.

I tried Making the header in the invoice a look-up, but that didn't work. It looks like I need to copy the field and past new within the script but so far I am having no success at this.

This is the last obstacle for me to finish this project, so please help....

Thank you

[Kurt Knippel]

The header should display the data from the first record ON THE PAGE, not in the found set. Simply make the header fields normal fields in the related file, choose them as if they are exactly the fields that you entered the data into. Using calculated or lookup tricks most often does not work.

Now sometimes you need to use a title header if you need something different on the first page.

Also make sure that you subsummary parts and thier order matches the fields and order of the sorting and you should be in fine shape.

[sneddens]

When using "subsummary when sorted by.." you have to be in Preview Mode to see every row of the body.

It is necessay to call this report with a button and script because it must be sorted and set to Preview.

[markdesign]

Thank you both of your recommendation. It was very helpful and it worked. As for capt kurt's suggestinos, I had to print out this thread and look at it for 2 days and cross reference with the manual to finally understand it.

Your suggestions was on the money, and I now have a working Database!!

One thing about filemaker, great program for what it was designed for, .. but don't ever try to learn it on your own for advanced projects.. =P

[rogermax]

There isn't much you can't do with FileMaker Pro. Thinking outside the box and simply watching these message boards gives great insights. Lots of these developers have kindly put up example files on their sites etc.

Practice.

Oh, and keep cool while at it.