Relationship not returning a result in link-linked pair

For HanDBase related topics that are not specific to one platform.

Relationship not returning a result in link-linked pair

Postby AD:AM » Mon Feb 03, 2014 1:45 am

Hello all, I'm using the Android platform.

I might be missing something simple but consider the following (simplified) scenario:

Database 1: Patient
Field 1: LastName (Text)
Field 2: New Rx (Link, Other DB: Rx, Which field: LastName, Show value: LastName)
Field 3: Last Rx Date (Relationship, Related field: LastName, Other DB: Rx, Which field: LastName, Show from other: Date, Last Matching Record)

Database 2: Rx
Field 1: Date (Date, Date record was added)
Field 2: LastName (Linked, Other DB: Patient, Which field: LastName)

So the objective is to display for each patient in the Patient DB, the date of the last Rx generated. However, "Last Rx Date" is always blank; I can make Relationship fields work OK when there is no Link-Linked relationship, but not in this situation.

I hope that makes sense.

Cheers
Adam
AD:AM
 
Posts: 2
Joined: Mon Feb 03, 2014 12:54 am

Re: Relationship not returning a result in link-linked pair

Postby Brian_Houghton » Mon Feb 03, 2014 6:45 am

Hi,

Relationships have to work with like field types. Your relationship is comparing a text field in the parent database to a linked field in the child database. This will cause the failure you are experiencing.

Sent from my Nexus 7 using Tapatalk HD
Kind Regards,
Brian Houghton, DDH Software
Brian_Houghton
 
Posts: 2246
Joined: Wed May 20, 2009 8:30 am

Re: Relationship not returning a result in link-linked pair

Postby mjhanna » Mon Feb 03, 2014 2:39 pm

Hi Adam,

Link/Linked fields are used to define a one-to-many relationship (one record links to many records) and are set up this way:

The "Link" field is in the "one" database
Other DB: the "many" database
Other Field: the Linked field in the "many" database
Show: the field from this "one" database to display in the Linked field record

The "Linked" field is in the "many" database
Other DB: the "one" database
Other Field: the Link field in the "one" database

Your "Patient" DB is the "one" database, and your "Rx" DB is the "many', so they should be set up as:

The "Link" field in the Patient DB (New Rx)
Other DB: the "many" database (Rx)
Other Field: the Linked field in the "many" database (LastName)
Show: the field from this "one" database to display in the Linked field record (LastName)

The "Linked" field in the RX DB (LastName)
Other DB: the "one" database (Patient)
Other Field: the Link field in the "one" database (New Rx, not LastName)

Now you can also define a Relationship field in either database based on this Link/Linked relationship, which are set up this way:

A Relationship field in a Linked "many" database
Related: Linked field
OtherDB: the "one" database
Other Field: the Link field
Show: some field from "one" database

A Relationship field in a Link "one" database
Related: Link field
OtherDB: the "many" database
Other Field: the Linked field
Show: some field from "many" database

So with your "Last Rx Date" you are defining a relationship in the "one" database to display something from the "many". Thus it needs to be:

Related: Link field (New Rx, not LastName)
OtherDB: the "many" database (Rx)
Other Field: the Linked field (LastName)
Show: some field from the "many" database (Date)

Hope this helps,
Michael
(Only a user and not affiliated with DDH Software, but working with computer databases for over fifty years.)
mjhanna
 
Posts: 154
Joined: Tue Oct 15, 2013 10:56 am
Location: New Mexico

Re: Relationship not returning a result in link-linked pair

Postby AD:AM » Mon Feb 03, 2014 5:13 pm

mjhanna wrote:The "Linked" field is in the "many" database
Other DB: the "one" database
Other Field: the Link field in the "one" database
...
Related: Link field (New Rx, not LastName)
...
Hope this helps


Michael, this helps enormously. I realise now the mental hurdle I had to get over regarding Link-Linked relationships - I thought I had to point back to a matching "key", where in fact the software establishes the one-to-many link via the Link-Linked fields.

Thanks for your post Brian - however happy to say, I was able to get it to work with Michael's direction (that is, looking up a Text field value in a Linked field in another database. I saw you had offered a solution for someone the other way around - bringing more fields over to a Linked record - so I figured it must have been possible).

One unrelated issue that made troubleshooting a bit more difficult - as I changed settings with Link and Linked fields, I would tend to "lose" the "other fields" in the drop-down - I would see the default nomenclature of Field 1, Field 2 etc rather than the field names I had applied. The workaround was to select another (any other) "Other DB", and re-select the DB I required, and then the "other fields" drop-down was repopulated correctly.

Thanks again (is there a way of marking "answered" or "best answer"?)
Adam
AD:AM
 
Posts: 2
Joined: Mon Feb 03, 2014 12:54 am

Re: Relationship not returning a result in link-linked pair

Postby mjhanna » Mon Feb 03, 2014 7:46 pm

Glad it helped, Adam. Yes this is somewhat of a mental hurdle, which is why I made those notes to myself of how the Link/Linked works. I find I have to refer to them myself to get it right.
Michael
(Only a user and not affiliated with DDH Software, but working with computer databases for over fifty years.)
mjhanna
 
Posts: 154
Joined: Tue Oct 15, 2013 10:56 am
Location: New Mexico


Return to HanDBase General Discussion

Who is online

Users browsing this forum: Google [Bot] and 1 guest