Can not join a shared database

RAmeeti · January 30, 2021, 3:59pm

Most everything I do is with shared databases thus I was first disappointed, then confused when I got this error:
Image 1-30-21 at 7.35 AM
One of the features sought after in the Join was the use of Remote Clairvoyance which does appear to be working (in part). Here is my Join Dialog:

Previous to the Join, when I was using Clairvoyance in just the Calendar db, if I typed ‘Rob’ in a Crew line item field and there was only one person’s name starting with ‘Rob’, it would suggest Robert Ameeti as that name existed in a CrewΩ field.
After the Join, I expected the same to happen but when I typed ‘Rob’, Clairvoyance only suggested ‘Robert’ (not including the full formula of ‘Robert Ameeti’ that I declared in the Join dialog.
But regardless of the incomplete Clairvoyance, it did tell me that the Join had in fact worked even though the Calendar file was shared (contrary to what the error dialog stated)!

So 2 items given my Join dialog: 1) Should Clairvoyance suggest both the First & Last name as it exists in the Sailor db, and 2) Can a shared db use the Join dialog successfully regardless of the error noted above?

Lastly, can I bring over the data from the STT field from the Sailor db, but not bring over the Notes data from the Sailor db?

RAmeeti · January 30, 2021, 4:48pm

Ok. Further testing… The Join did work regardless of the error noted above.

FWIW: If I use the FindRelated statement, while the Datasheet is active, if the current field is one of the Line Item fields (a Ω field or Crew1, Crew2 field), it will make the Sailors db active and Find the record given the data in that particuar Line Item field. If the current field in the Calendar db is not a Line Item field, FindRelated will find the person whose name is in the Crew1 field as a default for what it thinks I might want found.
And the FindRelated does work on the Join Formula of CrewΩ=First+" "+Last as desired. So the Remote Clairvoyance is merely not bringing over the complete name as expected.

admin · January 31, 2021, 3:18am

I’m going to be a bit pedantic here – that is not the Join dialog. It is the Relation panel of the Database Options dialog.

I’m pointing this out because there in fact is a Join dialog, which is accessed from the Import submenu of the File menu. I will be discussing this dialog in detail on Tuesday.

So there is a reason for my being picky – it’s so that everyone will be on the same page as to what dialog is being discussed.

What dialog precipitated this error? I would expect this from the Join dialog in the File>Import menu. You cannot do a full database join on a shared database. After I discuss this on Tuesday I think it will be obvious why this is the case. On the other hand, joining a single record (as during data entry) works fine with a shared database.

I’m guessing you did not use the Join dialog in the File>Import menu, since I think you don’t know about it. So please describe exactly the steps you took leading up to this error message, since this message should not appear unless you have coded a join statement, or used the File>Import>Join Panorama Database with Current Database dialog.

My session on Thursday was pretty long and I went thru the topics quickly, but I did cover this exact situation near the end of the session. You might want to review that – look for slides with the title Enabling Remote Clairvoyance™ with a Custom Key Formula.

This topic is also covered in this help page, look for the section Using a Custom Key Formula in One or Both Databases.

Bottom line, yes, this can be made to work, but you need to set up the source formula for the CrewΩ field to get it to work. Panorama cannot do this automatically when you have one field in the current database matched up with multiple fields in the source database.

By the way, I am also going to be discussing using relations with line items on Tuesday.

Yes, but you need to turn off the Automatic Matching Fields option to do this, then manually bring over just the fields you want.

This post had quite a few questions – I hope I answered all of them.

RAmeeti · January 31, 2021, 5:21am

I do appreciate knowing the correct name for it. Thanks for that.

I am thinking that my first dialog shot does show that I had created the relation between CrewΩ (which I want to receive Robert Ameeti) links with First+" "+Last in the Sailor db. This is in the Key Options section. When I entered ‘Rob’ it did find the only matching First Name but did then only bring across the First Name and did not bring the Last.
I do believe that I set the relation correctly as when I later used the FindRelated, I could have it find the correct record in Sailors when I had two different Crew listings, one of Mary Bacon, and one of Mary Dawson, so it did look at the Last field in the Sailors db to know that the record in Calendar needed to match both First & Last in Sailors. Thus I was expecting it to bring into Calendar the Last along with the First.
After the Relation panel was completed as shown above (including my having unchecked the Automatic Matching Fields checkbox, I got the error by using the Test Keys button. That gave me the error.
Separately in the BitBucket, I did request that the ‘phantom text’ that appears in the text boxes immediately go away as soon as someone starts to enter text as it make it hard to read (as shown in the screen grab above).

admin · January 31, 2021, 9:14am

Aha, perfect, thank you, now I have the information needed to fix this. For now you will not be able to use the Test Keys feature with shared databases.

I’m not sure if you are saying that you still believe the relation is correctly set up. Since you kindly provided a screen shot, I can definitely say that it is not quite set up for the Clairvoyance to work. Additional manual setup is required when you are using multiple keys in either database. This is explained in the documentation and was covered in the session on Thursday. I’m thinking I may repeat that part of the session as it is a bit tricky, and I think I may have covered it a bit too fast.

Let me guage the interest on this – how many of you would like me to go thru this topic again a bit slower?

Remote Clairvoyance with Multiple Key Fields

I understand it, no further explanation needed.
I’m not fully clear on this, more time on this topic would be helpful.
I missed this topic entirely.

0 voters

This is a general problem with Text Editor objects and tab panels, there is at least one other long standing BitBucket issue about this (so I’ll probably mark this as a duplicate). Unfortunately, there appears to be no simple fix, or even a moderately complex fix. I believe a fix is going to require a complete rewrite of how text objects work, most like a project that will take 2-3 months. I think this will possibly clear up some other issues with Text Editor objects, so it is definitely on the to-do list, but obviously this cannot be done at this time.

In the meantime, you can always clear up the appearance by clicking to another tab and then clicking back to the Relations tab. Maybe for now I should make the Database Options dialog always open to the General panel.

RAmeeti · January 31, 2021, 3:55pm

So the answer is…
If a user is expecting to use Remote Clairvoyance from the Source db, they must have a Source Data Transfer Formula on the left side (circled in red) even if it is the exact same formula as a Custom Key Formula that is already linking the 2 databases and those fields.

The missing item is shown circled in the pic below.

I did uncheck the ‘Automatic Matching Fields’ checkbox but the phantom text showing the automatically matched fields did not disappear as noted in the Help file.

IF the Automatic Matching Fields is unchecked, and the user has not selected any Sources for any of the fields in the current db (as when the red box is empty), then it should probably display an error when exiting this dialog as there are no fields receiving any data (if the Key Field formula is not good enough to be used.)

Having unchecked the Automatic Match option, I had felt comfortable that all was done as needed since the formula had already been entered in the Key Formula as a Custom Key figuring that it would be used, and did not need to be replicated.

ie. I had selected Remote Clairvoyance AND Custom Key Formula AND NO Source Data Transfer Formula == ERROR CONDITION (or at least a warning.)

admin · January 31, 2021, 9:43pm

Yes, that is the answer I was trying to lead you to. However, this formula is only necessary if there are multiple key fields. If there is just one key field in each database, Panorama will figure this out automatically. Also if there are multiple key fields but they have the same names, Panorama will figure the formula out automatically.

I think it might be possible that in this specific situation – one key field in the current database, multiple in the source database, Panorama might be able to figure out the formula automatically since as you point out in this particular case it is the same as the key formula. I will have to look into this. However in the general case of different numbers of key fields between the source database and the current database, Panorama has no way to figure out what the formula should be (in that case the needed formula is different from the key formula) so you have to provide it explicitly.

The “phantom” text doesn’t actually disappear until you drag at least one field from the source database to the current database. You said you wanted to copy the STT field – you should drag it over, then the automatic fields will disappear.