I have table one with these records:
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:
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?
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)
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.
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
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 🤷♂️)
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.
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:
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)?
Update: I got this to work by adding quotes around data in the fields I'm comparing, so now my list is:
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.