So this year I am making a brand new app for our clients to use. This will have information on it about plants, their pests, how to harvest, etc.
Right now I am trying to set up some refs between the tables so that clients can easily navigate between associated information. For example, if they are on the Kale plant information detail view, I would like them to see refs in that view to kale pests, kale harvest info, kale recipes, etc.
Obviously, I have different spreadsheets for Plant information, Bug Information, Harvest Info, Recipes, etc.
Right now, in the Bug Info spreadsheet, I have a column titled [Plants Affected]. In each row is a list of plant families that are effect by the bug. There are mulitple plants within each plant family. And each bug affects multiple plant families. I would like to tell appsheet that if they find a plant family WITHIN this list, to form a ref to the plants that are in that plant family. In other words, I don’t want to have to give a new column to each plant family that is affected by the bugs because it would just be too long.
I have experimented with all sorts of virtual column expressions like SELECT, IFS, EXTRACT, etc., but my feeble brain can’t figure it out.
Also, just to make things more complicated, the plant [Family] is NOT the key in the Plant Information spreadsheet. The key is the [Plant]. So this whole thing will probably need to involve some derefs or something…
Happy to share more info. The main thing that is confusing me is the fact that there are many plant families listed within the same cell, and I am asking appsheet to separate them all out and form those info refs.