Difficulty in setting Related Records

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.

Hi Dave

It worked! Many thanks for your advice.

Kind regards, 

Andreas

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

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!

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

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!

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.

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.