Hello all - I have been unable to find a solution (if one exists) to the following challenge. Any assistance is appreciated!
I have a single table "Samples" that stores various laboratory sample preparations. The columns for this simplified version of table are the following:
* sample_id
* made_by
* parent_sample_id (optional input, a lookup that links to another sample_id)
So you can image a world, where "Person A" prepares "sample a1", of which a portion is removed, split, and further prepared by "Person B" to generate "sample b1" and "sample b2". Next, a portion of "sample b2" is then removed, split, and further prepared by "Person C" to generate samples "sample c1", "sample c2", and "sample c3". Essentially, a tree data structure. This is very common in labs where samples are prepared by multiple individuals at different time points along the evolution to a final sample to be used in an experiment.
Generating the database was not an issue. However, I (my lab) would very much like the ability to filter a gallery result of a combobox selection where-by all sample records are listed that originate from a selected "root" sample. (all children, grandchildren, and so on....) So if "sample b2" was selected, then [ "sample c1", "sample c2", "sample c3" ] are shown in the gallery. Likewise, if "sample a1" is selected, then ["sample b1", "sample b2", "sample c1", "sample c2", "sample c3" ] are shown in the gallery.
I cannot figure out a method of doing this. The best I can find is just obtaining the immediate children of the selected sample (but not the grandkids):
Gallery items: Filter( Samples, parent_sample_id.sample_id = ComboBox.Selected.sample_id)
where by the items in the ComboBox are: Choices(Samples.parent_sample_id)
I was looking for a way of creating a while loop in which I could add in various samples as I recursively make my through the tree, but alas, I failed.
