Make an external link to look up a term in your data

AppSheet Tips & Tricks
, ,
1

I’m building an app based on a list of about 8,000 English words that students of English should know. Detail views in the app have hyperlinks that take the user to external websites where the key term will be looked up automatically. In this little tip I’d like to show two ways of doing this. One relies exclusively on virtual columns and the other only uses real columns. I’m assuming that the second method would yield better performance, particularly on devices that are not very powerful, but I hope to get that confirmed by any experts who might do me the favor of looking at this tip.

Method one: Hypertext virtual column

Make a virtual column with a formula such as the following:

. . . to look a word up in Merriam-Webster’s online dictionary

HYPERLINK(Concatenate("https://www.merriam-webster.com/dictionary/",[Your word]),"Merriam-Webster")

. . . to look something up on Oxford Learner’s Dictionay

HYPERLINK(Concatenate("https://www.oxfordlearnersdictionaries.com/definition/english/",substitute([Your Word]," ","-")),concatenate("Oxford Learner's Dictionaries"))

Note that any spaces will need to be replaced with “-” on this website if you have two or more words separated by spaces. Space separation is handled differently on different sites. You might need to use INDEX(SPLIT([Your Word]," "),1) to select just the first word some sites. Or, you may need to replace spaces with “+”. You just need to experiment with the URL to see how your target site works.

. . . to do a Google Image search for your word

HYPERLINK(concatenate("https://www.google.com/search?hl=en&q=",[Your word],"&source=lnms&tbm=isch"),"Google Images")

. . . to do a Google news search for it

HYPERLINK(concatenate("https://news.google.com/news?q=",[Your word],"&hl=en"),"Google News")

All of these virtual columns will make linking actions that you can use in your app. Moreover, if you adjust the formula in your virtual column, the behavior of the action will be adjusted automatically.

Method two: Avoiding virtual columns

If you make an action directly (no intervening virtual column), you can use the CONCATENATE() part of the formulas above in the “URL Target” slot. Then, if you know, for example, that you always want the text “Merriam-Webster” to show for that dictionary link, you can put the text “Merriam-Webster” in a real column and then put your action on that column as a Display Inline action. This completely eliminates the virtual column, making everything either a real text column or an action.

Confirmation please

The virtual column method seems to be the conventional one. AppSheet, for example, has a sample app that shows how to make an external link with a virtual column:

https://www.appsheet.com/samples/How-to-link-to-a-URL-with-custom-text?appGuidString=3f55edfc-aa53-47fc-a206-469720f10b13

However, my app has about 8,000 words to be looked up and links to about 25 different external resources per word. The app seems to work well on my computer and on my iPhone (even my legacy iPhone 6) but it’s so slow as to be unusable on my Android device. My question is: On an app with many rows and many links, can I expect significantly better performance from method two than from method one? The number of real columns in the table will increase (I need them to “hang” my hyperlinks on) but the number of virtual columns will decrease by the same number.

1 Like
2

Kirk_Masden:

On an app with many rows and many links, can I expect significantly better performance from method two than from method one?

Both are bad. Consider instead an action button.

3 Likes
3

Steve:

Consider instead an action button.

Thanks! I thought the second method was a sort of action button (the link). Do you mean that I should make an action that writes the URL to the sheet?

I thought I would put a CONCATENATE() expression in an action. Does that wind up being the same as a virtual column, in terms of efficiency, even though I’m not technically using a virtual column?

4

Steve:

Both are bad.

I’m glad to know that I need a different approach.

I wonder if I could request that my “Tips and Tricks” be changed to a normal question. I thought that my second approach would be a better one because it doesn’t use virtual columns. But, since I was mistaken, I think it’s better that this not clutter up the “Tips and Tricks” category. Thanks!

Postscript: After further discussion I think it’s clear that my basic idea (avoid virtual columns by putting the expression in an action) was OK after all. So, maybe this can stay a “tip” for now.

5

Kirk_Masden:

Thanks! I thought the second method was a sort of action button (the link). Do you mean that I should make an action that writes the URL to the sheet?

I’m suggesting an action to navigate the user to the definition page, of type External: go to a website. I don’t really see any need to write anything to the table.

3 Likes
6

Kirk_Masden:

The virtual column method seems to be the conventional one. AppSheet, for example, has a sample app that shows how to make an external link with a virtual column:

I’ve noticed that many of the official sample apps from AppSheet are NOT built using good practices.

Take the “Workstation Tracker” sample app, inside I’ve found the following “bad practices” on one table alone

3X_6_9_6953d281018a39c8dd96a11d6fbfac12ed6ef039.png
3X_6_9_6953d281018a39c8dd96a11d6fbfac12ed6ef039.png1281×835 84.9 KB

  • The RED outlined VCs shouldn’t be VCs
  • The GREEN outline VC should use a (slice)
  • The “Related Distance Checker” columns don’t need to be REF’ed

The RED outlined VCs shouldn’t be VCs

  • They should be physical columns

This is because they don’t contain data that changes dynamically; the data they contain only change based on changes to the record.

  • When you’ve got data points like that, you don’t store that computed value inside a VC - you store it in a physical column with an app formula.

The GREEN outline VC should use a (slice)

The Occupied column uses a brute-force SELECT() (ported into a FILTER formula) to pull any ongoing workstation checkouts.

  • This should instead be a REF_ROWS() to a slice of the ongoing workstation checkouts.

When you implement a (slice) to create a sub-set of data like this, AppSheet uses internal systems to increase efficiency when doing all the “sorting which child records go with which parent.”

  • If you leave it with a brute-force SELECT() like that, you’re forcing the system to work harder than it needs to

The “Related Distance Checker” columns don’t need to be REF’ed

I assume the “Distance Checker” table was implemented before enum base-type refs were a thing. The references on that table should be replaced with enum columns to remove the unecessary reverse references inside the workstation table.

These are the problems I’ve found with just one table in one sample app.

I wouldn’t assume that what you see in an “official sample app” is necessarily the “right” way to go

  • Same can be said for “unofficial” sample apps too

That being said… I agree with @Steve - using actions for your external links is definitely the way to go.

5 Likes
7

Thanks to both @Steve and @MultiTech_Visions for your helpful responses.

2 Likes