Single Form to compare records from 2 tables

Applications Forms
,
#1

Hi

I have x2 tables in my database.

  1. Source Data - this will be used to pre-populate a web form.
  2. Response table - these will be the survey responses.

What I need to do is compare certain core fields like location.

I currently have my form (single row) all setup nicely pulling data from the response table which I am able to review. However, what I need is to supplement the form as follows
Column 1 - Source data fields
Column 2 - Response data fields

If there is a mismatch, then I will need to identify it at thsi stage.

Is it possible to pull from x2 tables in one form?

Thanks

#2

Yes, but the second form needs to be done manually. Just create the number of fields needed and in the onload event you do a select on the second table and move the fields into the custom fields.

#3

Great - so I have setup the fields etc, but my skills stop short of SQL Select statements.
Could you please assist me

My primary key in the existing table/form: “AssetID”
Second table I want to query: “dbo.TBL_MASTER_ASSETS”
Column: Status.
Target field on form: “Master Status”
If status doesn’t exist, then the form should highlight the empty field indicating an action is required

/**

  • Selecting a field from another table
    */

// Check for record
$check_sql = “SELECT state_name, region”
. " FROM States"
. " WHERE state_id = ‘" . {field_state_id} . "’";
sc_lookup(rs, $check_sql);

if (isset({rs[0][0]})) // Row found
{
{other_field} = {rs[0][0]};
{other_region} = {rs[0][1]};
}
else // No row found
{
{other_field} = ‘’;
{other_region} = ‘’;
}

#4

To highlight an empty field will be a problem as afaik there are no macro’s to change the appearance of an input field. What might be helpful is to go to the sql builder and create the select for you. Then you only need to copy paste this in the $check_sql.
The highlight issue might be a problem, but a work-around might be to add additional fields and set label property to true. Set the description blank and set if it qualifies.

original-field label-field compare-field

if no match then labelfield = ‘>>’;

to show the field which needs attention. Top make the fields this way you can create a block with 3 columns.

It will require some fiddling, but fun and doable.