Hi,
I created a table of parts that run on a specific production line. I would like the user to be able to first select the ID in a single-select (already in my app). Once they’ve selected an ID, I would like only the color options for that ID to be in a 2nd single-select.
I would like Color 1 through Color 9 to be available options in a 2nd single-select, but be specific to the ID selected in the 1st single-select.
You can do this by adding some array actions on a trigger for the first single select widget that’s loading the ID table records.
Add a “Push onto Array” action for each color column, 1 - 9, to assign each value to a text list variable.
Then you can use that text list variable as the data source for your second single select widget to pick the color from that list
This is going to add blank records to the dropdown for your IDs that don’t have records in every column though.
If that’s a problem, you might have to split the Push onto Array actions out into individual triggers so you can set Is Not Blank conditions for them independently. There might be a more elegant solution to just trim the blank values off the color list, but that’s the best I can think of right now (:
Hi @codygarcia,
here is another Idea.
This is a similar approach to @willis.hance but in a single Expression and it ignores all null values by default:
Simply put this Trigger on your Single Select for the ID.
This selects the record and puts all existing values into a string (the link() makes sure to replace all null values with an empty string. Otherwise the whole string would bekome null)
This string is comma separated (feel free to use another character here, but make sure it is not part of any of the values).
Then this comma separation is used to make it an array, which is used for the second single select (Value / Color)
Beautiful! I knew there had to be a more elegant solution. That’s going to help me out on something I was already working on, thank you!
Looks like the reason your having that error is because your splitting the sting on “,” but inside your link function your not including it.
It should look something like link(‘,’+data,‘’) as outline in the previous post.
So you need to include your delimiter selected between your data sets.
Yes @erikheckler is right.
You need to add a ',' + from the second value on…
...+ LINK(',' + @Table record.Silver Line Parts. Color 2, '') + ...
I made the changes so here is the trigger expression for Part 1 Color.
However, I’m still getting an error. For reference, here is how I set up the drop-down called “Select Part 1” and it works.
Please let me know what I should do next in order to have the Part 1 Color properly display the desired table data for when a part is selected.
Thanks!
Your color selector seems to be fine.
Would be good to see the Place Holder Values for that record.
Also you could try to copy that same expression into `
`Data manipulation`-> `Expression` -> `Variable`
(any text variable)
And investigate what the text looks like…
It should be like: color1,color2, color3 e.g.: WH,CV,SD… Or just one value as WH
If this was me i’d honestly have the entire table structured differently.
You suggest to have all the color codes in 1 field seperated by comma. This is what I would do.
You can then avoid having the unecessarily parsing a ton of columns into one string which then needs to be parsed with a delimiter.
It also add flexibility having it as one column / one string with , delimiter. Imagine if you have over 200 color combinations… you’d go over your max column allowance for a table and you’d have to re-structure all your triggers every time you add a new column.
Just put the whole list of all color combos per ID into one column seperated by a common delimiter like , and parse it that way. Much simpler.
My approach is totally respecting your current Table structure and should work with that.
I rebuilt your structure and it works fine:
The suggestion of @erikheckler to completely structure the table different is valid, but in my opinion not universal. It depends on the whole use case and the way you work with that.
If this szenario is the only job of the table, then you could also built a Table like that:
| Id | Silver Line Part | Color |
|---|---|---|
| A7fK9mQ2ZxR4 | Part1 | WH |
| bT3P8WnL0sJH | Part 2 | WH |
| 5xMZQ2E9aV7R | Part 1 | SD |
| K1wF8p3N6JAH | Part 1 | CV |
| 0RZbS7LQm8H4 | Part 1 | TT |
Then an aggregation on the Part column with unique values could provide the first dropdown and an aggregation on the color column (query filters by the part) delivers the second column.
However, this is only safe for up to 1000 records and has less overview over the available colors in the table itself.
Also the approach to store everything in a comma separated string is also a good idea, but you have always to extract the actual values since they are not provided isolated.
So it depends on the use case and your experience.
The current approach should work. I actually wondered about the values in the running app, not the table itself 
It is a bit hard to debug with only these screenshots 
