I don’t quite understand why I am seeing two different kinds of behavior from very similar ARRAYUNIQUE rollup fields.
I am making a database of health issues. One table is Substances (nutrients, herbs, medications, etc), another is Issues (diseases, problems, or symptoms), and then I have a Junction table where I can select a Substance and an Issue and add in some other information (how/why they’re connected, along with references).
On the Issue table, I want to be able to look at the record for an Issue and see all the Substances associated with it. So I have a Rollup set to ARRAYUNIQUE the Substances it looks up, because while I may have multiple Junction table records connecting the same Issue to the same Substance, I only want to see the Substance listed once. (For example, I may enter in many references in the Junction table for carnitine correcting exercise intolerance, but when I look up the exercise intolerance record in order to see what may be helpful to someone, I only need to see carnitine listed once.) ARRAYUNIQUE behaves as I want it to in this table: it eliminates duplicate mentions of Substances so I get a clear list of things to consider.
But for some reason, the same type of ARRAYUNIQUE rollup on the Substance table does not eliminate duplicates. So using the carnitine example, let’s say I want to look up all the Issues associated with carnitine. This is what it returns:
“Muscle weakness or paralysis,Hypoglycemia,Hypoammonemia,Cardiomyopathy,Muscle pain,Muscle weakness or paralysis,Intermittant claudication,Exercise intolerance”
The string “muscle weakness or paralysis” comes up twice because I have two sources in the Junction table explaining different aspects of this.
Does it have something to do with spaces being involved? When I have seen duplicates be successfully removed on the Issues table, no spaces were in the Substances associated with it.
Why is this happening, and how can I fix it? I need to have as much evidence on hand as possible (health issues can be high stakes), so it’s not feasible to simply not have Junction table entries repeating the same connections or fleshing out different aspects of the same connection. It is not feasible for me to combine everything into a single record, either. But it seems the more evidence I enter to back up connections, the more unusable the Substances table will get, because the number of repetitions of Issues means I can’t skim a list of Issues and narrow down, among a list of Substances, what should be tried first; the lists will get way too long and repetitive and I will overlook important things. I really need the database to eliminate the duplicate mentions for it to be useful in any practical sense.
