Displaying data from linked tables

It's a simple scenario - admin reviews a submission by a registered user, sets it to publish and sends them email.

To send the email I need to get the user's email from the jos_users db.
The relationship is standard (cf_user_id from jos_submissions = id from jos_users).

I assume I'm right in using two DB Record Loader actions.

The first is displaying data from the jos_submissions table, no problem.

How do I get the second to display name and email from the jos_users table?

I have set a Model ID 'User' in the jos_submissions DB Loader and tried a number of WHERE statements in the jos_users DB Loader none of which work:

On the form preview is a custom code element:
<?php echo "Submitted by ".$user['name']." ".$user['email']; ?>

I've swopped things around endlessly but don't really understand which of the Model IDs and WHERE statements are doing the work!

The full current settings are:
DB Record Loader 1 (working OK)

DB Field: cf_id
Table: jos_submissions
Request Param: cf_id
Load Fields: Yes
Model ID: User
Load Under Model ID: No
WHERE statement (empty)

DB Record Loader 2 (producing no results)

DB Field: id
Table: jos_users
Request Param: id
Load Fields: Yes
Model ID:
Load Under Model ID: No
WHERE statement (below)

<?php
$user = $form->data['User'];
?>
`id` = <?php echo $user['cf_user_id']; ?>

Any guidance would be appreciated,
Nick

Hi Nick,

Assuming you are using "User" as Model Id in record loader#1 then all data loaded from it will be under

$form->data['User']

So, to use it in record loader #2 you need this code instead:

<?php
$user = $form->data['User'];
?>
`id` = '<?php echo $user['cf_user_id']; ?>'

I hope this will do it!🙂

Regards,
Max

Thanks for quick response Max.
Unfortunately this made no difference.
I've tried many permutations - could I ask you to take another look?

If it's relevant, the form in question is being called from a list on the basis of cf_id, eg
http://www.mysite.com/admin/unpublished-playwright-profiles?chronoform=psplaywright-review&cf_id=14
Is this messing things up?

Nick

Hi Nick,

Please drag a "Debugger" action after the 2nd record loader and show me the debug output, it should display the DB Query used and this may give us some hints.

Regards,
Max

Thanks,

Does this help to explain?

Debug Data

1. SELECT * FROM `jos_users` AS `JosUsers` WHERE `id` = ''

Nick

Can anyone help on this?
Nick

Hi Nick,

Strong apologies for not responding sooner!

I found a bug in that action, that's why it doesn't work for you, you may contact me through the "Contact Us" page to get the patch.

Regards,
Max

Thank you Max,

This works fine now and the code is below.
Just a reminder that I'm getting the user's email address from a membership application.

On Load
The first thing in here is an Authenticator to restrict access, followed by the usual Show html.

DB Record Loader 1

DB Field: cf_id
Table: jos_members
Request Param: cf_id
Load Fields: Yes
Model ID: Member
Load Under Model ID: Yes
WHERE statement (empty because the form is already called from a single cf_id link)

DB Record Loader 2

DB Field: id
Table: jos_users
Request Param: id
Load Fields: No
Model ID: Registrant
Load Under Model ID: Yes
WHERE statement (below)

<?php
$detail = $form->data['Member'];
?>
`id` = '<?php echo $detail['cf_user_id']; ?>'

On the Preview side, the fields can be displayed using custom code with any fields represented in the following format:

{Member.name}
{Registrant.email}

Thanks for sharing!🙂