Forum Discussion
How to use calculated field for a relationship (genus and species)?
XPS35thank you, that worked! I had no idea you could have a relationship based on two fields.
BUT I've downloaded another table from the USFWS that has endangered species but with both genus and species in one field. So it would still be helpful to be able to combine both to one field to join with data that is structured that way.
- To the contrary. Putting two values in one field is bad design. Correct THAT table design flaw in a temporary table or query before importing its data into your table, which is more appropriately designed.
- It's two values but can be one value. Genus and species are commonly referred to as Scientific Name and presented as one string. In any case, I did separate it using ArcGIS Pro which lets you use Python to parse text very easily
Unfortunately, that's not the most effective approach for relational database design, though.
A genus can and does include multiple species. It's form of category and sub-category. And that calls for two discreet fields in the context of a relational database.While it is true that describing any given specimen can involve the scientific name stated as both genus and species, that alone doesn't rationalize ignoring the importance of good table design. Most cultures assign two or more names to individuals, commonly referred to as "first" and "last" names, plus other variations. Here again, the preferred status is to create two or more fields for those two or more names.
In other words, the rules for good database design are based on how well that design supports the work required of the database. Your problem here is a strong case in point. When a source fails to observe good design principles--putting two pieces of data in a single field--complication and extra work are generated. Not that it can't be done with sufficient additional wads of compensating code. But good table design minimizes the need for that additional code wad.
