Setting up relational database

If I interpret this to mean you are trying do all this only using the data sheet then I would have to say you will be hard pressed to get things working as desired using this method. I would urge you to set this up in a form or multiple forms to give you much better control over user input and interconnectability with the various files.

1 Like

Kirby

Often, the easiest way to learn how to do something is to see how somebody else has done it. I have set up four databases which provide a basis for an introductory lesson in the construction of a simple invoicing system. Subscribers to the ProVUE forum occasionally ask for advice or assistance in setting up an invoicing system and I hope that this package will provide a useful basis for future development. It has been written in Panorama X and is not readily adaptable to Panorama 6 or earlier versions. It comes with a comprehensive Users Guide.

I haven’t put it on the Database Exchange yet because it still has a few rough edges and I’ve been caught up with several other tasks, not the least of which is setting up a large domestic hydroponic tomato and strawberry system.

Anyway, I think you might learn some useful tricks from it. If you need help with it I’m happy to provide it.

You can download the system from Dropbox - File Deleted

3 Likes

@michael — thank you so much for the invaluable opportunity! :blush: . I have always learned by, well, taking things apart —very much looking forward to examining your solutions, from tiny to whole.

Thank you for the extensive response.

Do I understand correctly that the only way to write a value (in this case a copy of a value in database A) into database B is from database B?

The method you present involves the user making a record in database B active and selecting from information retrieved from database A in order to have a value saved in database B. Retrieving information is not, it seems, crippling my database system; writing information is.

The several variations of the lookup( function provide ample ways of retrieving information. The example given in the “Extra Credit” section of Tutorial 303 “Data Entry Helpers” retrieves values from a record in the current database, and via an assignment statement in the Code field of a database field writes those values to other fields in the active record. I understand the use and utility of the retrieval facility of the variations of lookup(, and likely will work through the creation of an interface to get, for instance, the value of “PersonalID” stored in database A and store it in database B, while working in database B.

What I would like to do is have a new record added to database B, and a field filled with a value (e.g.: “PersonalID”), when I create a new record in database A. Panorama X Help tells me that the addrecord statement works with (and thus with only) the current database. That’s as much as I’ve researched so far.

I’m guessing that I may have stumbled into a situation where @BruceDeB’s suggestion above of listing image file addresses in text array fields in each record with a unique PersonalID is a good solution (there is only one database, so the unique identifier is entered only once). If I understand correctly, I am using my second and third databases to create multicolumn arrays of data for each record in database A. (My ancient database knowledge tells me that since no record in database B will be associated with more than one record in database A I have not fully “normalized” my data.) The solution for my data may be to create an interface for filling in text arrays rather than trying to create and fill data in other databases.

My issue here arises from adding information to the data set. If the data set were static and I were simply retrieving information I would be all set.

Thanks again for your continued consideration.

—K.

You could setup a data entry form in database A. Data that will be stored in another database, could be entered into a text editor object that sets a variable. A procedure, possibly triggered by closing the object, would then post it to the other database. The key value could then be posted at the same time. It’s not absolutely necessary to enter them at separate times in separate places.

1 Like

Go back and study the post statement again. If you post from DB A to DB B with the mode set for add, you’ll get a new record with whatever data you choose in it. If you set the mode to updateadd ,
you’ll get a new record if you need one, or if there’s already such a record, any data from DB A that you choose will be written or rewritten into that record.

1 Like

If you have a .NewRecord procedure in database A with the proper code you can automatically create a new record in database B and fill a field with the same value as it is in database A. Here is some sample code that would be in the database A .NewRecord procedure:

PersonalID=uniqueid("ID",info("user"))
local theID
theId=PersonalID
setactivedatabase "B"
addrecord
PersonalID=theID
setactivedatabase ""

Once the new record is added the procedure will populate the PersonalID field in with a unique ID and then assign the same value to a local variable to use in database B. The setactivedatabase statement makes database B the temporarily active database and then adds a new record to it and assigns the PersonalID field of database B the same ID as in database A. Finally we set the active database back to database A.

1 Like

Gary’s example code is perfectly fine, but here is an alternative that is slightly shorter:

PersonalID=uniqueid("ID",info("user"))
setactivedatabase "B"
addrecord
PersonalID=fieldvalue("A","PersonalD")
setactivedatabase ""

In both of these example, the final setactivedatabase "" statement isn’t really necessary, since Panorama will do that automatically when a procedure ends.

I believe an even shorter version could be written using the post statement.

PersonalID=uniqueid("ID",info("user"))
let theID=PersonalID
post "add","B","","","PersonalID",theID

That was my thought, but I didn’t explore it further since you said that your actual application was more complex than you explained. Text arrays in a database field are often a great solution, I use that technique frequently. In fact, sometimes I’ll use two dimensional text arrays (usually tab/return delimited), essentially embedding a tiny database within each record (for example for line items in an invoice).

2 Likes

Indeed. Thank you for the reminder and for your earlier post.

I am still uncanning worms, but I suppose that is the beginner’s diet.

I have set up three databases: dbA, dbB, & dbC (not the actual names, in case that signifies).
Each database has more than one field.
Each database has one field called “UniqID” (again, not the actual name).

My goal is to have the value of the UniqID of the current record written into the UniqID fields of a newly-created record in dbB and in dbC when I enter text in the UniqID field of dbA.

I have the following code in the Code field of the Field Properties of dbA’s UniqID field:
post "updateadd", "dbB", "UniqID", UniqID, "UniqID", UniqID

(I should state that dbA has a .newrecord procedure, as I have been working in all available directions. That procedure now consists of several lines commented-out, and one statement: addrecord.)

When I add a new record to dbA and enter text in the UniqID field and tab out of the field, a new record is created in dbB, but it is blank.

I get the same result when I specify dbC instead of dbB.

If I change the code to:
post "updateadd", "dbB", "UniqID", UniqID, "UniqID", "Catfish"
when enter text in the UniqID field in dbA and tab out of the field, a new record is created in dbB, it is blank, and “Catfish” replaces the text I typed in the UniqID field of dbA. This strikes me as irregular, and therefore significant.

I get the same result when I specify dbC instead of dbB.

Further, when I tried to create a new record with UniqID copied from dbA to each of dbB and dbC with the following two lines of code:
post "updateadd", "dbB", "UniqID", UniqID, "UniqID", UniqID
post "updateadd", "dbC", "UniqID", UniqID, "UniqID", UniqID
a new blank record was created in dbB, and a new record was created in dbC, with the UniqID field containing the UniqID value from the UniqID field of dbA!

When I reverse the order of the two lines of code, the result is reversed: a blank new record in dbC and a new record in dbB with the value of UniqID in dbA in the UniqID field of dbB.

I see similar results with the post statement when used (sort of) as @admin recommends elsewhere in this thread. With the following in the code field of the UniqID field of dbA:
let theID=UniqID
post "add","dbB","","","UniqID",theID
A new record is created, and it is blank.

(I tried this in the .NewRecord procedure*, but it doesn’t do what I need, since the new record in dbB is created at the same time as the new record in dbA, and therefore there is no value in the UniqID field of dbA. I understand that the suggestion to use a .NewRecord procedure was based on automatically generating UniqID values. I could, perhaps, do it this way, but it entails adding a whole other set of fields to the databases, which I could hide, but it seems a long way 'round. I will always need a unique person-reference that brings to mind the person. I had thought a simple solutions would be to assign these myself, and use them in lieu of any alpha-numeric code.)

(@admin Jim — especial thanks for the link and exact volume and page in the Pan6 documents regarding the .NewRecord procedure.)

I got similar results in a test I ran a few days ago, but I hadn’t gotten around to writing up a bug report. Post is a procedure that secretly makes the other database active, then either finds or adds the appropriate record (in this case it would be adding) and then assigns values to the appropriate fields. Everything happens as it should until it comes time to do the assignment, and then it seems to forget which database is active.

If I run the procedure from a procedure window, rather than triggering it from data entry, everything works as it should. In this case, the edit window for the data cell isn’t open as the procedure runs. That might be significant.

If I write a procedure that visibly makes the other database active, it also works correctly. A procedure of my own that does what post does, fails in the same way that post does.

2 Likes

Thanks for the quick response.

Sigh. I spent 8 hours quadruple-checking everything and still expected that:

  1. There was something completely basic I didn’t “get”, and
  2. Having stretched the kindness of this forum, my continued failure to make this work would break it.

Glad to know that the unexpected behavior is unexpected. I will look for a work-around. (Added:) Suggestions welcome.

2 posts were split to a new topic: Submitting Bug Reports

Hi Kirby,

I’ve read several of your posts and I, like you but a few years later, am trying to transition to PanoramaX from FileMaker. While I understand that PanoramaX uses look ups to find related data in the databases, I find that I am unable to create the relational functionality that was available in FileMaker. Without having the ability to link databases with key fields, it seems like creating relationships n PanoramaX is a work around, with all the dynamic updating that a relational database poses functionally absent.

Your being told to get over the FileMaker way of thinking not very helpful. However, I guess that may be the only answer to trying to make a flat file database like PanoramaX perform like a relational database. Before I give up on PanoramaX, I was wondering if you found a way to make PanoramaX work like FileMaker?

Thanks - Jim Weber

2 Likes

A really novice/newbie question here - for various reasons I need to quickly determine if I should buy PanoramaX or not (I’m currently watching the videos but I don’t know if I’m able to figure out what I need to know before I need to decide to buy or not)and the key feature for me is that I’m able to create a relational database.

The functionality I need is

  • One-to-one
  • One-to-many
  • Many-to-many

In other words I want to be able to do something similar to this using SQL-speak

select A.col1, A.col2, B.col3
from A join B on (A.id = B.id)
where A.x >= something
order by A.col1, B.col3

And being able to present the result as a (read-only) view and also as PDF & text.

The lookup and superlookup function seem to solve this problem … but just to be 100% sure.

And yes, I could do everything I need to do in a SQLite database but I want to have something that looks good also :slight_smile:

(I should perhaps add that I don’t need to know how this can be done - I assume I can figure this out from the docs, just that it can be done.

Yes you can. Lookup and post take care of everything.

Remember that you can try Panorama on a free trial so you can test it out before buying it.

@billconable Thanks for the answer.

@JeffK Yes, I have and will shortly be trying to do a few things. But it’s valuable to get info from experienced users that have done things that will get me some time to get to.

I’ve been watching more videos and have a couple of additional questions that I’m not 100% sure about.

What I don’t really get a grip of is how scripts, forms and, using SQL-speak, tables are stored.

  • Are “tables” stored as separate PanoramaX databases? So if I need 5 tables for a database (SQL speak) then I need to create 5 separate PanoramaX databases?

  • Where are scripts, forms, etc stored? (in one of the databases, spread out between them, separately)

Unlike SQL, where a database may contain multiple tables, in Panorama each database contains a single table. In fact Panorama doesn’t officially use the term table, just database. Each database is a separate file that you can see in the Finder.

Each database contains it’s own forms and procedures. When a database is active (the topmost window), it’s forms show up in the View menu and the procedures show up in the Action menu (actually the procedures show up in the View menu also). When you click on a different database these menus will change.

Thank you for the answer

Also, I believe what you are calling “scripts” are procedures in Panorama. Procedures in one database can act upon data in another database and even call procedures in another database to calculate results or grab data that can be used in the active database, In other words, it’s not like you need to see five different databases flashing around on the screen. A solution can be created which uses multiple databases that, to a user, seems like there is only one database open.