Discussions

Expand all | Collapse all

Multi-select text to IDs

  • 1.  Multi-select text to IDs

    Posted 19 days ago

    Hi 

    I have a many to many relationship which I want to simplify by making use of multi-select text fields for the user to select from. But the challenge I have is that whilst I want my users to be able to select text items from a list, I need QB to convert these text selections to a list of RIDs for use in my third party app (via API).

    So imagine I my multiselect gets its values from a shared field in another table, with the following rows (RID; Value)

    1; Red
    2; Blue
    3; Green
    4; Purple
    5; Black
    6; Yellow

    When the user selects 'Red;Green;Yellow' in the multiselect field, I want QB to translate this (dynamically) to a list based upon the IDs.

    So in this example, I'd want the result to be '1;3;6'

    It don't need the returned IDs to be in any specific order.

    Any ideas appreciated.

    Thanks

    David

    PS Obviously this is easy to do by 'hard coding' the values into an if or case statement, but I really want this to work dynamically and be 100% data driven, so no maintenance is required.



    ------------------------------
    dmlaycock2000 dmlaycock2000
    ------------------------------


  • 2.  RE: Multi-select text to IDs

    Posted 19 days ago
    Are you willing to have the rid incorporated into the multi select field.  for example presented as

    Red (1)
    Blue (2)
    Green (3)
    Purple (4)
    Black (5)
    Yellow (6)


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Multi-select text to IDs

    Posted 19 days ago
    Hi Mark,

    Thanks for looking and thinking about this.

    I've found a relatively simple solution, which heavily relies upon QB's efficiency with formulae and relationships.

    1) Change the key field in the values table to the field with the selectable text values in (instead of RID)
    2) In the main table, create 20 fields, using the 'Part' function to create separate fields for each selected item
    3) Create relationships from the main table to the lookup table based upon these formula fields.
    4) Lookup the Record ID via the formula.

    Works a dream and needs minimal maintenance (what is required is a process to deal with when changes are needed to values in the shared field, but that doesn't happen often at all, and it's easy to create an exception report to flag it).

    D

    ------------------------------
    dmlaycock2000 dmlaycock2000
    ------------------------------



  • 4.  RE: Multi-select text to IDs

    Posted 18 days ago
    Yes, that is a good approach. I was going to suggest it if you were not willing to inflict the record ID being embedded in the multi select field.

    It's just a lot more work to set up but a better user experience

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------