Creating a place for test results (nested groups, filtered groups and more)

I have a YouTube channel where I cover what I think of a bunch of gadgets. Each gadget serves a number of purposes so I assign a number of tags to them. Then I create some tests per tag. So for a usb battery a test might be how long does it take to charge. And for a travel router it might be how long does it take to connect to the hotel WiFi. So one device could need both tests done and another only needs one.

So I have a table of tags and what they mean. I have a table of gadgets. And one of tests per category.

Finally I want to creat a table that lists the gadgets and then each of the tags that apply to that gadget and for each tag show each of the relevant tests. And list it all even if I donā€™t have results. That way I can see which tasks need to be run for each gadget.

So this is a bunch of problems I canā€™t solve. I cant seem to create a group by of tags that only apply to that gadget. I think normally I could figure stuff out but Iā€™m also taking care of a 7 week old infant so nothing complex makes sense.

1 Like

I guess the first question is this. If i create a lookup column and use it to group a table, how do i filter that lookup column to only show certain values. Evertything I have tried doesnā€™t change that lookup column. Its like filter doesnā€™t work on lookup columns.

Hey @Matt_Williams_technovangelist, thanks for the question! With regards to your group by of tags, if the gadget has multiple tags then the group by would show all gadgets that have those specific set of tags. You can also create a select list that filters out the list of gadgets even if that column is a lookup column. The key distinction here is that the select list would be populated by rows from the Tags table so that when you filter the Gadgets table, you are comparing a row in the Tags table with a row in the ā€œTagsā€ column of the Gadgets table.

I embedded a sample doc below to show some of these concepts (in addition to listing out all the tests per tag). Let me know if you have any questions!

Wow, thats pretty amazing. One more ask. I want to be able to record the answers for each test. so for each gadget that does wifi, how long does it take to complete the test. So each test would probably be on its own row and I could record the values. Is that possible?

@Al_Chen_Coda was faster than me, but hereā€™s the version I built out that I think answers your question re having a row per test result. Itā€™s a bit of a complex button, but happy to walk you through how it works:

the two of you are incredible. Seeing the two perspectives definitely helps clear a lot of things for me. OK, one more wrench to throw in here. The first time I get a gadget of one type i think I know everything there is to know about that type of gadget. But then the newest coolest thing comes out that focuses on something I never thought of. I want to add that test and I want to remember that I need to run the test on the four existing gadgets. The button is a neat approach but it means every test must be known before creating the tests, correct? Maybe just add another button to add the tests that are missing?

Actually I wrote the button with an AddOrModifyRows() function. That is a super-helpful action - often called an UPSERT to database folks. Basically all it is doing is ensuring that there is at least one row for every pair of {Gadget, Relevant Gadget Test}. You can try it - just add a test for one of the tags and then the buttons should turn green again, and then when you press them, it should add just that one test.

One other note while I am thinking about it: a best practice I would recommend is to set up sections with views for each of these flows. For example, you could write a section about determining tests per category, then another one about adding a new gadget to test and running the applicable tests, and perhaps another one for adding a new test and then retesting older gadgets.

1 Like

Iā€™m working on something which solves the problemā€¦ Itā€™s just not working quite right yet.

For some reason, itā€™s not matching correctly for the ā€˜Ten hoursā€™ result for the Powerbank. Iā€™ll keep playing with it, but perhaps either you or someone else in this thread can help me with the debugging?

1 Like

OK, worked it out. Itā€™s not as concise as Iā€™d like, but it should work.

I added conditional formatting for the gadgets with pending tests, and a checkbox to make filtering easy.

@Matt_Williams_technovangelist Yup adding a button that adds a row to your Tests table would be the way to get a new test added. You would then select the associate Tag for that Test (in the Tests table), and then any new gadget that gets added with that Tag will also show the new Test (in the ā€œAll Testsā€ column in the doc I showed).

As @shishir mentioned, setting this up in multiple sections will help you organize the workflow better versus having all your tables and views in one section.

@Joe_Innes that is definitely an interesting way of solving the problem. Very clever formula to get the Test results!