Structure:
Table1
Name, Subname
Table2
Subname, Nickname, Description
Input:
Table1.Name 1
Table1.Name 2
Output:
Description WHERE Name1.Table2.Subname in Name2.Table2.Nickname
So let me try to rephrase this into non-SQL language.
You have two tables: Table 1 with 2 columns name & subname, Table 2 with 3 columns Subname, Nickname and Description. I assume the Subname columns in those two tables are related to each other.
You want to “search” with two inputs, which correlates to the name column in table 1, so basically 2 rows.
Then I’m not clear what you want to compare with the output to get the description. Can you explain?
You’ve completely understood me. So, what I want to see is all descriptions from table2 where subname from input1 matches nickname from input2. Let me show the example to be more clear:
Table 1
WhiteColorBox | White
BlackColorBox | Black
ColorBox | BlackAndWhite
Table 2
White | Black | Mix of white and black is very interesting
White | Orange | Mix of white and orange
White | Black | Use this mix for your pictures
Black | White | Description to be filtered out
Then I input two strings from table1 (ideally search and select from dropdown list):
WhiteColorBox
BlackColorBox
Then it shows me all matches from table 2:
- Mix of white and black is very interesting
- Use this mix for your pictures
If your second choice was “ColorBox”, what would you expect the output to be?
Is this what you are looking for?
Note that if you select ColorBox as both options, you get zero matches. I’m not sure if that is expected behaviour or not.
Anyway, to get the above:
- Write the two choices to user specific columns in table 1
- Create 2 single value columns in table 2, each of them fetching the corresponding user specific column from table 1
- Create an if-then-else column in table 2:
- If Nickname is sv1, then true
- If Nickname is sv2, then true
- Finally use an inline list and filter where the if-then-else is true
2 Likes
Nothing as no match found.
Many thanks for your answer. I will try to investigate it.
As for your picture, it works the wrong way.
- WhiteColorBox+WhiteColorBox should get empty result as there are no matches White+White in table 2. The same for BlackColorBox+BlackColorBox.
- ColorBox+BlackColorBox should get empty result as there are no matches BlackAndWhite+White in table 2
- BlackColorBox+WhiteColorBox should only get “Description to be filtered out”. It doesn’t seem logical, I understand.
Okay, so…
- Choice 1 should match Table 2 → SubName
- Choice 2 should match Table 2 → Nickname
- And both should be true
Yes?
To get that, you just need to adjust the if-then-else column. Instead of:
It should be:
- If SubName is not sv-1, then empty
- If Nickname is not sv-2, then empty
- Else true
After making that change:
2 Likes
Yes, now your picture looks correct but I cannot get how to implement it.
I created an if-then-else column in table 2 which does not contain column Subname.
I followed your original example:
Here is a link to the app I created it in, you can make a copy…
There are some extra tables, but just ignore those - this is just an app I muck around with to test things with. Your stuff is in Table 1 and Table 2
Yes, this was my mistake. I figured it out.
Thank you so much! This is really what I wanted to achieve.
BTW, if there are thousands rows in table 1… is there a search option in choice picker?
1 Like
Yes. If you use a choice picker in standard (non-segmented) mode, then you’ll get a search box.
That’s because you only have 3 choices.
Add more and it will appear.
1 Like
Hello,
Now when I have thousands of rows I have a problem with search.
The app is here: pills-app.net
I open a second tab where there is just a list of items and put Nurofen. As supposed I get results.
Then I open a third tab and in the first active choice box I put the same text Nurofen, I get just one row, not plenty of them as in the previous step. I expect to get the same results as both elements (a list of items in the database and active choice) have the same source database.
How many rows do you have in total?
In that list 17k
Can you show the settings for your choice component? I may be wrong, but I think the choice component will eliminate duplicates, because there is no use in showing the same choice multiple times. Most importantly, I’m curious what your Value and Display As settings are for the choice component. Do you have the Value set to column that’s the same for all Nurofren rows, so it’s only picking the first one to show?
hmm, so I assume the Main_component column is the second column you are showing in your screenshot? If my theory is correct, then that would mean that you should at least see two values in your list of choices, since what I see are two unique Main_component values. But it’s only a guess and doesn’t seem to match what you are experiencing. Is it true that the second column you are showing of your data is the Main_component column? I can’t tell, since you didn’t include the headers.
Are you sure you don’t have some sort of filter on your choice component? Are you limiting the number of items visible in the choice component?
If you believe that everything is set up correctly…based on what I’m seeing, I would expect the list in the choice component to be smaller, but only because glide may be trying to eliminate items that may be duplicates base on the column you selected for your ‘Value’ setting. Another thought is that the large number of rows are causing some bugs to surface.
As an alternative, you could consider creating your own kind of choices screen. This would require you to first have a new sheet with a single row that will act as your tab. You will need a template column that can be filled with the word ‘key’. Create a similar template in your pills table. Then create a single relation in your pills table that links the key template to the key template in the new table. Your tab will use the new table. Now instead of a choice components, you could have two buttons. The action for each button would be ‘Show New Screen’. On each screen, you would add an inline list. The action on the inline list would be to Set Column and write that Main_Component value through the single relation in the pill table to a user specific column in your new table. So basically we would get rid of the choice components and create your own with Buttons, New Screens, Inline Lists, and Actions. I think it would work very similar and actually might work better if the choices are reacting the way I think they are and eliminating duplicate values.
2 Likes
Yes, it is true. I’ve shown it on the screen, but the forum engine cut the preview, just click on the picture to open it in full size and you will see headers.
There is no filtering or limitation in the choice component.
It would be strange but in any case this guess seems to be incorrect as even in this example there are 2 different values that must be displayed while only one is. I also have examples where no data is displayed.
This seems to be true.
I would try to think of what you supposed next but how glide can fix what I described above?