Setting up relational database


#21

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.


#22

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 https://www.dropbox.com/s/5c3wokcv784jl4x/AUBIS%20system%20files.zip?dl=0


#23

@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.


#24

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.


#25

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.


#26

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.


#27

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.


#28

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).


#29

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.)


#30

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.


#31

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.


#32

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