Question/Observation on relationships

[Steve]

I've got four tables, (Well, many more actually, but to illustrate the question...)
Customer_List,CustomerNotes, CustomerNoteDetail and CustomerContacts. structured like:

Code: Select all

Customer_List --------------------------
Contacts(Link) => CustomerContacts:Customer(Linked), Show Companyname in other database
CustomerNotes(link) => CustomerNotes:Customer(Linked), Show Companyname in other database
Companyname(text)
(still more fields but not relevant to discussion)

CustomerNotes --------------------------
Customer(Linked) => Customer_List:Contacts(Linked)
Date(date)
notetype(popup)
Details (link) => CustomerNotesDetails:CustomerNote (Linked)  Show Customer in other database
FollowupDate(Date)
(still more fields but not relevant to discussion)

CustomerNoteDetail----------------------
CustomerNote(Linked) => CustomerNote:Detail(Linked)
Purpose(Popup)
NoteDetail(Note)
Date(Date)
(still more fields but not relevant to discussion)

CustomerContacts------------------------------
Customer(Linked) => Customer_List:Contacts(Link)
Name(text)
Position(text)
ContactItems(Link) => ContactItems:ContactName(Linked), Show Name in other database
(still more fields but not relevant to discussion)


I always start with Customer_List and follow the links to the other info in the other DB's
It works well for many of my purposes, HOWEVER...

Suppose Ive got a CustomerNote open on one of the customers in Customer_List, and I wish to
view the related Name or Phone number in CustomerContacts.

The only reliable way I've found to do this is to provide another link/linked pair between them.
This is very clunky and prone to error. (maybe I'm missing the obvious?)

Why not define relationships between DB's while creating/editing them?
that way, any related record's fields would be accessible from within the relationship.
for instance: I could create a field in that same CustomerNote record that say something like Contact Phone, and
grab the data like I would with a link/linked pair but be able to choose a field from the link DB.
OR leverage the Link/Linked ID to allow additional fields in the link DB to be shown in the Linked DB via that unique LinkID.

And on a small unrelated note: When editing databases, in place of "Database Properties" at the top of the page, use the databases name instead
eg; CustomerNotes Properties.
And the same for any of the properties options...
CustomerNotes Fields, CustomerNotes Field Order, CustomerNotes Forms etc.
When editing I sometimes loose track of where I am and need to backup to Database settings.

[Brian_Houghton]

Hi Steve,

Thanks for posting.

My understanding is that you are interested in something along the lines of dynamic relationship creation that can be created "on the fly". Is this a correct summary?

If it is, it is definitely an interesting idea.

Regarding the option to display the database name at the top of the Database Properties screen is a good idea. I am envisioning something along the lines of:

Database Properties: myGreatDatabase

[Steve]

Last first,

"Database Properties: myGreatDatabase"

... Yes that's what I mean,... but I like your format better.

I don't know the internal workings of Handbase well enough to know if, "on the fly" applies. , however, if my current limited understanding is correct, then yes.

Is the below a better restated idea of what you thought I meant?

CustomerList
Notes(Link) => CustomerNotes:Customer(Linked), Show Customer in other Database
Contacts(Link) => CustomerContacts:Customer(Linked), Show Customer in other Database
Customer(text) #Customers Name

CustomerContacts
Customer(Linked) => CustomerList:Contacts(Link)
ContactMethods(Link) => CustomerContactMethods:Name(Linked), Show Name in other Database
Name(text)

CustomerContactMethods
Name(Linked) => CustomerContacts:Name(Linked)
Customer(NewFieldType) #would show CustomerList:Customer
methodType(popup) #eg: phone, email, cell
method(text) #eg: phone number or email address


CustomerNotes
History(Link)=>CustomerNoteHistory:CustomerNote(Linked) Show subject in other database
Customer(Linked) => CustomerList:Notes(Link)
date(date)
subject(text)

CustomerNoteHistory
CustomerNote(Linked)=>CustomerNotes:History(Link)
Contact(NewFieldType)
#here is a place where a list view on the Contacts DB would be useful. entering this field would produce a list view of contacts for that company from the CustomerContacts DB, Recording the selected contact from there, in this DB's record until changed
Date(date)
Notes(note)

I've attempted to implement this by using a unique field in CustomerList, and manually entering that to each linked DB record.
I then tried to relate, calculate or otherwise reference that ID, unsuccessfully.
Attempting to reference the Linked field doesn't work as I'm sure it is a unique numeric value that is not displayed.
If CustomerLIst:Customer = ABC, It is displayed in CustomerContacts:Customer as ABC, but for relationship field or any other comparison type, ABC isn't used.

Is this any clearer?

[Brian_Houghton]

My recommendations are to:

- Avoid using the unique field. It is mostly a legacy field leftover from earlier versions of database that does not always work properly. It can create non-unique values. What I recommend instead is to use a formula to create your own truly unique values. To do this, I would add a date field and a time field and another field type, such as an incrementing calculated field together using the concentenate feature of the external field type.

- Then, instead of using link/linked fields I recommend using relationships. This will cause new records in the child databases to be related to the record in the parent database as it is created.

They are only small changes, but I think you will like the flexibility and the truly unique values better.

[mjhanna]

You might like to review the thread here where I explored creating a unique key. Be sure to read the entire thread, as Dave and Brian made valuable corrections and comments.

[Steve]

Thank you Brian and mjhana.
These solutions work, except for one instance.
I've implemented unique keys by adding Date record created and Time record created.
as this method produces an id down the the second, it seems unlikely that multiple users would create a new record within the same second.
(In any event, I'm all of the users. LOL).

here's the basic linking structure that is working for me in all but one case.

Code: Select all

One:
ID(calculated), created as stated above
Many(relationship)  One.ID and Many.FK_OneID

Many:
FK_OneID(DBpopup) Many.ID

I've got a Job Database,(One) and a Timelog Database, (Many), related as above.
The previous method of relating was using a Link/Linked pair (Job:Link, Timelog:Linked)

Timelog contains 1737 records and is 633K in size
Job contains 802 records and is 235k in size

I intend to populate the FK_JobID field in Timelog by following the DBpopup until such time as I figure out a way to do this quickly via some other method.
Adding new records should be as simple as following the Timelog relationship in Jobs to the Timelog record.
HOWEVER,
When I follow Timelog, Create a new Timelog record and save, the FK_JobID field is not populated and if I try to force it by following the FK_JobID dbpopup in Timelog, It also will not populate. (I do sucessfully locate the correct Job record and choose it).

I have not yet tried to create a new blank DB for each as a test...

[mjhanna]

Hi Steve,

Glad the pointers were some help. I have some further notes I wrote to myself concerning this topic a while back. Maybe they would be of use.

Selecting the one source record
In any other database which desires to retrieve and display data from one record in a source database based on the Record ID, either one or two fields must be defined to establish the connection to the source database. One field must specify the desired one record in the source database. This field can simply be an Integer field type where the Record ID of the desired record in the source database is manually entered. Alternatively it can be a DB Popup field type which retrieves the Record ID. The first method requires knowing the desired source Record ID number. The second method permits either manually entering a known Record ID or clicking the field name to find the desired record among all the records in the source database. A second field in the retrieving database is needed only if the first specifying field is a DB Popup field type. This second field will simply be the retrieved Record ID as a Calculated field type. Since this second method is more flexible, it is usually the one I use.

[This second method sounds like the one you have chosen to use.]

Field A (method two to identify the source record)
Field Name: (usually the other database name)
Field Type: DB Popup
Visible, Exported, Not Encryted, Pixels = (enough for the largest likely ID number)
Max Characters: (enough for the largest likely ID number)
Other Database Name: (the source database)
Other Field Name: Key
Group: 0
No Popups
Default: None

Either manually enter a known Record ID, or use the Popup to locate and select the desired source record which will enter that Record ID in this field.

Field B (method two second required field)
Field Name: (usually the other database name)# [the appended ‘#’ differentiates the name from Field A’s]
Field Type: Calculated
Not Visible, Not Exported, Not Encrypted, Pixels = 0
Configure: ( FieldA * 1)
if Field A is Field number ‘n’ displays as: ( Fn*1 )
Result Format: Integer

Retrieving a value from a source field
Now this retrieving database may have as many Relationship field types as desired to display the value from any data field in the source database. I like Relationship field types as they display the data without the ability to edit or change that data. Clicking on this field name will open a List View of the source database, but since the related fields are based on the Record ID, they will only match one record in that database, so only one record will show.

Field R
Field Name: (usually the source field name to be shown)
Field Type: Relationship
Visible, Exported, Not Encrypted, Pixels = (what ever is needed for this shown field)
Related Field In This Database: (see below)
Other Database Name: (source database)
Other Field Name: (see below)
Show From Other Database: (the desired source field)
Show Which Record: (neither, since retrieving on Record ID will identify a unique record)

Which “Related Field In This Database” and the “Other Field Name” should be used both depend upon which of the two methods is used to identify the source record. The Relationship field type requires that both the values of these two fields be identical and the Field Type of the two fields be identical.

If method two, DB Popup field type, is used then the two matching Calculated field types are specified. The Relationship field will not match a DB Popup field type in this retrieving database with either an Integer or Calculated field type in the source database. This is the reason for creating the Calculated field type in this retrieving database.
Related Field In This Database: (Field B)
Other Field Name: databasename ID

==============
Hope these notes are helpful,

Michael

[Steve]

Michael,
your notes were difficult for me to follow, but I think I understand what you are saying.
Athough I'm not clear why you created the calculated Field B... It appears you expect the source tables' Key field to be text, wouldn't it be integer already?

I also think, my code section above was ambiguous, and inconsistent with my other notation methods above it.
I've attached two simple databases, (One.pdb and ToMany.pdb), demonstrating the above Implementation of Unique ID's and their use in Relationships.

The two tables in question, Job( like Example db One) and Timelog( like example DB ToMany), do not work as they do in my other DB's related in the same way.
eg: CustomerList(One) is related to Jobs(ToMany), and is working fine.

The only difference, is that Jobs and Timelog have Link/Linked associations still, while I establish the relationships and verify functionality.
The Job database is creating new ID's with each new record, but the FK_JobID field in Timelog is not populating correctly and furthermore, that entire column is not populated.

I should also mention that many of the Link/Linked pairs in Jobs/TimeLog are broken as I've Imported data from earlier versions of Jobs and Timelog that I intend to connect via this new relationship method.

Perhaps this is the reason Timelog.FK_JobID isn't populating.


Still have not tried empty database test...

[mjhanna]

Michael,
your notes were difficult for me to follow, but I think I understand what you are saying.

Sorry about their difficulty, Steve. They were intended as notes to myself, so of course I understand them <grin>.

Athough I'm not clear why you created the calculated Field B... It appears you expect the source tables' Key field to be text, wouldn't it be integer already?

The calculated Field B is essential, because a DB Popup field is NOT the integer you expect. That is the whole point. Internally the DB Popup field contains a "pointer-value" to the integer field in the other database. The purpose of the calculated Field B is to use that "pointer-value" field to create an integer within this database which is the same as the current integer in the pointed-to field. Then that calculated integer value (Field B) can be used in this database as the "Related field" for other Relationship fields (Field R) within this database. The relevant comments from my notes are:

The Relationship field type requires that both the values of these two fields be identical and the Field Type of the two fields be identical... The Relationship field will not match a DB Popup field type in this retrieving database with either an Integer or Calculated field type in the source database. This is the reason for creating the Calculated field type in this retrieving database.

A DB Popup field in this database cannot be the "Related field" for a Relationship field in this database if the field type in the other database is an integer. The Field Types of the two fields are not identical: Field A in this database is a "pointer-value" type field, the ID field in the other database is a "calculated integer" type field. That was the main purpose of this reminder in my notes to myself. But HanDBase can use the Field A "pointer value" type field in this database to create a separate "calculated integer" type field (Field B) in this database which will now be identical in type to the "calculated integer" type field in the other database. So Field B (but not Field A) can now be used as the "Related field" in Relationship fields in this database because the types are now identical.

If you don't need Relationship fields in this database to retrieve other fields from the other database, then you don't need to create Field B. But I find that I always am retrieving at least one other field, so I always need to create it.

I know this is a confusing concept, which is why I wrote the notes to myself. But hopefully this is somewhat clearer?

[Steve]

Didn't notice that my attachments didn't attach, was in a rush this morning.

ToMany.zip

(2.83 KiB) Downloaded 1827 times

One.zip

(2.54 KiB) Downloaded 1828 times

Michael,
In these samples, ToMany uses FK_OneID in it's Relationship field, successfully, without first converting it's value.