How do I manually link references to a specif...

[Rogerio_Penna]

How do I manually link references to a specific column instead of just the table?

Have a table where I must select a company, then a job, then the name of an employee.

Have another table, where I have the companies, jobs inside it and employess names.

So, 2nd table is like

Company 1 - accountant - Mike Company 1 - accountant - Josh Company 1 - driver - Paul Company 2 - janitor - Aaron Company 2 - driver - Anton

Ok, the catch is that I need to not only select company, job and employee, but also add new ones of each, if needed. Thus, I guess ENUM lists are ruled out.

That means I need to use columns of the REF type on the first table.

So I have this column named Company. Made it ref.

It shows: Company 1 Company 1 Company 1 Company 2 Company 2

instead of just “Company 1” and “Company 2”

I select Company 1.

Now for jobs… I also made it a ref.

It shows: Company 1 Company 1 Company 1 Company 2 Company 2

Why is it showing the Company column of the 2nd table? Tried all sorts of combinations of key and label columns.

It keeps pointing to Company column.

Same thing with the Employee Name column. Also is referencing the Company column of table 2.

Is there any way to say exactly what column I want to reference? The referenced column is always grayed out, chosen automatically by AppSheet. Why can´t we users choose it, so I could match references from Table1-Company to table2-Company and Table1-Job to table2-job, etc?

[Rogerio_Penna]

edit: can´t understand sometimes AppSheet automatic behavior.

I turned the Company column from Table 1 into an Enum. Tested. Turned it again into a Ref to Table 2.

Now, miraculously, it shows only Company 1 and Company 2 instead of repeating each one for each row they appeared.

[Rogerio_Penna]

just to show how weird this all is…

I am changing all columns from table A that were refs to table B, to ENUM type, then using VALID_IF, saving, and changing again to REF types to Table B.

Now COMPANY shows only Company 1 and Company 2 (even so Table 2 has Comp1 and Comp2 repeated multiple times)

And when I select a Company, only the jobs listed UNDER THAT COMPANY show in the next Ref column.

And so far I did not test it as ref, it’s using an ENUM type, but Name column is only listing employees

under that Job and Company selected previously.

So, what’s so weird?

That JOB column is not referencing to Table 2. It’s referencing to ANOTHER TABLE where there is absolutely no column named Jobs!

[Rogerio_Penna]

Changed them all again to REFs, including Employee Name column.

And all is working FINE, except its completely mind boggling that Job column is refering to ANOTHER table (the main Form of the app) and this table has no column named Job or the list of jobs, and yet, it’s showing the Jobs from Table 2 (which it is NOT being referenced!) and even being used to narrow down Name column!!!

[Adam-google]

Hi @Rogerio_Penna, a lot to digest here but broadly speaking Refs are by definition references to tables, not individual columns. Ref column values are always associated with the key column of the referenced table because the key uniquely identifies the row. The values that appear in the dropdown by default are determined by the “row label” setting. Identical options are not collapsed because they still correspond to unique rows. If you put a Valid_If on the Ref you can override the default dropdown to show a different table/column (maybe this is how you’re seeing jobs there?), but this is usually a bad idea because the values will all become broken refs.

So how to fix it? If you use Valid_If with an Enum, you can get dependent dropdown behavior more easily (where each selection filters the options in the next step) or show all options in each step, but it won’t let you add new options and it will collapse identical options. If you want to be able to add new instances of each item, you would need to either use static EnumOptions without Valid_If, or make each column a Ref to a separate table. You could have a companies table, a jobs table, and an employees table. From there it’s possible to set up “dependent dropdown” expressions manually on the Refs if you want that behavior.