Alpha Video Training
Results 1 to 8 of 8

Thread: Advice on validating data in a Table

  1. #1
    Member
    Real Name
    Paul Main
    Join Date
    Feb 2005
    Location
    Bucks, UK
    Posts
    206

    Default Advice on validating data in a Table

    I have what is a rather simple problem that Action Scripting seems to give no answers and for a novice coding is rather daunting.

    I have a table "T_inputs" that contains quote number "ID_Quote" and customer number "ID_Customer. I have a separate table "T_Customers" that contains customer number "ID_Customer1" and other data about the customer. What I want to do is check that all the quotes have valid customer numbers by comparing the customer numbers in the quote table with those in the customer table.

    What methods would you recommend I use. Are there any efficient ways to do this.

    thanks
    Paul

  2. #2
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Advice on validating data in a Table

    Quote Originally Posted by Paul Main View Post
    I have what is a rather simple problem that Action Scripting seems to give no answers and for a novice coding is rather daunting.

    I have a table "T_inputs" that contains quote number "ID_Quote" and customer number "ID_Customer. I have a separate table "T_Customers" that contains customer number "ID_Customer1" and other data about the customer. What I want to do is check that all the quotes have valid customer numbers by comparing the customer numbers in the quote table with those in the customer table.

    What methods would you recommend I use. Are there any efficient ways to do this.

    thanks
    Paul

    The easiest way I can think of is to create a set with t_inputs as the parent and t_customers as the child, linked on the id fields one to one. Now open the set and sort ascending on one of the child table fields. This will put the "empty" child records at the top, which should be the ones that did not have matches in the parent table.

  3. #3
    Member
    Real Name
    Paul Main
    Join Date
    Feb 2005
    Location
    Bucks, UK
    Posts
    206

    Default Re: Advice on validating data in a Table

    As so often happens the first reply exposses what was thought and not said. Appologies.

    The situation is that the quote table might have an invalid customer number in it. Therefore, when producing a quote and getting information passed in from the customer table it fails. Hence the need to periodically check the validity of the customer numbers in the quote record.

    I did think of stepping through the quotes and lookup each customer number but I could not figure out the code to do this.

    Paul

  4. #4
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Advice on validating data in a Table

    Quote Originally Posted by Paul Main View Post
    As so often happens the first reply exposses what was thought and not said. Appologies.

    The situation is that the quote table might have an invalid customer number in it. Therefore, when producing a quote and getting information passed in from the customer table it fails. Hence the need to periodically check the validity of the customer numbers in the quote record.

    I did think of stepping through the quotes and lookup each customer number but I could not figure out the code to do this.

    Paul
    Not sure why what I suggested fails. How about adding a field to the quote table. Post to the new field from the customer table, any value that the field will hold, based on id fields. This will indicate what records in the quote table do and don't have matching customer table records.

    Blank the new field before each run with an update operation.

  5. #5
    Member
    Real Name
    Paul Main
    Join Date
    Feb 2005
    Location
    Bucks, UK
    Posts
    206

    Default Re: Advice on validating data in a Table

    Stan, All I want to do is run a check routine that compares the customer numbers in the quote table with the master customer table to see if it is valid. The output would be a list of quotes with invalid customer numbers, which by definition would be that it does not exist in the customer master table.

    The routine I thought of would be

    Set index t_quote table to quote number ascending
    Set index t_customer table to customer number ascending
    Goto first record t_quote table
    do while .t. if quote number exist
    find t_quote.customerno in t_customer.customer table
    if .not. t_quote.customer = t_customer.customer
    print quote number
    endif
    goto next quote record

    Am I making sense?

  6. #6
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Advice on validating data in a Table

    Quote Originally Posted by Paul Main View Post
    Stan, All I want to do is run a check routine that compares the customer numbers in the quote table with the master customer table to see if it is valid. The output would be a list of quotes with invalid customer numbers, which by definition would be that it does not exist in the customer master table.

    The routine I thought of would be

    Set index t_quote table to quote number ascending
    Set index t_customer table to customer number ascending
    Goto first record t_quote table
    do while .t. if quote number exist
    find t_quote.customerno in t_customer.customer table
    if .not. t_quote.customer = t_customer.customer
    print quote number
    endif
    goto next quote record

    Am I making sense?
    You're making perfect sense. I just disagree with the method you want to use to get the list. If you used my last suggestion, you could export (excel or ascii) just the quote numbers based on isblank("addedfield").

    If' you'd rather code it in xbasic, go ahead. No hard feelings. I know how fast the post, update, and export operations run for something this simple. I don't know how fast a script would run that fetched through all the customers for each quote.


    I'm accustomed to multi-million record tables so I am probably biased towards certain solutions.

  7. #7
    Member
    Real Name
    Paul Main
    Join Date
    Feb 2005
    Location
    Bucks, UK
    Posts
    206

    Default Re: Advice on validating data in a Table

    Stan, thanks. I have just tried what you suggested and I can see how to use it. It also gives me the opportunity to make corrections.

    My thought pattern was to have a batch job to check the data and list the errors. Your way will work for small sets of mismatches which hopefully will only be rarely needed.

  8. #8
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Advice on validating data in a Table

    Quote Originally Posted by Paul Main View Post
    Stan, thanks. I have just tried what you suggested and I can see how to use it. It also gives me the opportunity to make corrections.

    My thought pattern was to have a batch job to check the data and list the errors. Your way will work for small sets of mismatches which hopefully will only be rarely needed.
    Great. Now consider changing the customer id field in your quote table to a lookup to the customer table. It could fill in the customer id and name. No possibility of mismatches.

Similar Threads

  1. validating password
    By Susie in forum Alpha Five Version 7
    Replies: 1
    Last Post: 02-02-2006, 06:33 AM
  2. Trouble validating dates
    By fergua in forum Alpha Five Version 7
    Replies: 1
    Last Post: 11-02-2005, 02:02 PM
  3. Advice needed on transferring data from flat db
    By Charlain in forum Alpha Five Version 5
    Replies: 2
    Last Post: 05-30-2003, 06:12 PM
  4. validating information
    By Donnie Mayes in forum Alpha Five Version 4
    Replies: 10
    Last Post: 01-17-2002, 08:24 AM
  5. help validating field rule
    By Darrell Payne in forum Alpha Five Version 4
    Replies: 2
    Last Post: 05-25-2001, 11:47 AM

Bookmarks

Posting Permissions

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