I've cobbled together a Work Order system to track trouble tickets and am running into an automation issue.

I have a button that "closes" tickets, and I want to compare the contents of two checkbox lists before allowing the ticket to be closed. One field is "AccessoriesIn" and one is "AccessoriesOut" (both using the value list "Accessories"). When clicking the "Close Ticket" button, I want it to compare the contents of both lists and, if they don't match, stop the script and display a dialog telling the user to check the accessories and try closing the ticket again.

I'm sure the Case() and FilterValues() functions will be involved, but can't wrap my head around how to properly nest them.

Any clues?

Welcome to the forum.

See attached.

You don't really need the Case function, as the desired result is just a Boolean.

FilterValues ( List1 ; List2 ) = List1 & ¶

and

FilterValues ( List2 ; List1 ) = List2 & ¶

Will produce 1 if they match, 0 if they don't match. I can be used directly in a script as:

If [ FilterValues ( List1 ; List2 ) = List1 & ¶ and FilterValues ( List2 ; List1 ) = List2 & ¶ ]

or, negatively:

If [ not ( FilterValues ( List1 ; List2 ) = List1 & ¶ and FilterValues ( List2 ; List1 ) = List2 & ¶ ) ]

*Enclose the tests in parentheses for a negative test

The FilterValues() function seems to add a carriage return to the end of the results, unless there's already one there. And, strangely enough, the above test does NOT work if there is a carriage return at the end of either or BOTH of the fields. The later is unexpected, by me anyway; it's like FileMaker doesn't see its own carriage return. One could remove them of course, if one needed to (which you don't).

You don't have to worry about the above, as checkboxes will not add an extra carriage return. But I thought I'd mention it.

Before posting I ran my script it worked fine whether I chose 1, 2 or more items in the check boxes.

After reading your post I couldn't understand why you would need to use the filtervalues. Then I changed the order in which I checked the items and saw that FM keeps the list in the order in which the item is checked, so my script would fail in that instance. Why does FM do this? Is there some logical reason or is it 'Just the way they do it?'

Using a checkbox is only entering the data in a file, it is the same as typing the data, hense it follows the order of entry.

In other words, if you check the checkbox A, and then the checkbox with B, it is the same as typing in A B in the Field. If you check the checkbox B and then A, it is the same as you have typed B and then A in the Field.

HTH

Lee

Again, always learning something new. :P

Thanks Fenton, that worked a treat!

One little glitch, tho. If both lists are empty (i.e. no boxes checked), I get the wrong result. Any ideas?

Negative test:

not ( IsEmpty ( Lists::List1 ) and IsEmpty ( Lists::List2 ) )

and

not (

FilterValues ( Lists::List1 ; Lists::List2 ) = Lists::List1 & ¶

and

FilterValues ( Lists::List2 ; Lists::List1 ) = Lists::List2 & ¶

)

You, sir, are a genius. Many thanks.

This is an interesting problem in general - testing the equality of two unordered sets. I was wondering whether this could be done by a single comparison. Of course, in the present example, there's the value list - so one could use that to sort the two checkbox fields:

Let ( 

vList = ValueListItems ( Get (FileName) ; "Accessories" )

;

FilterValues ( vList ; AccessoriesIn ) = FilterValues ( vList ; AccessoriesOut )

)

On a more general level, we could do:

Let ( 

union = List ( SetA ; SetB )

;

Exact ( FilterValues ( union ; SetA ) ; FilterValues ( union ; SetB ) )

)

Note that this truly considers the sets to be unordered, so that {1; 2; 3} is deemed to be equal to {3; 2; 1; 2}.

Also, despite using the Exact() function, the test is NOT case-sensitive, so {a; b; c} is equal to {B; C; A}.

Yes, that is better, a little shorter anyway. It also has the advantage that it still works if either or both of the fields has a trailing carriage return, because the List() function removes them. (I don't know why the FilterValues adds one, but at least in this case it adds one to each of them, and the calculation still works.) In most cases of comparing lists I would not want a trailing carriage return to break the test.

You could add (as Blackie wanted)

IsEmpty (union) or

if you wanted empty lists to be considered equal. But in most cases generally, there'd be no action to take if the lists were empty.

Good point that this test (mine also) does not care whether there are duplicates or not. I can't think of a way that would, unless you used a custom function to sort each list, then compared them with exact.

it still works if either or both of the fields has a trailing carriage return, because the List() function removes them.

Actually, it's because the FilterValues() function ADDS them. I don't know why it needs to do that; I suppose at some time someone had the bright idea that LeftValues & RightValues could be concatenated directly - and we've been stuck with the rule that all the xxxValues functions must add a trailing carriage return ever since.

You could add (as Blackie wanted)

IsEmpty (union) or

if you wanted empty lists to be considered equal.

No need - it already does that.

Right. I forgot to uncheck the [ ] Do not evaluate if all fields are empty