Complex Layout from Two Tables

[Dana G]

Attached is a file of what I need my end product to look like.

(I have removed or renamed some client specific information.)

I have 2 tables in the same file from which I need to create a report that resembles the one attached.

Currently the guy working with this report runs a script that exports fields from both tables and puts them into an excel document. The rows with numbers in column A are from one table and the rows with OPI in column A are from the second table. He has to format all of the colors, strikeouts and add some text. He want's to be able to do all of this in FileMaker.

Is it possible to do without portals? I don't have a problem with them but he wants to be able to see what he is going to print while in Browse. When I add a portal to one of the layouts, I can only see one record at a time from the current layout.

I know this information is kind of sketchy but it's really hard for me to explain when I can't show you the two tables. (There is a lot of sensitive client related material).

If you don't think this can be done in FM alone...do you know of a plug in that may accomplish this?

Thanks for your time!

CHANGESCHEDULE-102705.pdf

[T-Square]

Take a look at building a report based on your OPI table that includes a sub-summary when sorted by date. Put the in this part, the info from the number lines in the body, create a script that selects the right records and sorts by date, and view the results.

HTH,

David

[Dana G]

Thanks T-Square but...,

I can only see one record at a time.

In the example, that was attached from before, each line in the report is a different record. All of the lines with a 4 digit number under the red date are from one table. All of the records with OPI under the red date are from another table.

When I start breaking things up into subsummaries,

1) I can't figure out a way to see all number records and OPI records at the same time while in BROWSE mode.

2) I can't get them to fall like they are shown on the attachment which is all number records followed by all OPI records (if any)

What I can get is one number record followed by one OPI record.

If there are 5 number records and only 1 OPI record, the same OPI record fills in for all 5 number records.

If the layout portion can be figured out, then...

Ultimately he needs to be able to format additions to the report in blue and deletions need to show as strikeouts.

I have achieved something in the past that was close using a portal but when he formatted the text it changed in the original table. Also, he couldn't see all records from both tables while in browse mode.

Maybe this is a lost cause and I need to tell him to keep doing in in Excel.

I sooo appreciate everyone's help and attempts to help me with this

[T-Square]

There's a setting in the layout that tells it to display one record or many, and I can never figure out where it is, except to re-create the report from scratch and select the appropriate layout type in the first prompt. That's the one where you choose a columnar or tabular layout.

That said, the solution I offer here will really only work in preview mode; in browse mode, the header will display a header that's appropriate for the currently-selected record.

So, your only real option in browse mode is to use a portal...

With regard to the client changing formatting in the base table, why not set up a couple of checkbox fields in the appropriate table that represent the concept of what your client is doing visually? In other words, your client is highlighting an entry to indicate that it is important. Instead of him (or her) using highlighting, have them check an "Important" box, and then build your fields so that they change format based on the checkbox value. That way, the underlying data is preserved, and your client is focussing on the process rather than on marking things up.

It's also beneficial when your client decides that blue is no longer the highlight color of choice and asks you to change it to red. Then, you just change the calculation once, and all the colors are changed.

David

[Dana G]

I'll see what I can find on the layout setup - all I know to use is "list view" but I will search some more.

The checkbox formatting is a wonderful idea that I will most definately use! Thanks for the outside the box thinking on that one.

I will see what I can do and thank you again for your suggestions! :clap:

[netbuilder]

One solution would be to create a third table, just for reporting. Modify your current export script to instead copy the information you need from the two tables into this report table (where the 'numbers' and 'OPI' end up in the same field). If the text formatting is procedural it could be done in that script as well.

[Dana G]

Great idea! I hadn't thought of that. I'll see how that works out.