New call-to-action
Results 1 to 4 of 4

Thread: Complicated data validation

  1. #1
    Real Name
    Ron Shenk
    Join Date
    Jun 2013

    Default Complicated data validation

    I am working with a client on a database applicateion. I need a complicated validation for their application that will need to involve a SQL query. Currently I have a dialog entry screen that is triggered on entry or edit. I assume that I can write either custom javascript or xbasic to do what I need but that is honestly a bit over my head.

    The validation needs to look at three fields on the entry screen and determine if that combination already exists or is unique. My Idea was to put code in the validation portion of the third field that would do a sql query and see if there were any records returned in the resultset. If there were a record the validation would fail with a message. In this case I need to have a unique combination of DevelopmentName, Unit Number and Time Period. Only one record can exist in the development for the unit number and time period.

    This table is used for 8 developments and only two of them require this validation on the record.

    I have been combing documentation and looking at examples in the client side and server side functions but haven't come up with anything to help.

    I have developed this application without need for any real coding just standard Alpha 5 web components so I am really new to having to write javascript or xbasic.

    I did have to write a conversion routine in xbasic to convert data from old system to MS Sql database but not part of a validation.

  2. #2
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    In the Wolds, Louth, Lincolnshire, UK

    Default Re: Complicated data validation

    The easiest way to validate 3 fields is to Concatenate them in a redundant field. It drives purist nuts but is simple.

    Field 1 =abc
    Field 2 = 999
    Field 3 = xyz

    I have edited this 3 times now as some characters are not allowed.

    Field 4 which is Calculated = abc999xyz
    Set it as Unique or Primary and it will trap dupes.
    Ted Giles
    Example Consulting - UK
    See our site for Alpha Support, Conversion and Upgrade.

  3. #3
    Real Name
    Sean OKelly
    Join Date
    Sep 2015
    Charleston, WV

    Default Re: Complicated data validation

    Here is a simple option if you are working with UX controls (code is just taken from other examples - write your own):

    Create a hidden list object. Put it on a panel card that you don't show.
    Add a search part to the list
    Use the three fields as criteria for the search part

    Perform the search example:
    var listObj = {dialog.object}.getControl("LIST1");

    if (listObj) {
    listObj.searchList({searchMode : 'auto', maxRows: 1});

    After the list has been filtered, you can use javascript to determine the number of records:
    var lObj = {dialog.object}.getControl('ID_of_your_list');
    var count = lObj._state.recordCount;

    There are many ways to do what you want. This is just another one.

  4. #4
    "Certified" Alphaholic
    Real Name
    Gregg Schmidt
    Join Date
    Mar 2001

    Default Re: Complicated data validation

    Hi Ron,

    I'm going to start by saying I haven't tested the code shown below but the idea is
    to use that function to validate the data entered. If the function finds a match
    (development name , unit number, and time period) it will return as true.

    From there, the code is simply if dataexists(control1,control2,control3),"Failed","Passed") .

    I can give a better example if I have actual data to work with.

    function dataexists as l (devname as c, unitnum as c, timeperiod as c)
    dim funcArgs as sql::arguments
    dim funcCN as sql::cn
    set = devname
    set funcArgs.unit = unitnum
    set funcArgs.period = timeperiod
    if sql_lookup(funcCN,<sqlTable>,"develpmentname = :name and unitnumber=:unit and timeperiod=:period","count(*)",funcArgs) > 0 then
    	dataexists = .t.
    	dataexists = .f.
    end function

Similar Threads

  1. grid data validation
    By bustamorg in forum Application Server Version 11 - Web/Browser Applications
    Replies: 5
    Last Post: 01-05-2015, 01:46 PM
  2. Data Validation Error Message
    By mikeallenbrown in forum Mobile & Browser Applications
    Replies: 1
    Last Post: 12-30-2013, 05:28 PM
  3. Data Validation Expression
    By mikeallenbrown in forum Application Server Version 11 - Web/Browser Applications
    Replies: 11
    Last Post: 06-20-2013, 10:14 AM
  4. Dialog V11 data validation
    By Mark Hosken in forum Application Server Version 11 - Web/Browser Applications
    Replies: 1
    Last Post: 02-18-2013, 12:14 PM
  5. Data validation
    By Garry Flanigan in forum Code Archive
    Replies: 2
    Last Post: 01-04-2008, 06:57 PM


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts