Good morning!

I have a rather simple database, kind of a call center that that tracks incoming calls and consists of a CALL table and and a NOTE table. The primary key in the CALL table is CALL_NO and there is a foreign key in the NOTE table also named CALL_NO. Basically I create a call record for all calls that come in to my organization. As we try to resolve the calls we may create zero or more notes. These notes are created in the NOTE table and are attached (related) to the calls. I have a portal on the CALL table that lists the notes and I have buttons that allow the user to drill into the notes because the note could be anywhere from a few words to a dozen paragraphs or more.

I want to be able to print a detail report that would list selected calls and show all of the related notes. My first thought was to create the report based on the NOTE field putting the notes in the body of the report and the CALL information in a summary section. This idea works fine when there are notes but when I encounter a call where there aren't any notes the information from the CALL table is missing. I have a feeling that I am doing something fundamentially wrong. I don't want to report from the CALL table and print a portal because the notes can get lengthy and I don't think individual portal rows shrink like individual fields do.

I hope I have made myself clear. Can anyone provide some guidance?

Thank you,

-Becky

I suspect that when there are no notes the related record is not being created.

When you create related record via a portal you cannot just rely upon the key field generating a blank record in the related table. You must fill at least one field in the related field to generate the related record.

If you try it (by hand) you'll notice that you don't get the related key value until another field is filled. Incidentally, auto enter does not work in the related table for this purpose - you'll need to script the entry, maybe the date/time.

Mark,

Thank you for your quick answer. I actually thought of creating one blank note for eack call but then I just figured I didn't know what I was doing and that there must be a better way.

I'll keep this in mind unless someone comes up with something better.

-Becky

Hi Becky,

It's true that if there are no child records for a parent, then the parent won't be shown in the sub-summary report based in the child table. If you need your all your CALL records to show, then you'll either need to run your report from a layout based on the CALL table (this as you've noted has sliding issues with the portal,) or have some mechanism to create a NOTE for every CALL (You might have your Call creation script also create a simple Note that says "Call started" or something.)

Ender,

Thank you. I'll consider your (and Mark's) answer as the final answer because I can tell from your many posts that you are very, very knowledgable about Filemaker but the solution is quite frankly, astonishing. This would seem to be a common task. I'm surprised that Filemaker doesn't have a better solution.

Anyway, than you very much for your help!

-Becky

Unfortunately, it is true that Filemaker doesn't do UNION joins (which is what you need) natively. But there are several ways you can create them yourself. In your situation, I think the best approach would be to create a union table (combining fields from both tables) and import two set of records into it, one from each table.