I don't have the file open in front of me, but this grouped object button performs a script. If you open and examine that script, you'll find a set field step that updates a field with the value in the same portal row as the clicked button. That's the field that you'll want to reference. I think that I put that field on the layout so that you can see it update as you click the buttons of the different portals.
True. The 'ultimate' field, listing the values shows those chosen. However, the 'checkbox' is really conditional formatting switching between normal and point 120.
1. I'm uncertain how through ESQL I'll switch the 'X' on/off and
2 Would I be better using ESQL throughout the hierarchical conditional lists?
1. ExecuteSQL "reads" data, it doesn't change data. What exactly are you trying to do with ESQL here? I didn't use this in my examples as that wasn't necessary. PS: in FileMaker 13, you can also use "hide object when" to hide the "X".
2. I can't offer an answer from the limited info available to me.
1. What I'm trying to do: I lists of a) Regions, b) Companies, c) Products. a) and b) are checkbox fields. c) is a Portal to Products with the product name grouped with an 'X'. As per the Adventures file script when the 'X' is selected/deselected (conditional formatting to point 120) a 'ProductList' field is checked for that product, if it isn't there, it's added, if it is there, it's removed from the list.
My problem is that through relationships, I can change what appears in b) and c). However, causing these records to disappear doesn't deselect their checkbox and therefore doesn't change the ProducList. So what I'd like to do is deselect any value that no longer appears in the list, but keeps those values already selected that remain.
So you have, in a sense, combined the concepts of a hierarchical conditional value list from Adventures #1 with the scrolling check boxes of Adventures #2...
If you check out the examples of hierarchical conditional value lists, you'll see some examples that clear the other fields when you change category selections. That may be the simplest option.
But there is another option. The return separated list of values in your first two fields (that's what your check box format produces when the user clicks check boxes) can be used as a match field in a relationship. Say your check boxes are for "fruit" ; "vegetable" and "Mineral". If you click the check boxes for "Fruit" and "vegetable", then as a match field in a relationship, it can match to every record that is categorized as either "Fruit" or "vegetable" and the list function can return that list of all matching values.
then FilterValues ( List ( RelatedbyCheckboxesTable::category ) ; LayoutTable::ScrollingCheckboxValues ) will return only those previously selected values that are members of the remaining categories. Thus this can be the calculated result parameter of a single set field step to update the scrollingcheckboxValues field to omit the values no longer part of the categories selected in your check box field of categories.
And yes, an ExecuteSQL function could return the same list, in theory, to use inside that filtervalues function call but I think you'll have trouble turning the list of selected values into a set of values to use with IN in your SQL query.
FilterValues ( List ( RelatedbyCheckboxesTable::category ) ; LayoutTable::ScrollingCheckboxValues ) .....brilliant !!
Took me a while to figure that I needed a new Calc field for this calc.
No calc field should be needed for this. This can just be a script step:
Set FIeld [ YourTable::checkBoxesField ; FilterValues ( List ( RelatedbyCheckboxesTable::category ) ; LayoutTable::ScrollingCheckboxValues ) ]
True.........I'm still getting used to the fact that where in Excel 1 'command' does 1 thing.....FM can do 3, with 2 variants!
2 points/ queries:
1. If I apply the above filter in a 'set field' script activated on changing the parent checklists, when I de-select then re-select a Company its products are de-selected from the final list. However, if the filter is part of a calc field, de-selecting a Company de-selects products, but re-selecting the Company adds the products back to the ProductList, because they remain selected. It gives a choice depending on which is most useful to the user.
2. If I have a ProductList, then select some more items, there's 1 space between the old and new items in ProducList. This space disappears if I de-select a parent Company. It's not a problem; merely wondered why it happens.
1) not quite, the values are still selected in the field, they've just been hidden from the portal. That is a potential source of trouble to keep in mind as your display no longer matches your data stored in the field.
2) FilterValues appends a return on the end of the list it returns--even if it returns a single value. You can add code to strip off this extra return if you need to keep the list "pretty", but as you have observed, it doesn't affect the function of this system.
Note: All of the "values" functions add this extra return on the end of their results. It's quite annoying as it is more work to remove that extra return than it would be to add it in if you need it.
Re. your point in 1) above, in what way might I get problems by having my ultimate ProductList accurate, but, with certain 'parent' list items hidden that would add products to ProductList, if the parent items were unhidden.
That is, I can see that this untidy in that stored data is not 'synchronised', but it might be quite useful to users to be able to 'play' with list combinations where deselecting/re-selecting a 'parent' item would remove/ put back a 'batch' of items into the ProducList. Ultimately, what can be seen by the user and the ProductList, is what will matter.......unless of course this could change without a deliberate intent to change it.
That would depend on what you do with the data in this field. If you later employ an edit box formatted copy of this field for inclusion in a report, the data won't match the display. If you use this field as a match field in a relationship, it might match to records that your display don't show as having matching values. There are probably other ways this could be a source of trouble but those are what I can imagine. So this is not a "don't do it" as much as a "be careful" admonition.
Thank you. Really useful guidance and information for reference.