I am trying to lookup a column of values from a table dynamically based on other functions. I can easily get the correct column name for my table, but I am unable to get a Filter, Lookup, or anything else I have tried to return the values and not just a list of rows.
This (editable) doc shows what I’m talking about. Feel free to play around in there and hopefully you can tell me what I’m getting wrong.
Thanks,
Peter
to get a value out of specific column after filter/lookup etc, you could use . for eg. Table1.Filter(<critieria>).Column1 would first filter set of records from Table1 based on critieria you supplied and then create a list of values from Column1 using that filtered set of records.
Thanks.
Thanks for the reply!
The problem I have is that I don’t always want Column 1, so I need to be able to dynamically choose Column 1/2/3 (ideally within the Filter or whatever function).
The result of this function that I’m trying to make is for a Select List which will have different options depending on other criteria in the app.
I was thinking I might be able to do this with yet another table that dynamically links to different columns and then I can always select “Column 1” of that table. I’m not sure if that will hit the same issues or not though.
Peter
Could you please tell me more about your scenario. Dynamically picking column isnt something supported straightaway (you could always use SwichIf function to switch on your criteria btw). so wondering if i could hear the scenario and could give you an alternative.
I updated that test doc to be closer to what I’m looking for. The end application is to have a single UI with multiple options that sort of progressively filter down. So the first selection the user makes limits the choices in the second, then that selection controls the third…
Instead of having ~100 items in a Select list to scroll through I can have a Select with 5 high-level options, then one with 4 mid-level sub-options, and finally only 5 of the original 100 items that are based on the first two answers.
okay got it, you want a cascading controls - classic example is Car Manufacturer, Car Model, Trim - where each select would reduce down option in other one. let me build an example and put it here.
That’s correct, and a good example!
I saw your Section 2 solution and that will work, although I’m worried about the scalability.
Is there another data format that would work better? Having the data in labeled rows and trying to use “thisRow” lookups? I didn’t go down that road yet because I figured this column select would work somehow I just wasn’t seeing.
I can sort of morph the data however ahead of time, but I’m hoping to avoid having to make a new Table/formula entry/etc. for each new list item I add. In the end the user will be able to add their own items to the list and I don’t want to make them edit a formula or know to copy and paste a whole structure to put data into (although maybe I could formify that?).
Thanks!
Peter
@Krunal_Sheth Made another section in the example doc linked that fixes my concerns from the message above. So now there are a couple of solution options shown in there.
Using the example of Car Company/Model/Trim the gist of the functions is:
Cars.Filter(Company=carCompanySelect.Value and Model= carModelSelect.Value).Trim.Unique(),
‘Select Car Company and Model First’
The table has to have a unique line for the lowest level with potentially repeating entries at the higher ones.
So in my ~100 items case the table may end up being ~500 lines or more, but that’s pretty easy to set up from the beginning and much better than the eye sore of a long list for the user.
Thanks again!
Peter
@Krunal_Sheth Is it possible to add back the example you mentioned here? There doesn’t seem to be a link or embedded file, and this is likely a solution to a current issue. Thanks!
Hi @Eter_Rodriguez,
I think all examples they are referring to in this topic are in the example doc in the top post. There are a couple pages in that doc.
Is there one you’re looking for in particular?
@BenLee Got it. I misinterpreted the part when he said “put it here.”
I’ve been able to open the file and am looking at the cars section, which seems to do what I’m trying to replicate.
Thanks for helping a newbie!
Glad its there and you’ve got what you need!
And welcome to the community! 
Hey all!
I’m struggling with a very similar problem and haven’t been able to figure it out from the above unfortunately 
it would be great if you could kindly help!
In short, I have two tables - one that has a list of columns with names like protein, carbs, etc etc, then I have a second table which has the same names but listed as rows.
My goal is to input a sorted list of the items into the rows, so for instance, Column 1 will say ingredient name, and column 2 will say the three ingredients that contain the most amount of that nutrient.
Since I’ll have around 30 columns (vitamins etc), is there a way to do this programmatically?
Happy to do it manually if needed, just really eager to get it sorted, so any thoughts you can add would be much appreciated!
Thank you!!
Here’s what I’ve got so far:
