Conditionally combine portal records into one field

[benmort81]

Hello All,

I have a parent table with a related table that holds records with 2 fields - a field holding either "S" (Strengths) or "W" (Weaknesses) and a comment field.

For report printing purposes, I'd like to combine the comment field of all related portal records in a combine_field in the parent table - but only if the record is a Strength.

Could you please advise if I need to script the combining similar to this http://help.filemaker.com/app/answers/detail/a_id/2403/kw/combining records but with an additional check on the S/W field...

or is there a more efficient solution whereby I can make my combine_field a calculated result using the List function and some kind of conditional function like If or FilterValues?

Thanks for your time,

Ben

[eos]

If you want to print a report, you would normally do it from a layout based on the child table.

What exactly do you want to include in your report?

[benmort81]

Hi Eos.

The Parent table records instances of Lesson Observations.  There are various fields like observation date, observer and 4 area that are rated 0, 1, 2 or 3.  Strengths and weaknesses also get recorded via a portal from the lesson observation layout.

The report would have:

• A  section showing all the fields recorded in the Lesson Obs record.
• A section that lists all the strengths recorded (I'd like this section to expand or contract depending on the number of strengths recorded.
• A section that lists all weaknesses recorded (expanding/contracting).

I'm I approaching this in the wrong way?

Thanks for your help,

Ben

[eos]

I approaching this in the wrong way?

​You mean by calculating List()s from the context of the LessonObservation? I'm afraid so … 

Try creating your report layout based on the UnnamedChildTable.

A section showing all the fields recorded in the Lesson Obs record.

​That would be the Header part, into which you place the desired fields from the parent LessonObservation record.

 

• A section that lists all the strengths recorded (I'd like this section to expand or contract depending on the number of strengths recorded.
• A section that lists all weaknesses recorded (expanding/contracting).

​That would be

1. a Sub-summary part by type (Strength/Weakness) that serves as a header for each group; specify that field as the break field (sorted by), and place it here

2. the Body part that lists the records each group; place the desired fields from ChildTable here 

This will take just as much space as you have records. If you have individual fields you want to shrink based on content, look into sliding. This link should help you:

http://fmhelp.filemaker.com/fmphelp_13/en/html/create_layout.9.2.html#1068091 

Final thing to create the report is to write a script. These are the two essential steps:

Go to Related Record [ UnnamedChildTable ( UnnamedChildTable ) ; matching only current record ]
# “finds” all related records; select the table (occurrence) that your layout is based on as target table (Show records from), and your report layout as the target layout
Sort Records [ by UnnamedChildTable::type ]
# same as the break field in the sub-summary part

That gives you

Header: fields from LessonObservation parent record
––––––––––––––––––––––
Sub-summary: Strengths (type field)
  Body: record from Child table; desired fields
  Body: record …
  …

Sub-summary: Weaknesses (type field)
  Body: record …
  Body: record …
  …

Note the option in Go to Related Record to open a new window; convenient to preview the report, and either print or discard it, then have the window closed and be back where you started.

For all of which you'd have to amend your script, so maybe take it slow … but start studying the script steps.

Edited June 3, 2015 by eos

[benmort81]

Hi Eos,

Because I was approaching this from the wrong direction, I had concluded that I needed to get the portal records into a combined field in the LessonObs table so that I could use sliding (which would probably work in a hack around way).

I'm going to work through your post now and redesign the report based on your suggestions. 

Thanks very much for your help.

Ben

[eos]

which would probably work in a hack around way).

​Maybe it would, but using a proper report list layout is much easier.

[comment]

I had concluded that I needed to get the portal records into a combined field in the LessonObs table so that I could use sliding

​To add to what eos already explained: you would need such field only if you wanted to place the combined strengths (or weaknesses) within the same text block - i.e. with no line breaks separating the individual values.

[benmort81]

​Maybe it would, but using a proper report list layout is much easier.

Your solution works like a charm of course!

Thank you both very much!

Edited June 4, 2015 by benmort81
Clarity

[benmort81]

1. a Sub-summary part by type (Strength/Weakness) that serves as a header for each group; specify that field as the break field (sorted by), and place it here

 As a small aside.  My headers for the groups are "S" and "W" as that is how I have recorded them.  However, I want them to be written "Strengths" and "Weaknesses".  Do I have to record another field per record with the verbose heading - or is there a more efficient trick I can use for this report?

Thanks again,

Ben

[eos]

You could use Replace Field Contents to permanently replace all of your Ss and Ws with the long form (and don't forget to adapt the value list, too, if you use one).

If you want to keep the short forms: create a calculation field (type text!) =

Case (
  type = "S" ; "Strength" ;
  type = "W" ; "Weakness"
)

and put that into the s-s part.

One approach that doesn't scale very well but that you could use anyway, having only two types: create two layout text objects as "Strengths" and “Weaknesses”, put them into the s-s part on top of each other, and hide each one conditionally using

type = "S" / type = "W"

respectively. 

is there a more efficient trick I can use for this report?​

There is a hack (that is scalable), but it's support-heavy, and you're probably more interested in a working layout than learning the intricacies of FileMaker Pro.

Upgrade to FM14, and this is very easy without an additional field … 

Edited June 4, 2015 by eos

[benmort81]

One approach that doesn't scale very well but that you could use anyway, having only two types: create two layout text objects as "Strengths" and “Weaknesses”, put them into the s-s part on top of each other, and hide each one conditionally using

type = "S" / type = "W"

 

This is perfect for this report with only 2 types.  Thanks!

Another problem I am running into though is not being able to have both the Strengths and Weaknesses on the same page.  The weaknesses always break onto another page as explained in this post  http://forums.filemaker.com/posts/798928f963

Any idea how I can get around this or am I going back to the drawing board?

Thanks so much.

Ben

 

[eos]

Show us a screenshot.

[benmort81]

Show us a screenshot.

​

So the white space at the bottom of the Lesson Observation window is where the sub-summary type has broken onto the next page.  The top of page 2 of the report has a grey bar with the heading Weaknesses (Actually "Considerations" now).  and a list of the records marked as such.

What I actually want is the weaknesses section to follow on from strengths and fill-up the white space. 

[benmort81]

Actually,  looking at an earlier test PDF, the report was coming out as I expected with both sub-sections on the same page.

But...  the comment field in the body section was not big enough to fit lengthy comments so I expanded the body section and comment field to accommodate them.

So is it a problem with FM believing that 5+ strength comments will take up a whole page even though with sliding enabled - they could actually take up far less space on the page?

[eos]

I'm pretty sure that FileMaker computes the actual page layout based on the “slided” content, not the “unslided” field size. So increasing the field size shouldn't have an adverse effect. (“Pretty sure” … “shouldn't” – maybe ask an expert? )

Are you sure that in your first screenshot, the following (“Weakness”?) comment – incl. the s-s header – isn't too large to fit on the page?

Also, make sure to look at the settings in the Part Definition dialog. FM isn't a DTP program, but you should be able to eek out a decent looking result.

[benmort81]

Eos,

You're a gentleman and a scholar! Thanks so much for your efforts - I've learnt a lot with your help.

Ben

[benmort81]

​Hello again,

As a follow-up to this discussion - I have downloaded a trial of FM14.  I have opened up my solution in the new version and generated the Lesson Observation report.  The report works exactly as expected - Weaknesses following on underneath strengths on the same page.  Nothing has been altered in the layout.

Back in FM13 again, and the same problem with the second sub-part breaking over onto a new page.  This looks like it might be a bug that has been fixed in FM14! 

Upgrade to FM14, and this is very easy without an additional field … 

Now i'm in FM14 - do you mind telling me what is the easy way to change "S" to "Strength" on the Sub-Section part, without using the conditional formatting trick?

Edited June 9, 2015 by benmort81

[eos]

Now i'm in FM14 - do you mind telling me what is the easy way​

Add a button bar; make it 1 segment only; set it to show the label only; add a calculation for the label like 

Case (
  YourTable::type = "S" ; "Strengths" ;
  YourTable::type = "W" ; "Weaknesses"
)

Format the segment as desired (e.g. remove borders); to format the label text, select Button Bar: Segment in the Inspector.

Put the segment into the s-s part.

As mentioned: I would rather use the long form, since you can usually calculate an abbreviation from a long form by a set of rules, but not vice versa. Imagine the above calculation for twenty types – then compare with the single

Left ( longForm ; 1 )

[benmort81]

 

As mentioned: I would rather use the long form, since you can usually calculate an abbreviation from a long form by a set of rules, but not vice versa. Imagine the above calculation for twenty types – then compare with the single

Left ( longForm ; 1 )

​Yeah, definitely agree for more than 2 or 3 cases it would be better to record the long form in a record.

FYI, because I've only got 2 cases, I'm using the layout objects as headers, but rather than conditionally formatting to make invisible the redundant text box (which has it's limitations), I've found you can 'Hide object when' based on a calculation in the Data tab of the Inspector. Works a treat.