Generate list of unique choices from linked table
J

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

Ninox Profile

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.

Ninox Profile

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 Profile

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 🤷‍♂️)

Ninox Profile

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.

J

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!

J

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

Reply