Difficulty in setting Related Records

15 posts by 2 authors in: Forums > CMS Builder
Last Post: November 4   (RSS)

By andreasml - October 21 - edited: October 22

Hi

I hope it will not be too much complicated.

I have set up the CMSB for a medical office. I have created a section named "Patients" which contains general info about the patients (eg, name, phone number, address, etc) . Then, I created another section named "Visits" with information regarding every visit of each patient. To relate the two sections, I have created a Related Records field in the "Patients" section named "List of visits". In the field option 'MySQL Where' of this field, I have kept its original content (foreignFieldNum='<?php echo mysql_escape(@$RECORD['num']) ?>'), and I have clicked all the list actions (View, Modify, Erase, Create). Also, I have created a text field in the "Visits" section named "ForeignFieldNum". Now, when I press the Create button in the "List of Visits" field for a patient, I can create a new visit for this patient. So far so good. However, in order to relate the records between the two sections I need to find and write the ID number ("num" field) of the patient into the "ForeignFieldNum" record of the "Visits" table, each time I create a new visit for this patient. Is it possible to automatically set a default value in the "ForeignFieldNum" field of the "Visits" section which would be the patient's ID number so as to avoid doing this manually each time? I have tried something like <?php echo (@$RECORD['num']) ?> with no success. 

I hope the question makes sense. 

Kind regards, 

Andreas Lazaris

By Dave - October 26

Hi Andreas, 

The foreignFieldNum field is just a placeholder, so it's no problem to use it, but you might want to use a more readable fieldname. Another option would be to have a field called patientNum in the visits table.  You can then set that to be a pulldown of all the patients with patient 'num' as the value and patient name as the label.

Then for the patients table have a related records field that looks up records from the "visits" table like this:

(patientNum='<?php echo mysql_escape(@$RECORD['num']) ?>'),

And here's how you can pass along the default value.

So in the "patients" table, if you click "Create" under the related records field for "Visits", it will redirect you to the visits create page, but there should be an additional field in the URL named "patientsNum".  This is the name of the referring table and the record num.  

In the "Visits" table, if you edit the "patientNum/foreignFieldNum" field and set the "default value" to this:

<?php echo htmlencode(@$_REQUEST['patientsNum']); ?>

Then it should auto-populate when you create a new record.  

Note that if your table isn't named "patients" you'll need to enter your actual table name.

Hope that helps, let me know if it works out for you.

Dave Edis - Senior Developer
interactivetools.com

By andreasml - October 28

Hi Dave

It worked! Many thanks for your advice.

Kind regards, 

Andreas

By andreasml - October 31

Hi Dave

Another small issue. 

In my section "Visits", I would like to create a link to redirect each "Visits" record to the corresponding record of the section "Patients". 

To make it clear the sections, and their fields are as follows:

"Patients" section: Patient ID, Surname, name, age, .... (other info)

"Visits" section: Visit ID, Patient ID, Patients.surname, Name.surname, ... (other "Visits" info)

I would like to add a link in the "Visits" section that would give me access to the corresponding "Patients" record.

Kind regards, 

Andreas

By Dave - October 31

Hi Andreas, 

A couple ways to do that.  One is to create another "Related Records" section that points back to patients.  This can be fast and easy and even though it has just one record it works.

Another way is to create a separator field and set the separator type to "HTML" as follows: 

<div class='col-sm-2'>
  Patient Record
</div>
<div class='col-sm-10'>
<?php $patientRecordNum = $RECORD['patientNum'] ?? ''; ?>
<?php if ($patientRecordNum): ?>
  <a href="?menu=patients&action=edit&num=<?php echo urlencode($patientRecordNum); ?>">View patient record</a>
<?php else: ?>
  There's no parent patient record selected for this visit yet
<?php endif ?>
</div>

That will show a link to the parent patient record.  Note that you may need to adjust the table and field names to match yours.

Hope that helps!

Dave Edis - Senior Developer
interactivetools.com

By andreasml - November 1

Hi Dave,

Thank you for your assistance. I am trying your second option, the separator field. Unfortunately, I cannot make it run. 

Here is what I did. 

I created a separator field in the "Visits" section, and added your code as following: 

<div class='col-sm-2'>
  Patient Record
</div>
<div class='col-sm-10'>
<?php $patientRecordNum = $RECORD['outpatients_clinicNum'] ?? ''; ?>
<?php if ($patientRecordNum): ?>
  <a href="?menu=outpatients_clinicNum&action=edit&num=<?php echo urlencode($patientRecordNum); ?>">View patient record</a>
<?php else: ?>
  There's no parent patient record selected for this visit yet
<?php endif ?>
</div>

On line 5, I have changed your $RECORD['patientNum'] to $RECORD['outpatients_clinicNum'] as 'outpatients_clinic' is the name of my section with the patients' details.

And on line 7, ?menu=patients to ?menu=outpatients_clinic, for the same reason as previously. 

Could you find the mistake?

Kind regards, 

Andreas

By Dave - November 4

Hi Andreas, 

Is there a PHP error, or no output, wrong link, or what's the specific problem?  Let me know and I'll try to help.

Thanks!

Dave Edis - Senior Developer
interactivetools.com

By Dave - November 4

Ahh, okay.  Try adding some debug code to see what the values we're checking are set to: 

outpatients_clinicNum: <?php echo @$RECORD['outpatients_clinicNum']; ?><br>

All record values: <?php showme($RECORD); ?>

The second line should give you a list of all the fields and values for the record so you can make sure you're checking the right thing.

Dave Edis - Senior Developer
interactivetools.com

By andreasml - November 4

I created a new separator field and I put into an HTML separator type field your code. The outcome I get when I open one record from this section (visits section) is the following:

outpatients_clinicNum:
All record values:
Array
(
    [num] => 102
    [createdDate] => 2022-11-03 20:20:41
    [createdByUserNum] => 1
    [updatedDate] => 2022-11-03 21:16:08
    [updatedByUserNum] => 1
    [dragSortOrder] => 1667499641
    [foreignFieldNum] => 867
    [name_num] => 867
    [date_of_visit] => 2022-11-03 20:20:00
    [office] => Private
    [title] => ΑΚΑ μικρό
    [content] => <ul>
<li>Ασυμπτωματικό </li>
<li>ΜΔ ~4.6 AP</li>
<li>Καπνισττής</li>
</ul>
    [pmh_num] => 867
    [outpatients_clinicnum] => 
    [plan] => 	Review	
    [plan_details] =>  Επανεξέταση με νέο YX σε 6/12<br>
Σύσταση για διακοπή καπνίσματος
    [type_of_hospital] => 
    [hospital_name] => 
    [venue] => 
    [priority] => 
    [arranged] => 0
    [scheduled_date] => 0000-00-00 00:00:00
    [type_of_treatment] => 
    [_tableName] => visits
)

It seems that the first line 

<?php echo @$RECORD['outpatients_clinicNum']; ?>

does not have any outcome.