0

Generate list of unique choices from linked table

Hi, 

I have table one with these records:

table 1

a

a

a

b

b

 

I'd like to create a field in table 2 that allows users to choose one of the options that shows up in table 1, but I only want it listed once:

table 2

a

b

 

I've tried playing around with "unique" in the contraints and the "show as" settings for a linked table, but I haven't figured it out yet. The field in table 2 can either be a dropdown "choice" field, or simply a linked table, but either way I'd like the options in table 2 to automatically populate with a unique list of based on data entered in table 1. Is there a straightforward way to do that?

Thank you,

Jay

6 replies

null
    • Choices_Software_Dean
    • 4 yrs ago
    • Reported - view

    Try creating a new database with a Table1 and Table2, each with one text field named Text. Then in the form view of Table1 add a button with the following code:

     

    let x := Text;
    let t := (select Table2);
    let v := t.Text;
    if index(text(v), x) = -1 then
    let p := (create Table2);
    p.(Text := x)
    end

    Try adding your above values in Table1 and clicking the button after typing each value. The button should create a new record in Table2 only if the value is unique. Then create your relation to Table2 that can act as a popup.

     

    The above has not been fully tested, but hopefully it will help.

    • Sean
    • 4 yrs ago
    • Reported - view

    Another possible option would be to add a Yes / No field, named "Unique" for example, to "table 1" and select Yes for each of the unique records. Then you can use the constraint b.Unique = true

    • Ninox partner
    • RoSoft_Steven.1
    • 4 yrs ago
    • Reported - view

    Yes Sean, but then you wouldn't get the records which are tripple A or double B. Only the records where A or B apear only once(unique). If that is what you mean i suppose...(Why recurring records in a table in the first place would I think 🤷‍♂️)

    • Sean
    • 4 yrs ago
    • Reported - view

    Steven, I misstated my solution and decided not to correct it until there was a response. What I should have said was you would check only one of multiple values and all of the unique values. Thank you for prompting me to correct it 😉. I don't know the why of it.

    • Venture to Market LLC
    • Jay_Holman
    • 4 yrs ago
    • Reported - view

    Thank you for your replies. Per your recommendation I created a separate table that has each of the values in it only once, and I'm using that to generate my list using a "contains" function in the Constraints for the field. My problem now is:

    I can't figure out how to do a contains that requires an exact match. Some of my fields are the equivalent of:

    List

    a

    a

    ab

    ab

     

    If I say contains(list,"a"), it gives me both "a" and "ab". Is there a way to do a contains type function that requires an exact match (i.e. some databases have an "IN" function for checking whether a value is in an array or list)?

     

    Thanks again!

    • Venture to Market LLC
    • Jay_Holman
    • 4 yrs ago
    • Reported - view

    Update: I got this to work by adding quotes around data in the fields I'm comparing, so now my list is:

    List

    "a"

    "a"

    "ab"

    "ab"

    But that was not the most elegant solution in my opinon. I'd still love to know if there is a way to do an exact match to find an item in a list or array, or to request that feature if it's not currently available.

     

    Thanks

Login to reply

Content aside

  • 4 yrs agoLast active
  • 6Replies
  • 2149Views