Forum Discussion

ArchiveUser's avatar
ArchiveUser
Qrew Captain
8 years ago

Relationship Filtered On Conditional Values - Contains but not equal to

I have a situation where many "Items" may be wired by many "Work Packages". I've set up this many-to-many relationship with an intermediate table where users can "Add New WiredBy". I then have a bunch of relationships in the background to get ultimately get a concatenated text field of all the associated work packages. A result for a given item may be "WP-1, WP-2, WP-3".

I then have a third table called "Cables". Previously when we didn't have this multiple-wired-by functionality and it was just 1 to many (an item is wired by a package), for a given cable record you selected which package this cable is associated with (ultimately this table produces the work package cable schedule) and the related item that is being wired would be a related dropdown filtered on the conditional value "Item: WiredBy" = "Cables: WiredBy Filter".

However in trying to implement this I'm running into the issue that I can no longer use the Conditional Values filter.
Now I want logic something along the lines of "Items: WiredBy Concat" CONTAINS "Cables: WiredBy Filter".

Is there a way to enable a more broad "contains" conditional value filtering instead of "=" logic?
Or if not, is there another way you can think for me to get a field in both tables that will consistently have the correct "equal" text in all situations?

The Cables really should be linked directly to the Work Package so I don't want to change the cables table filter options to the concat field.

I hope you can make sense of my description. I'm happy to try to explain differently if it doesn't make sense.

Thank you,
Leith
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    8 years ago
    I would consider this an imperfect solution, but as an interim solution I have done this:

    I have created a second field in cables called "Alternative Filter" which I made a text field 'from another field', pointing at the "Items: WiredBy Concat" field. Hence if the work package filter doesn't match one of the items associated with this cable "usually in the case of an electrical panel where multiple work packages are wiring devices to that panel", the user can select an alternative filter.

    I then have a background field I've called "Selected Filter" which is equal to the work package dropdown if the alternative filter field is empty, otherwise it is equal to the alternative filter. It is this selected filter field to which I have changed the Conditional value filtering of the items dropdown.

    I then have a sanity check row highlighting set in the standard cables report, where the record will be highlighted if the work package filter for that cable is not contained in the text of the "WiredBy Concat" of each of the associated items. So that sanity check won't raise issue when those values aren't equal, but it will raise an issue where it's not ONE of the associated packages for that item.

    As I said, I'd consider this imperfect as the user will have to use a second dropdown when the first dropdown isn't appropriate. That said, in Grid edit, once the user has used that alternative filter once to at least "find" the item in question, they can copy/paste/fill down perfectly fine.

    And one could argue this is at least better than what we had previously. Which was, for example let's say "Panel 1" is wired by multiple work packages. We would hence need to have multiple items named "Panel 1", each with the different required work package. That meant that in the Cables table, if you saw the item text "Panel 1" you didn't know which of the Item records named "Panel 1" that actually was. We also had logic to prevent duplicate tags that basically had to then be circumvented to allow the duplicate panel tags in this case, which was messy.

    Anyway, if someone has a better solution I'd welcome it. There have been many times in managing quickbase apps that I've wanted Conditional Value Filtering in relationships to be able to be logical comparisons other than x=y (contains, greater than, less than, etc). And as an extension of that I've thought the idea of relational summary fields for text values (aka a concatenate summary field of text values) would be very helpful many times. I have a workaround for that one which I got off this forum, but a concatenate summary field would be far more elegant.

    Thanks for reading my ramblings,
    Leith
    • MCFNeil's avatar
      MCFNeil
      Qrew Captain
      8 years ago
      I'll have to think about this one.  Not too often there is something that (1) stumps me outright and (2) I find a great value in what you are asking.
  • YouruiYeo's avatar
    YouruiYeo
    Qrew Trainee
    7 years ago
    Hi Leith, did you ever hear back on any alternative solution that worked better? I'm in the exact same boat right now (a many-to-many relationship that should use CONTAINS for the conditional instead of =) and in our case, your imperfect solution might not work (the end user would not be able to work with this). In our case, if we can't control the input properly, we might have to just allow people to pick from the large list and then do data sanity check to prevent incorrect combinations, but it would be awesome if there was a way to filter the drop down from the start!