Announcement

Collapse
No announcement yet.

Single Form to compare records from 2 tables

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Single Form to compare records from 2 tables

    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.
    Albert Drent
    aducom software netherlands
    scriptcase partner, reseller, support and (turn-key) development
    www.scriptcase.eu / www.scriptcase.nl

    Comment


    • #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} = '';
      }

      Comment


      • #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.
        Albert Drent
        aducom software netherlands
        scriptcase partner, reseller, support and (turn-key) development
        www.scriptcase.eu / www.scriptcase.nl

        Comment

        Working...
        X