Is this really a many to many relationship? Most many to many relationships are implemented with three tables, groups, items and a join table used to link the other two.
Is that what you have here?
Please describe your relationships in more detail.
well I would call it many-to-many but it may not be technically.
I'm using return-separated values sent via scripts to Items from Groups, to view Items in Groups. An Item can be associated with many Groups and a Group may be associated with many Items. I use substitute script steps via portal rows to 'turn them on/off'. This works fine ... except for the highlights. I want to be able to highlight portal rows that match, and for these highlights to be record specific, by which I mean that the highlights are for Items viewed via a portal in Groups and when I change to a different record in Groups, then the Items highlighted will be pertinent/specific to that record.
You do indeed have a many to many relationship, but have implemented a method that is much less flexible than using a join table to accomplish the same result.
My best guess is that this is your relationship:
Groups::ListOfItemIDs = Items::ItemID
What I'm having trouble with, is using conditional formatting to make the portal rows for Items (on the Groups layout) change colour for the Items that have been selected and appear in the Groups portal.
I am not sure I understand what you are describing in that sentence. If the Item appears in the portal hasn't it already been selected for a group? Have you set up a second portal to Items using the X operator in its relationship to list all item records and you click it to add the item to the current group?
I worked it out in the end via scripts. I seemed to be having an odd behaviour that I didn't understand with the conditional formatting, so I tried it another way (many other ways actually) and in the end a simple enough script does the job. I'm not sure how robust it will be though. Basically, I'm clearing the match field for the highlight in the entire related table (all records) then using another match/relationship to set the match field in just the relevant/desired records. So it's going from something, to nothing, to something. Seems a little fragile, but then again, perhaps no more fragile than setting and 'un'setting records in another Table.
You might find the "checkboxes" layout in this demo file for many to many relationships of interest to you. It could probably be adapted to Multi-key implemented many to many relationships as well as the join table that I used:
Thanks Phil. That's a really interesting file for someone of my level to see.
I do wonder about why one way would be better than another. I want to use this file primarily on iOS, so I want to keep it as light as possible. The join file will generate a lot more data (I suppose) whilst the scripts will require more cpu/time. Not sure which is best. Certainly, just using a setfield for 'on/off' works quite well, but the requirement for a portal to exist on a layout before you can use its selection is a shame, requiring those extra there and back again steps.
I don't think adding a join TABLE will make much difference in terms of file size. Your multi-value key field has a field index associated with it that will be quite large given the multiple values so it may be much of a wash in terms of file size.
Cosider setting up a report layout that lists two or more groups with each group member on a different row in the report--no wasted space and no portals used so you don't have to deal with the issues that can arise when printing from a layout with portals on it. That's easy to set up on a layout based on a join table, but how would you do it with a multi-valuekey field?
And what if you need to record some data specific to a given person's membership in a particular group such as designating the person's role within that group? Again, easily done with a join table as you can add a field for that to the join table, but where would you store that info with your approach?
Of course, you may find that you simply don't need those capabilities in your solution...
I'm having real trouble with this, having switched over from scripted replace/substitute keys.
I want to highlight a portal row in this way :
I now have 3 tables, Items, Groups and Items-Groups as a join. There are many items and they can be members of many groups. On the Group layout I want to be able to add items to a given group by clicking on a Portal list showing ALL the items. I'm now doing this using the join table and it's fine and works without a problem. The part I'm having trouble with, is finding a way of having Conditional Formatting, change the colour of the portal field in the ALL items portal, once an item has been added to the current Group. (The idea being that once a record is added to the Group, it is highlighted and so there is a distinction from the unhighlighted ones, that are not part of the Group).
I had this working when I was using only 2 tables by using scripted replace/substitute, but I can't find a way to make it work with a join table. The best I get is the currently active record in the join table being highlighted if it matches, or everything that matches (all Groups, not just the desired/active Group) or, just absolutely everything.
The Groups are not fixed, so they are being added to or deleted as well.
Please check out the "check box" layout in the demo file. The portal on that layout is a list of all contacts (think items). Clicking an item in that list selects it for the current event (just like clicking an item should select it for your group). Conditional formatting is used to make the "x" in the check box appear or disappear as a contact is selected or deselected. That conditional format expression could be used to highlight the fields in the portal row instead of producing the simulated check box.
I did look at that quite closely (following it through visually in a second window) but I can't see how to make that work for records in the join file which have 'many' related records in the Groups file. I can get it to work without a problem when there is just an either/or, or on/off if you prefer, but not when it's one of many. If I'm totally missing something ... just say 'look again' and I'll increase the damage to my limited brain and try again. :)
I think I'm missing something here.
Why would a record in the join table link to more than one record in Groups? That should not be the case, ever, for a record in the join table.
A portal to the join table should only list records that link to the current record in Groups. What you describe would then highlight all the records shown in such a portal.
In the demo file, you have a portal to all the records in the "other" table (items in your case if I recall the names correctly). There, a conditional format to identify the items that are a member of the current group makes sense as a way to distinguish the items that are selected from the items that are not selected.
In the check boxes layout, this is done with a conditional format expression that changes the font size of layout text--the letter x--to simulate the appearance/disappearance of a check in a check box. (The box shown is just a square drawn with the rectangle tool). To achieve a highlight effect, you'd take that same expression that checks for a related join table record and you'd reverse the logic so that it is true when there is a related join table record so that you can specify a different fill color to highlight the selected field(s) in the portal row.
The conditional format expression that I used on the "X" layout text (You have to ungroup the button before you can drill down to the letter x and see it's conditional format), is:
IsEmpty ( FilterValues ( List ( EchbContact_Event::ContactID) ; EchbAllContacts::ContactID ) )
If I rewrote that as:
Not IsEmpty ( FilterValues ( List ( EchbContact_Event::ContactID) ; EchbAllContacts::ContactID ) )
I could use the expression to specify a Fill color for the fields in the portal row to highlight them in a contrasting color.
I have added additional text to the demo file to help others better understand how the check box feature works.
It's hard trying to understand someone else's file. Actually, it's pretty damned hard trying to understand one of my own.
I think I see what you're doing now, but I'll have to break down the conditional formatting calc a bit more to understand why it works. It seems like your creating a return delimited list, and then removing everything but the match (key) value. I didn't know you could do this with a calc. I've been using scripts with substitute to achieve something similar, but this is vastly better. Ho hum. :)
Filtervalues is a good way to test for "set membership". I am indeed generating a list of values (my set of existing values in the join table) and then using Filter Values and IsEmpty to test to see if the current record's value is present in that list.