My database has many fields which contain acronyms — abbreviations that the user may not know how to interpret.
So I want to add a tooltip to these fields that will pop-up and interpret the abbreviation.
To give an artifical example, suppose the content of field1 is "PRC", then hovering over field1 will bring up a tooltip that says "People’s Republic of China".
I also have an Acronyms Table which has all the acronyms that are used. In a field Acronyms::Acronym it has the acronym itself, and in a related field Acronym::Meaning it has the interpretation of the acronym, like:
Acronyms::Acronym = "PRC"
Acronyms:Meaning = "People’s Republic of China"
The Acronyms table is not related to any other tables in the database, though. As new acronyms come into use, I add these records to the Acronyms table, so the table is not totally static. (There are literally thousands of these abbreviations and they are frequently being modified.)
To get the tooltip to display the interpretation of the acronym, I would like to have a calculation that says basically:
“if the content of Table1::field is X, then give me the content of Acronyms::Meaning when Acronyms::Acronym = X ”
where, crucially, the two tables, Table1 and Acronyms, are not related.
I have no idea how to do this. Any ideas?
As a stopgap I have a button that runs a script — let’s call it “Interpret Acronym", and this script finds the appropriate record in the Acronyms Table and displays information from that record in another window. The user can then close that window after seeing what the Acronym means. But it would be so much easier to have this information displayed in a tooltip (faster for the user, plus my layouts are now cluttered with interpretation buttons). But I can’t run a script inside a caculation can I?
I have thought about writing a custom function that takes the acronym as its argument and returns the interpretation. Then I could just put this function in the calculation for the tooltip display.
But the problem is that this function will replicate the pre-existing Acronym Table itself, and needs to be dynamic, since the Acronym Table changes. I have in fact already had to do this for a logically similar problem, and it is a hassle. Again, the function is defined like a calculation, and I can’t run a script inside a calculation, right?
Of course it would have made sense to relate the records with the Acronyms in them to the records inthe Acronym table. Then I could proceed through the record’s relatedness to get the right value. But going back and adding these relationships now to all the records would also be a hassle. I suppose I could try to automate the addition of these relationships, but I would like to see if there is an alternative. It might also involve creating join tables, etc. etc.
Mine is a WebDirect implementation so there are certain limitations that I am operating with as well.
If anyone has any advice, I would really appreciate it! Thanks!
Late Edit: Could “ExecuteSQL” be the way to go?