Display Results Based on Checkbox Contents

[MikeAz1]

Hi,

I am trying to create a script that displays the records that have their checkbox checked, but am not having much luck.

I have created a Field (MSDSCheckbox) as a number field and a Value List (VList1) that shows only the number one (1). I have placed the Field (MSDSCheckbox) on my layout and made the Number Format as Boolean (with non-zeros = Yes; and zeros = No) and told it to get it's value from VList1. I then use the following script:

New Window[]

Go to Layout["NewMSDSIssue"(MSDSRegister)]

Perform Find [Restore]

Action: Find Record Criteria: ProductMSDS::MSDSCheckbox:[=1]

Enter Preview Mode

Print Setup [Restore; No Dialog]

Set Zoom Level [150%]

I had the script running alright when it searched for the Last Updated date, so I know that all the layout/display features work, I just cannot get this Boolean function to work, and I do not understand why. I think I am doing everything correctly, so if someone can tell me what exactly I am messing up, or suggest a better way of achieving my goal it would be greatly appreciated. If you need further information please post and let me know what you need and I’ll happily provide.

Thankyou

Mike

[bcooney]

Two things. You call the field a checkbox, but you don't have it set to a checkbox, do you? Typically, you'd resize the checkbox so that the number 1 does not display.

But that's not the problem. I believe you have my favorite source of problems, a context issue. From your script, you are going to a layout based on a table occurrence, "MSD SRegister", but your find step is in a different TO. You must find in the same TO that you're in.

[MikeAz1]

Thankyou for your reply.

I have been able to figure out what to do by myself, so I thought I should post this as reference for anyone else who is having problems doing a similar task.

What I was trying to do was have a field (set as a checkbox) that users could check if they want that record to be printed on a report. I didn’t realise it would be such a mammoth task, and I still don’t really understand why it was and why a lot of what I initially did didn’t actually work, but hey, that has been the charm of FileMaker in my experience.

So to do this task:

1) Create your field as a Text field (not as a number field like everyone else will tell you to do. Using a Number field DID NOT WORK for me!).

2) Create a value list with one value that is ‘1’.

3) Enter your field on a Layout as a checkbox (you can hide the number by changing the text colour and shortening the field length).

Here I used 2 Scripts, the first script (set to button one) opened the layout and set all the records to uncheck their checkboxes; and the second script (set to button two) opened a new layout as a report and displayed the chosen records.

Script 1:

• Go to Layout[LayoutName]

• Show All Records

• Replace Field Contents [No dialog; TableOne::FieldCheckbox; If (TableOne::FieldCheckbox=1;0)]

• Sort Records [Resore; No dialog]

• Enter Browse Mode[]

• View As [View as List]

So what this did was go to my layout, display all the records, change the value of all the Checkbox fields to 0 (therefore unchecked), then sort my records and display the screen in browse mode as a list.

Script 2:

• Commit Records/Requests [skip data entry validation; No dialog]

• New Window[]

• Go to Layout [“Layout Name 2”]

• Perform Find[Restore] (Find Records TableOne::FieldCheckbox=1)

• Sort Records[Restore; No dialog]

• Print Setup [Restore; No dialog]

• Enter Preview Mode []

• Set Zoom Level [150%]

So this script commits all the changes (which need to be done or the last box you check will not display its contents on the report), opens a new window for the print layout, finds all the records which a checked (therefore FieldCheckbox=1), sorts the records and displays them in a print preview.

I’m sure there is a more straight forward way of doing this (so any comments are appreciated), but as I said, I had to fend for myself and this process does exactly what I need it to so hopefully it will help someone else. If it is at all confusing please post a reply and I will try to clear up any issues.

[comment]

1) Create your field as a Text field (not as a number field like everyone else will tell you to do. Using a Number field DID NOT WORK for me!).

I would be among those telling you to make it a number. If it didn't work for you, you should investigate why, instead of jumping to unwarranted conclusions. If you cannot pinpoint the cause, post a file showing the problem.

The other (and more serious) problem with your approach is that it won't work in a multi-user scenario.

[bcooney]

I know you're excited that you got this to work, but the problems you have weren't because the field was set as number, not text. A checkbox with a value list of 1 is just fine being a number. Again, did you verify that you had the correct context in your Find step. Do you know what I mean by that?

Also, as comment points out, marking a record by actually setting the field to 1 will present problems in a multi-user environment.

Picture this scenario: Sally spends 2 hours marking records. Bob arrives and runs your script that clears all the marks. Sally's careful marking is gone! Also, two users cannot edit the same record at one time. So, if Sally is currently editing a record, that record will not be reset to 0 by Bob. This is called record locking. Global replaces are prone to record locking issues in a multi-user environment.

The correct way to mark records in a multiuser environment is to turn your checkbox into a button. This button runs a script that simple appends the record's primary key ID to a global field. Then, before printing, to isolate just the checked records, one would use the gtrr script step using a self-join relationship, global field to primary key. The checkbox itself is a calc, see attached demo.

MarkRecordsDemo.zip

[MikeAz1]

Thankyou for posting and making some issues clearer (and for posting a demo, very helpful). The issue with a multi user environment will not actually affect this database, but I would like to do it correctly for future reference.

Regards