Insurmountable problem printing posters with resizable cells?

[jjjjp]

I'm trying to do something that is turning out to be much more difficult than I thought. I want to produce posters for series of workshops, one poster for each series. I have accomplished this in the past using Mail Merge in Word. To simplify somewhat: In FileMaker, I have a script that creates an Excel file, one row per poster. The data gets pasted automatically into fields in the appropriate Word file, and I can produce PDFs from there. Different types of series need their own word file, but there is a manageable number of possibilities.

I am attaching examples of two typical posters.

Here's the problem I seem to be having in arriving at a solution that occurs entirely in Filemaker. The poster has a top section consisting of information common to all workshops. The middle section is a table, one row per date. The bottom section consists of information common to all of the series. All three sections have variable length text, so they will need to take advantage of FM's ability to remove blank space by sliding up and resizing the enclosed parts.

I've considered 3 options. All have problems:

1. Create layouts that mimic the Word Mail Merge files. If I could get this option to work, it would be the easiest for me, because there would be the least change from how I now manage the posters. There would need to be a table in the middle section with borders. The table would have a fixed number of cells. The problem here is that there is no elegant way to create a functional Word-like table in Form View. I could improvise a table composed of individual cells, but the problem is that I would have no way of getting all the cells in a row to expand vertically to the same height. They would all have different amounts of text.
2. Use a portal in the middle section. I would need to do a fair bit of work to create the necessary fields and relationships. But that part is do-able. The problem is that inside a portal, the cells just don't seem to be resizing even though I have set each cell and the portal row to do that.
3. Use List View. For this option, I would create a Title Header and Title Footer, respectively, for the top and bottom sections. The problem here is that the Header appears to be vertically fixed. It doesn't shrink if not all the vertical space is needed.

Going back to relying on Mail Merge is a definite option, but I thought I would give this a serious try.

(Note that for now, I have put aside the question of getting long posters to print on one page.)

w+ poster 02 essential skills sat mid sep.pdf

w+ poster 02 essential skills sat mid sep.pdf

w+ poster 01 start to finish tue mid sep.pdf

[comment]

IMHO, you should definitely use List view for this. However, you will need a separate layout for each type of series (i.e. each arrangement of columns). Place the common information inside grand summary parts Instead of header and footer.

[Wim Decorte]

55 minutes ago, jjjjp said:

 

1. Create layouts that mimic the Word Mail Merge files. 

Or you could keep using Word for what it is good at... nothing wrong with integrating with the right tools...

[jjjjp]

Thanks, using grand summary definitely solved the problem of the header not resizing.

I agree that there's something to be said for using Word. In particular, one then has the option of tweaking font sizes, etc., to get everything on a page. For the other users, however, who don't necessarily have much experience with Mail Merge, it would be a plus to have all of the PDFs (fifteen or so of them in any given year for this application of the database) ready-made at the click of a button.

So maybe it makes sense now to ask: is ensuring that everything gets on a page as problematic as it would seem to be? I want to dump all the files on the Desktop, without any intermediate intervention from the users.

[comment]

44 minutes ago, jjjjp said:

is ensuring that everything gets on a page as problematic as it would seem to be?

Well, it's not trivial. What exactly would you want to happen when it overflows?

[jjjjp]

Well, the nicest solution would be for all fonts to decrease more or less uniformly in size, but I imagine that would be a scripting nightmare. I would settle for  reducing the scale of the image so that the vertical dimension no longer runs over a page. That might sometimes make for a skinny poster, but it would probably be acceptable. 

[comment]

29 minutes ago, jjjjp said:

the nicest solution would be for all fonts to decrease more or less uniformly in size,

Well, if all fields on the layout were calculation fields using a calculation like =

TextSize ( SomeRealField ; gFontSize )

then you'd only need to change the size stored in the global field.

 

38 minutes ago, jjjjp said:

I would settle for  reducing the scale of the image

I am sorry: what image are you referring to?

 

[jjjjp]

39 minutes ago, comment said:

Well, if all fields on the layout were calculation fields using a calculation like =

TextSize ( SomeRealField ; gFontSize )

then you'd only need to change the size stored in the global field.

 

That's a terrific solution. I assume I'd have to start with a largish value and iterate down, in a loop, till everything fits on a page? Is there a simple way (or any way) to know that I've run over a page? Will, e.g., Get ( WindowHeight ) take into account all of the scaling down all of the resizing of cells and rows?

46 minutes ago, comment said:

I am sorry: what image are you referring to?

What I mean is doing something equivalent to Fit on Page or at least Scaling when printing from some browsers.

[comment]

2 hours ago, jjjjp said:

That's a terrific solution.

Is it? I would have thought it's an awful lot of work. 

Another option you may consider is conditional formatting of those fields, again based on some global value. You could define say 3 or 4 possible sizes for each field. There would be less fine tuning, but also considerably less work, I think. 

 

3 hours ago, jjjjp said:

I assume I'd have to start with a largish value and iterate down, in a loop, till everything fits on a page?

Yes, that is correct. You will know you have run over when:

Enter Preview Mode 
Go to Record/Request/Page [ Last ] 
Set Variable [ $lastPage; Value: Get ( PageNumber ) ] 

ends with $lastPage being larger than 1.

 

3 hours ago, jjjjp said:

something equivalent to Fit on Page

I believe the only thing close to that is the scaling you'll find in Print Setup. Your specific printer may even offer a fit on page option - but you cannot rely on that being true for other users.

 

 

[Wim Decorte]

12 hours ago, jjjjp said:

For the other users, however, who don't necessarily have much experience with Mail Merge, it would be a plus to have all of the PDFs (fifteen or so of them in any given year for this application of the database) ready-made at the click of a button.

All of it can be automated, that's be beauty of integration.  The user does not need to be familiar with mail merges or knowing how to output from Word to PDF.  It really can be a simple as a button in FM, data flows to Word without any user interaction.

Chances are that you'll need some non-FM OS-level scripting to make it happen but:

- it's not scary

- it's very rewarding

- you may get a more optimal solution than trying to force FM to do something that is may not be terribly good at

 

[jjjjp]

12 hours ago, comment said:

15 hours ago, jjjjp said:

That's a terrific solution.

Is it? I would have thought it's an awful lot of work. 

Compared to what I was initially thinking, it will bring the task a lot closer to workable. I was assuming—don't know why—that text sizes needed to be set to integral values, and I hadn't thought of using calculations in the list.

12 hours ago, comment said:

Another option you may consider is conditional formatting of those fields, again based on some global value. You could define say 3 or 4 possible sizes for each field. There would be less fine tuning, but also considerably less work, I think.

I'll think about this some more, but I'm never comfortable with solutions that may not cover all possible cases—that aren't airtight in theory. Also, I don't want the user fiddling with options, so ultimately it seems it's down to iterating and testing either way. Maybe I'm not fully understanding this solution, but running through a finite # of cases seems in some ways harder than scaling down a global value in a loop.

7 hours ago, Wim Decorte said:

All of it can be automated, that's be beauty of integration.

I take your point, but Filemaker I know; OS-level automation or scripting, I don't. And the FM solution will guarantee that there's no work for users—no Word files to tweak after the fact, even if they are all prepared with no need to get involved in Mail Merge. Word, e.g., as far as I know, doesn't have something that uniformly scales down fonts, as Mac Pages does.

Important thing is I'm now in a position to decide whether I'm up for the coding. Thanks for the ideas.

[comment]

3 hours ago, jjjjp said:

I'm never comfortable with solutions that may not cover all possible cases—that aren't airtight in theory.

I am not sure there is a practical difference: either way you must have a minimum font size beyond which the text is unreadable - and therefore there's always the possibility of having too much text that won't fit to a single page, no matter what. So it's only a question of how many steps will there be between the maximum and the minimum. And that number will always be finite, because font sizes are truncated to integer values.

 

3 hours ago, jjjjp said:

Also, I don't want the user fiddling with options, so ultimately it seems it's down to iterating and testing either way. Maybe I'm not fully understanding this solution, but running through a finite # of cases seems in some ways harder than scaling down a global value in a loop.

What I had in mind is the same thing: run through (for example) a set of global values {3; 2; 1; 0} which for some field could correspond to font size of {16; 12; 10; 8} and another say {36; 24; 18; 16}.

 

[jjjjp]

32 minutes ago, comment said:

I am not sure there is a practical difference: either way you must have a minimum font size beyond which the text is unreadable -

True, particularly as you say that font sizes must be integers.

 

33 minutes ago, comment said:

What I had in mind is the same thing: run through (for example) a set of global values {3; 2; 1; 0} which for some field could correspond to font size of {16; 12; 10; 8} and another say {36; 24; 18; 16}.

Yes, I'm starting to see how this works. I won't have to set scads of global variables this way, which would be a lot easier. So, say, four separate conditions in Conditional format, with conditions looking like SystemGlobal::Iteration = 1,  SystemGlobal::Iteration = 2, etc. And then I choose the font under More Formatting . . .  .

Multiple conditions, then, all work separately, I take it. I have never used more than one condition.

Out of curiosity: if more than one condition is satisfied, the lower one cancels out the higher if they conflict on a format?

In the Script, I run through the 4 globals in a loop, and for each layout do a Preview, test for number of  page, and then exit the loop if # pages is 1. That sounds like the better option.

[comment]

15 minutes ago, jjjjp said:

if more than one condition is satisfied, the lower one cancels out the higher if they conflict on a format?

The last condition to be true overrides previous conflicting ones (exactly the opposite of the Case() function). If the conditions do not conflict, the formats are merged.

 

19 minutes ago, jjjjp said:

I'm starting to see how this works.

Do tell us how it came out. I didn't try this myself and I am curious if you run into any unexpected issues.

[jjjjp]

Will do. I could never have thought of this on my own, so thanks very much again.

[jjjjp]

I realized there would be a problem almost as soon as I got started, but much better to hit roadblocks early rather than late.

For the tables in these posters, I will need solid borders. In the past, when I have used the resize feature in list view, I recall that I haven't used borders. That would have been for a good reason: the vertical borders will all extend differently depending on the amount of text. I'm attaching part of a PDF from a past layout that relied on resizing of cells, but I've added solid borders on two sides of each cell in an attempt to create a grid. One immediately sees the problem.

Any ideas on how I might solve this? Otherwise I'm ready to accept, as Wim says, that some things FM just isn't set up to do.

table border problem.pdftable border problem.pdf

[comment]

On 8/4/2017 at 10:12 PM, jjjjp said:

the vertical borders will all extend differently depending on the amount of text.

Yes, this is an old and well-known problem. There used be a simple solution that used vertical lines extending across the body part instead of field borders. Unfortunately, this stopped working in version 7 and the solutions became much more complicated.

I haven't revisited this since the file format changed again when version 12 came out. I am happy to report that the old solution is working again, with one small change: the top end of the vertical line must be within the body part. The bottom end should extend slightly into the trailing grand summary part, as before.

(Not tested in versions before  v.16.)

[jjjjp]

23 hours ago, comment said:

(Not tested in versions before  v.16.)

I tried to implement what I believe you are suggesting and wasn't successful, but I'm lagging far behind—in FM 13. I'm attaching a simple test database. If the layout that opens on launch (TestListViewWithGrid) properly resizes cells in FM 16 in Preview mode, then that will mean that my addition of line objects is correct and FM 13 hasn't solved the vertical-line problem. If cells aren't properly resized, then I must have gotten the vertical lines wrong. Please have a quick look if you can?

TestListWithGrid.fmp12

I wish I were up-to-date on FM, but I must use what my colleagues all use as part of a licensing arrangement for a database with much wider use at my university. We may be moving up at the very least to 14 in the near future. I will plan to look into that.

But the best solution right now is probably just not to worry about vertical lines. No lines definitely wouldn't work, but horizontal lines on their own work surprisingly well for tables. (See attached.)

w+ poster 01 start to finish tue mid sep hor lines.docx

Edited August 6, 2017 by jjjjp

[comment]

57 minutes ago, jjjjp said:

I'm attaching a simple test

I have made it even simpler: there is only one line and it's red. How does it look on your side?

It should look like this:

TestListWithGridAsPDF.pdf

 

TestListWithGrid2.fmp12

Edited August 7, 2017 by comment

[LaRetta]

jjjjp, what you have wrong in your file is this:

Body top is 50
Bottom bottom is 150

Your line top is 50 ... this is correct.
Your line bottom is 150 ... wrong.  As Comment said ( and displays in his file ), the line must extend below the body.

[jjjjp]

1 hour ago, comment said:

How does it look on your side?

Looks good. I completely missed the last instruction. It looks like I now have all options open. Thanks for getting me back on track.

[jjjjp]

I completed the process of creating posters using conditional formatting and iterating on a set of global values (as discussed above). I ended up using 6 iterations. I ran into no unexpected issues. The basic idea was fairly straightforward to implement. Still, implementing this was a fair bit of work and quite complex at times. The real challenge was writing code to recognize patterns in the workshops in each series; preparing records that corresponded to the rows in tables (each pattern required a different strategy); and checking for irregularities. In all there were 8 layouts, or templates, corresponding to 5 basic patterns. Seven were for regular patterns, one for the case of no regular pattern at all. Were I to treat each series as irregular and create posters simply as lists of workshops, descriptors, and dates, this whole task would have been much simpler, but much less interesting.

I ended up going with 6 iterations per field. There were edge cases that I felt made at least 6 necessary. One also wants enough gradation to limit possible white space. Wherever possible, I grouped multiple fields together. Typically, there were 5 groups per layout. That amounted to about 240 font sizes to set via conditional formatting. I think it was definitely the right decision to go with conditional formats rather than the other way we had discussed. It was quite easy this way to run through each iteration and see how everything looked in Preview mode.

Thanks again for the excellent advice.

Edited August 16, 2017 by jjjjp