Report with data from a checkbox populated field

[Kris M]

Can anyone take a look at this solution and see if their is a better way?

I have a field in a main table that can have zero or many values from a checkbox control/value list.

I have to count the values in a report so I came up with a script that "parses" the many values and relevant one values into a temp table then reports on that. It works fine in my final solution but wonder if their is a way to do it without involving the temp table.

TIA

Kris

test_value_list_stuff.zip

[Vaughan]

The quick method is to make a calc field that counts the number of returns in the field, since the values are return-delimited. The full expression that takes into account empty values is:

Case( isempty( field ) ; 0 ; PatternCount( field ; "¶" ) + 1 ) )

The correct method is to store the individual values on related records, and display them in a portal. Getting reports out of a relational system like this is very easy; for the "multiple values in a field" method, it's painful or impossible.

[Kris M]

I understand the value of counting the returns and setting up relational structure however I am probably not going to be using portals in this or any printable (preview mode) report. Why not? If their are more related records than the portal is set to display without scrolling then they dont get printed or seen. Also having a portal sized to contain the maximum number of possible related records wastes screen real estate when printing.

If I am wrong about this please correct me.

Did I understand your reply?

Thanks for the response.

Kris

[Søren Dyhr]

Case( isempty( field ) ; 0 ; PatternCount( field ; "¶" ) + 1 ) )

Can you explain why you bother dealing with NULL values when filemaker in summaries ignores them anyway??

PatternCount( field ; "¶" ) + 1

Must be all it takes, really??

--sd

[Søren Dyhr]

If their are more related records than the portal is set to display without scrolling then they dont get printed or seen. Also having a portal sized to contain the maximum number of possible related records wastes screen real estate when printing.

Check out this option, although it's a bit on the heavy side for the CPU:

http://fmcollective.com/2007/08/29/pseudoportals-with-alternating-fill/

--sd

[comment]

PatternCount( field ; "¶" ) + 1

Must be all it takes, really??

No, ValueCount ( field ) is all it takes, really.

[comment]

The relationship between boards and components is many-to-many, so a join table between them would be in order. You can get away with using a checkbox instead, as long as you are aware of the limitations. For example, you can never repair more than one component of the same type. And some types of report are difficult to produce, as your script demonstrates.

If you enter the repaired components into a portal, you will be creating the records in your report table in real time - so there will be no need to collect them by script. Printing should be done from the join table, no need to use portals (although portals CAN shrink during printing).

[Kris M]

I didnt know portals shrink as you said.. cool..

The reason that i dont do the data entry for components with a portal is because each "board" line item is already a related record in a repair record.. soi in actuality if i was going to use the join table it would be the third level away from the parent record when doing data entry.

The file that i attached is not the complete solution just and example of the issue i am trying to understand.

I can expand the example slightly to show the exact reason for some of my choices if it would be valuable to continue the discussion.

Thanks all for your replies.

Kris

[comment]

if i was going to use the join table it would be the third level away from the parent record when doing data entry.

Yes, but it is third level away from the parent record in reality, so...

You could have a portal to Boards, and next to it another portal to the components of selected board (selection being done by setting a global field to the clicked board's ID).

[Vaughan]

Comment wrote: "No, ValueCount ( field ) is all it takes, really."

Yes indeed!