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:
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:
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