Multiple Clairvoyant links between databases

From the department of when is there ever enough…In Pan 6 it was possible to set up multiple fields with Clairvoyant links to another database. (As posted here in June '17) The Relationships do well for a single field, but when more are desired it doesn’t seem like there’s a solution.

A simple example of the ambition is in the HobbyShopCustomers db used in Help. Suppose there are cases where I want to be able to enter an email address and have it autocomplete…

My particular need is in that customer IDs are used to create the relationship versus the various issues that can arise with common names like John Smith. From what I can tell, that relationship doesn’t allow me to have a Clairvoyance connection between the name field in the two databases.

In the majority of cases the name is unique so triggering the rest of the data entry via the relationship is fine. For cases like John Smith, it’ll be tossed back to the user to indicate which to use.

I’ve got good news, bad news, and kind of good news.

First the good news. You can definitely do what you want.

Now the bad news, there is no real user interface for this, and it isn’t documented.

Finally, the kind of good news – you can set this up manually with the Field blueprint. Let me do exactly what you are suggesting with the HobbyShopSales vs. HobbyShopCustomers databases, linking the Email addresses in these two databases.

To get this to work, I just have to set up three items in the Field Properties blueprint – CLAIRVOYANCE, CLAIRVOYANCEDATABASE and CLAIRVOYANCEFORMULA. Here’s what they need to look like in this example.

CleanShot 2023-08-10 at 12.53.50@2x

  • CLAIRVOYANCE - this needs to be on to enable the clairvoyance feature.
  • CLAIRVOYANCEDATABASE - enter the exact name of the databasse you want to link to. The spelling must be exact, including upper and lower case. Panorama does not check, if you misspell the name it simply won’t work, without any error message or indication.
  • CLAIRVOYANCEFORMULA - This must be the name of the field in the second database you want to link to.

You’ll notice that there is a CLAIRVOYANCELINK item, this should be left as “NO”. This item is used by the Relation dialog, you should not use it. (If you changed this to YES, the Relation dialog would “take over” the clairvoyance options for this field an override the manual changes you have made.)

The CLAIRVOYANCEFORMULA doesn’t have to be a field name, it could be any formula. For example if you wanted to link a field named Last in the current database to a field called Name in the second database, you might want to use the formula lastword(Name). You can also leave the CLAIRVOYANCEDATABASE empty and use the formula to customize how clairvoyance scans the current database.

There is aldo a CLAIRVOYANCEVALUES option that allows you to completely change how clairvoyance works. If this option is provided, Panorama won’t scan the database, instead it will only scan the items provided. For example for a Shipping field you could use:


The example above puts each value on a separate line. However, if there are no carriage returns, it will look for commas, so this example could also be written this way:


As you can see, this is all quite powerful, perhaps even going beyond the “department of when is there ever enough.” However, it’s also quite complicated and unintuitive. It really needs a user interface to smooth the way. I haven’t figured out what that user interface should be, so I consider this feature to be unfinished, which is why it is not documented. However, it does work perfectly, and I do not anticipate that anything described above will change once a user interface is provided.

But wait, is this really enough? There’s actually a bit more. If you look at the blueprint for a text editor object, you’ll see that there is a "$TextEditorClairvoyanceValues" property. This allows you to set the clairvoyance values for an individual Text Editor field, even if it is editing a variable! Again, there is no user interface or documentation for this (so far), but you can use the blueprint to set it up.

Ok … I think that’s enough! :grinning:

1 Like

I’m glad I asked and thanks for the prompt response - and for the very extensive details. I paused today to decide how to proceed on this very issue, so tomorrow I can just pick right up.

Can I quote myself from a post earlier this week? “That’s one of the great things about this forum.”

I’m glad this is at least written down somewhere now. To be honest, though I knew that this was in there, I had forgotten the details and had to figure them out to write this post. Which wasn’t super hard, but had I waited another year the details might have gotten even more fuzzy to me. Though I mostly figured it out by looking at the blueprint the relation dialog had set up (this feature was really implemented as part of the relation feature, but I went farther than relations needed).

Worked like a charm and it was easy. It undoubtedly took you far more time to write down how to do it.

This and a number of items from the forum are well worthy of being supplements to the Help file in some way. Possibly an index cross-referenced to the forum.

In this particular case, my plan is to create a proper user interface and document that. Though you found it “easy” in it’s current state, I don’t think that would be the case for most Panorama users.