Linking for beginner: relate datasheets that share one field

[KirbyKrieger]

Please help me get over this early hurdle in my use of Panorama (X — I have never used Panorama before).

At its simplest (I think), my question is: how do I link two datasheets that share a common field?

Note that I bring the mental prejudice of thinking that it makes sense to link my data at the datasheets that contain it. Further, this is for data entry and acquisition, not for retrieval.

As an example, consider the Checkbook created in the superb PanX tutorial (#301ff, found in Help). One of the fields used is “Category”. The tutorial uses the PanX feature Clairvoyance(™) to help the user populate this field and minimize data entry errors. Were the tutorial to continue developing the Checkbook database, it should cover making the Category field into its own datasheet so that the user could store additional information about each category (e.g.: tax relationship). How would that be done? How would the datasheets be linked so that the user could add fields to hold data about each category, limit categories in the Checkbook datasheet to records in the Categories datasheet (or force creating a new record in the Categories datasheet when entering a new category in the Checkbook datasheet)? (Again, this is just to have an example; I think the application of this kind of spawning of children datasheets that share a field is universal.)

I have sought a solution in the Help file and here in this forum. @admin says:

@admin

relational operations are performed “on the fly” with various lookup functions. So you don’t set up relations in advance, you simply specify what the relations are when needed. The basic function for this is the lookup( function, this allows you to specify that a single field in one database is related to a single field in a second database

@JeffK offered me this:

@JeffK

make a file of the 600 top level identifiers with unique IDs. Make a second file of aliases and link each alias record to its top level Identifier using their unique ID

I cannot figure out how the link is done (and am stymied at the conception of this as an “as-needed” operation rather than a structural member of the data-storage-and-retrieval system).

This has come up before, without, afaict, any resolution.

• How to relate two different db's
• Setting up relational database
• Invoice system with relation tables

I assume @billconable’s comment applies to me:

Exemple of invoice

James, I think part of your problem is that you’re thinking in terms of the tables and indexes and so forth that are necessary for a traditional relational DB. Panorama, both the old versions and the new one, doesn’t work that way. Since the whole DB is loaded in memory, there is no necessity for tables and indexes. You just look up what you want and it’s quick as can be because the whole thing is in RAM. If I understand what’s going on in this conversation, you’re looking for something t…

Thanks for your consideration.

[KJM]

You got to get that FileMaker type of thinking out of the way.

In Panorama (X) the lookup( functionality is wherever you need it. You can use the lookup( functions e.g. in formulas, when you are morphing fields, or when you write a formulafill formula in a procedure, or when you build the formula in order to fill a text list in a form.

I think it is best to read the documentation of the lookup( functions, and I strongly recommend Michael Kellock’s LookupWizard (in the Panorama Database Exchange) that helps to understand the function and to get the syntax correct.

[billconable]

Yes, Kirby, I think my comment does apply to you. In particular I would recommend you study the post statement. I have several situations where DB A looks up information in DB B, but if the lookup fails, then the procedure creates a new record in DB B and populates with information from DB A. Study the options for Post.

Think of something as simple as address info. An event DB looks that up in a mailing list DB; if it’s not there, I fill it in in the event DB and then post it to the ML. And there’s even a way that if the info (e.g a phone number) has changed, just the change is posted to the ML. The posting can be automatic if you set a flag variable for a change in the record.

I’d be glad to share some example procedures with you off-list, but I expect that you’ll be able to figure it out; I figured out the same kind of changeover on my own decades ago on the demise of the lamented Interlace.

Regards,
Bill Conable

[KirbyKrieger]

@billconable

In particular I would recommend you study the post statement. I have several situations where DB A looks up information in DB B, but if the lookup fails, then the procedure creates a new record in DB B and populates with information from DB A. Study the options for Post.

I can hear the wind whistling through an “Aha” moment somewhere around the next bend … your “Post” statement suggestion is very helpful for itself and even for letting me conceive of databases interacting in real time through statements like that instead of via an established multi-table structure. Thanks.

The situation you describe with DB A and DB B seems as apt as can be. I’m still befuddled that there isn’t a sample pair of databases available for apish learning. Seems to me (n=1) that it could even serve as a magic-carpet ride into the world-of-difference where Panorama flourishes.

[admin]

I think you might find the Extra Credit: Automatic Lookup of Category and Debit of this tutorial page instructive. In this particular example the relationship is between different records in the same database, but it’s just a minor tweak to do the same thing between different databases.

Data Entry Helpers

On another thread you mentioned how much you liked the documentation and examples, and I thanked you. However, in spite of the wealth of material, there is still more to be done, and relational examples and documentation are one area where some training material available in Panorama 6 is not yet available in Panorama X.