The best approach when referencing back to another table - searching while avoiding yellow triangles

[casitasrd]

I have a table call units and another table where i can select a unit from a column referencing back to that table call units. In order to make it easier to find, pick and differentiate between one unit from one project that may have the same unit number in another project, i've made a virtual column that concatenate the unit with the project and made it the label column. The issue is that I now have Unit and project name in the column where im referring from, and just want the Unit. What would be the best approach when tackling this kind of situations?

Creating another column to auto compute just the unit and hide the one with both unit and project? or is there a better approach to this?

What do you think about appsheet letting you pick 2 two labels so that when you reference back you can see more info on that? would this create caos in the backend?

[TeeSee1 · Accepted solution]

I understand the issue you are facing but at the same time a bit confused about the following. 

The fact you have a concatenated field seems to indicate that you differentiate between two units of of the same number that are associated with different projects. Or if the units are the same, why do you even need to see projects in selecting a unit?

How does the user typically select the right unit? Choose a project first and then pick a unit from a list of the units associate with the project?

I do not mean to offer any solution at this stage (or I could not even offer one unless I know more about how the user typically navigates thru your app) but curious.

[TeeSee1 · Accepted solution]

I understand the issue you are facing but at the same time a bit confused about the following. 

The fact you have a concatenated field seems to indicate that you differentiate between two units of of the same number that are associated with different projects. Or if the units are the same, why do you even need to see projects in selecting a unit?

How does the user typically select the right unit? Choose a project first and then pick a unit from a list of the units associate with the project?

I do not mean to offer any solution at this stage (or I could not even offer one unless I know more about how the user typically navigates thru your app) but curious.

[casitasrd]

@TeeSee1 We pick the unit first then the Project column auto computes to the project associated to that unit.

I think I like the idea you just gave me of picking the project first and the finding the unit by searching through a list of units associated to that project. Is that possible?

I'm going to work on that and hopefully not break anything. 

[casitasrd]

@TeeSee1 I broke everything by trying to accomplish to select a project first, then a unit by selecting from a filter list by projects. Can this be doable before I break anything else? 😅

[TeeSee1]

You provide a dropdown list of projects.

You can then create a dropdown list of units based on the selection on the project column. 

This is doable.

[casitasrd]

Can you do it as a reference type? In order to keep the auto REF columns you need to keep the reference type correct?

[TeeSee1]

ref columns always point to the key of another table. I do not know how your units and projects are set up. If they each have a table of its own, you can use a ref for both of them (I had recently did a Q&A where the person had only one table that managed group/sub-group combo. so the group and sub-group cannot be referenced).

So the answer depends on how you have yours set up.

[casitasrd]

ok great. I got the formula written and it's working. Now, how do I make it a multiple selection instead of 1 selection of the list.

Remember I'm using the column data type REF

[casitasrd]

Just remove the LIST()😅 . But now the yellow triangle are back again in action!

[casitasrd · Accepted solution]

@TeeSee1 Thank you very much for your help. Ultimately I went for picking the project first, then the unit with a filter that only shows units corresponding to that project; using a type ref; referring back to the units table as you suggested. 

I fixed the yellow triangle by comparing list to list by IDs. Here is the formula fixed on this specific case:

FILTER(unidad, ([proyecto] = [_THISROW].[proyecto].[id]))

What this translate to is basically grab the units where their projects column value equals to the row I'm in project id value as well. I was missing the .[id] value at the end, without it I would be comparing between project ID to project name.