Setting up relational database


#13

At some point, you will need to have matching values. You can assign unique IDs and use those to link them. Then you will be able to tell one John Smith from another. You would also be able to change the name to John Q Smith, and know it’s the same individual.


#14

I don’t change those entries until I am going to print the receipts. In the receipt form I can see if the address is successfully filled in.

For those recipients where no address is found, I check how the name is written — usually for 3 to 5 (of 70) members where the name has changed or the payer’s name is written “First Last” instead of “Last, First”.

That’s easy enough for me. So until today, I have not thought about automating this matching action with a procedure. If I would, I would have to make sure the procedure will identify names correctly and will not change names for other people (creating a fine mess).


#15

If you are entering values manually, Panorama’s Clairvoyance feature is a great help to make sure you always use the same way of writing. You activate it in the field’s properties.


#16

How do you link them (“unique IDs”) in Panorama X?

I understand how to use lookup( to retrieve values in other fields that are part of the record looked up. I don’t understand (and feel that I have over-stretched the politeness of this forum’s kind members) how to link unique IDs (let me retry this) how to lookup in another database a unique ID in order to enter it so that it matches and is dynamically changed when the unique ID in the other database is changed. IOW (I think) I can populate the field in database B with values which match the unique ID field in database A, and I can use lookup( to then get values from the unique ID record in database A, but the databases are not linked. I want database B to show the value of the unique ID, but I want the field to hold a reference to — to be linked to —the record field in database A.


#17

Exactly. Is there any way to use it across databases? I’m looking for the equivalent of entering data (a name) in a field in database B and having “Clairvoyance” suggest and complete the entry based on the entries in a field in database A.

@BruceDeB suggested above that I use arrays in a single database. This works for the example I gave, but I simplified the example for forum discussion. The actual data is more complex; it has additional self-references.


#18

The unique ID is the link. Its presence in both records is what shows the records are related. It would be something like a serial number. Something you would not want to change.


#19

Clairvoyance can be linked across databases in Panorama 6, but I do not think that has been implemented in Panorama X. But that is all the more reason why you should think about using single files whenever possible.

I have an old database that, if there are identical names that result from a search, brings up a choice of addresses, so that is possible, even if you do not use unique IDs.


Formulas & Programming pdf
#20

To have a link between two databases, you must have matching data in both databases. In some cases that can be a name or a company name, but that can be fragile as there may be different people or companies with the same name, or a name can change.

To get around this you would need to assign an arbitrary unique value to each entity. Then this value needs to be stored in each appropriate data.

In some cases, you may want to manually assign a visible unique ID value to each entity. For example you might assign sequential vendor id numbers, or manually create a SKU based on some algorithm or system.

If you don’t care about a visible ID number, you may just want to assign a random, but guaranteed unique value. One way to do this is with the info(“guid”) function. This function generates an identifier value that is guaranteed to be unique across all computers on the same computer. However, it is a very long value that would be difficult to type, but if your system is set up correctly you would never need to type it. In the primary database associated with the ID, you would set up a special procedure called .NewRecord. Unfortunately, use of this special procedure is not currently documented in the Panorama X documentation, but it is documented on page 386 of the Panorama 6 PDF book Formulas & Programming, which you can download here:

http://www.provue.com/Downloads/PDF/index.html

If a database contains a .NewRecord procedure, it will be automatically invoked whenever a new record is manually added to the database, so you include code in that procedure to generate the unique ID value. If your database has a field named PersonID, the .NewRecord procedure just needs to include this one line of code to automatically generate a unique ID whenever a new record is created.

PersonID = info("guid")

Note: If a new record is added by procedure code, the .NewRecord procedure is not automatically invoked, so you should make sure that your procedure code usess the call statement to call the .NewRecord procedure if necessary, like this:

addrecord
call .NewRecord

Now, how do we get this PersonID value into other, related databases. You could just type it, but obviously that isn’t a great plan (though perhaps useful for testing or when first getting things working). One way to do this is to set up a text list or matrix that displays a searchable list of information in the target database. This is very easy to set up, and the list or matrix can display information from any open database. This is well documented, and there are also paid videos that show this in great detail. The user would search for a subset of the list, and then click on the exact item they want, then press a button that would invoke a short program to extract the unique ID value from the list and copy it into the current record (the list doesn’t have to actually display the unique ID value itself, most likely it would display names and/or other info the user would use to make their choice).

Once you have data with unique id’s in them, you can use the various lookup( function to determine on the fly what records are related to each other. I think this may have been a point of confusion – the lookup( functions don’t create relationships, they expose the relationships that are inherent in the data itself.

Now this is a fairly fancy application. Once you know how to do it it’s not super hard, but I’m not going to claim that it is something that is super easy the first time or that you can just pop together without quite a bit of learning. But it definitely can be done and in a way that will make a nice user interface for the person actually using the database.

There are other techniques that could be used for setting up related records with matching id values, for example drag-and-drop. But this is already a very long post.


One other important point, Panorama X actually maintains a unique ID number for every record automatically. I forgot about this initially, because Panorama 6 did not do this except for shared databases. But Panorama X assigns a unique integer value to each record as it is created. You can access this value with the info(“serverrecordid”) function. (In spite of the name of this function, in Panorama X you can use this with any database, whether a server is involved or not.) So if you decide to use this, you can skip everything I said about the .NewRecord procedure and the info("guid") function.


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


split this topic #32

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