Setting up relational database

[derick]

I have a rarely-used but important database in FileMaker 10. It began life in Corel InfoCentral for Windows 3.1 (anyone remember that?) and I was able to migrate all the tables and relations into Filemaker through a procedure like this:

Table A - with unique ID
Table B - with unique ID
Table A-B - with fields for a Table A ID and Table B ID (and other details on the relationship)

Import all three tables & create links based on the IDs

Now it’s 2017 and FileMaker 10 won’t work in High Sierra so I’m looking for alternatives. I was able to set this up in a Ninox trial but I’m a bit wary of a fairly new and under documented product. Hence I’m also demoing Panorama X.

I’ve been able to import all three tables but I don’t see how to create the relations between them. Am I missing something obvious?

[admin]

In Panorama, 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.

lookup( function

Panorama X has a new function called superlookup( that allows more complex keys, for example using more than one field in each database, or even a formula to define the relation. For example, you could have one database that had separate first and last name fields, while a second database has one combined field for first and last names, and using superlookup( you could relate these two databases even though they have different field configurations for the keys:

superlookup( function

These functions can be used anywhere a formula can be used. For example, in a form or report, you could display a relation on the fly by using one of these functions in a Text Display object. A typical example would be displaying related information from a customer database in an invoice.

Text Display Object

Another place lookup formulas can be used is in an automatic field calculation. You would use this to copy related information into the current database as data is entered. For example, when constructing an invoice, you would probably want to copy the current price for each item into the invoice, so that if you come back to the invoice some time later, you’ll see the price that was charged at that time, rather than whatever the current price is.

Automatic Field Calculations

If you want to do lookups on many records at once, use the formulafill statement.

formulafill statement

[JamesCook]

You omitted two of my favorites: arrays and matrices.

They can do plenty on their own but together they’re awesome.

Arraybuild can pull quantities of data from one or more databases with extreme precision. Arrayboth enables combining arrays built from different databases into one array. Displaying an array in a matrix makes it possible to not just view the data, but you can set up processes to act on it and change the data in those other databases using post or postadjust.

In Panorama there are just so many ways to skin a cat.

[Asu]

This is all very interesting, however it would be so useful to have a step-by-step set of instructions. I have a large database with multiple tables self-relationships, and I am also considering converting but I this information alone won’t help. is there such a thing somewhere?

[BruceDeB]

Step by step is difficult, because there are so many ways to do it.

I can only give advice about how I look at it:
If you want a live relationship, where if you relate to data in another file even if it changes, you should look it up in a procedure in a form object, so it will be updated each time you look at the form. If you want at static relationship, where the data is put into a record at the time the record is created, and you want it to be saved so it stays there, then you look it up in a procedure in a field and save it there.

[Asu]

Thanks. Maybe someone who has done it once will see this thread and responds.

[KirbyKrieger]

If you want a live relationship, where if you relate to data in another file even if it changes, you should look it up in a procedure in a form object

This is exactly what I want to do (as far as my understanding extends, which is about a handbreadth past my eyelashes). I have several databases that are linked by a common field (let’s just call this “UniqKey”). I want (I am struggling mightily in the eddies of ignorance) to, from Datasheet B, select the UniqKey from Datasheet A, and have it update and remain linked when I change the text of the UniqKey in Datasheet A.

Example:

• Datasheet A is individuals.
• Datasheet B stores addresses for image files of those individuals. There are 0-to-many records in Datasheet B for each individual in Datasheet A.
• Datasheet C stores addresses for image files taken by those individuals. There are 1-to-many records in Datasheet C for each individual in Datasheet A.

At it’s simplest (afaict) I want to be able to add records to Datasheets B & C and include a field in common with Datasheet A (e.g.: “UniqKey”) that will update any time the field-in-common in Datasheet A is changed.

The goal (of course) is to be able to build forms that show Individuals (Datasheet A) and (in my example) photos of them (Datasheet B), and photos by them (Datasheet C). The example is specific, but is meant to be basic enough to apply to many standard data situations (so substitute any dataset for “photos” — I just happen to do a lot of work with images,and so the fields in my example hold storage addresses for images).

At this point, that the link is dynamic is is secondary. I.do.not.know.how.to.make.a.static.link. I just want to be able to pop-up a list of, say, Names-in-Datasheet-A when I’m entering records in Datasheets B or C (pop-up or equivalent, such as “Clairvoyantly” auto-fill, use “Post” statement with mode “updateadd”, etc.).

Let me specify that, unhelpfully, I do not know the difference between Statements and Functions, or between Formulas and Code.*

*(Having just read the Help page on “Code”, I now know the difference between Code, Statements, and Functions.)

[BruceDeB]

It looks like you have a set of names, and a set of image file addresses (IFAsa). I use addresses because that is how they will be referred by the database.

For each name, which I hope will be unique, and will not change, you want to associate IFAs in two ways, if they were taken by that name, and if that name appears in the image.

So what I see is that there should be a flat file with at least three fields. One field will be the name, the second field will be arrays of IFAs that were taken by that name, and the third field will be arrays of IFAs in which that name appears. There may be more fields associated with the name, but they can fit in the database. I presume that you may want to change those fields, but that is not going to change whether that name took or appears in a IFA.

This is a very simple database that is not relational at all. The trick is entering data into these arrays, and displaying what you want. That will take some work with forms.

[KJM]

I think I jump in with some help a little bit closer to Kirby’s example.

Let’s say I have a database file A containing names and addresses, and a database B with payments. The common key is a Name field.

I want to print receipts for payments done last year. I have a receipt form in database B with surrounding text and text display of the relevant data fields.

Now the relational part: A Text Display object in the receipt form of database B fetches the postal address of the recipient “on the fly” from database A.

(I have a procedure doing those steps because I have to do this task every year. The procedure e.g. opens database A, if it is not open, and does some initial checks.)

Here is the formula for the “Address” Text Display Object. (You enter the formula under the Formula tab in the Properties panel of the Text Display Object and click “Apply”):

«PayerName»
{lookup("A","MemberName",«PayerName»,"Street","",0)}
{lookup("A","MemberName",«PayerName»,"ZIP","",0)} {lookup("A","MemberName",«PayerName»,"Town","",0)}

This code gives you three lines in your receipt form

1. with the recipients name,
2. the street address and then
3. the zip code and town

for all those records where MemberName in db A matches PayerName in db B.

(The amount of payments, displayed in another TDO, comes directly from analyzing and outlining the transfers in database B.)

I hope this gives you an idea how to use lookup( functions to build relational links between databases. This lookup does not change any contents in database A or B.

The post statement in a procedure would be used if you want to change values of fields in another database.

[KirbyKrieger]

Thank you Kurt (@kjm) — well explained.

How do you enter (get/fill) the Name field in Database B when you create a new record (record a payment by a customer)?

[KJM]

I import the list of transfers from a csv text file (created by my banking application).

So one of my preliminary checks before printing the receipts is to make sure that „PayerName“ in database B is written the same way as „MemberName“ in database A.

[KirbyKrieger]

Clear and helpful — thanks again. I promise I won’t follow up beyond these two questions, the first of which is probably just a short curb in the path towards using Panorama X, but I feel it as stone wall blocking my progress.

How do you (or should I) manually (and most speedily) enter a new payment record in database B so that “{database B} PayerName” matches “{database A} MemberName”?

Is there a way to behind-the-scenes update “{database B} PayerName” matching records when there is a change in “{database A} MemberName”? (I think the congruent question is, “Is it possible to save a reference to a field in another database instead of saving a copy of the value?”)

[dave]

“Is it possible to save a reference to a field in another database instead of saving a copy of the value?”)

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.

[KJM]

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

[KJM]

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.

[KirbyKrieger]

You can assign unique IDs and use those to link them.

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.

[KirbyKrieger]

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.

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.

[dave]

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.

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.

[BruceDeB]

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.

[admin]

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.