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

Thread: Combine 3 fields from 1 table, and compare to 3 fields in another table

  1. #1
    Member
    Real Name
    Dave Classick Jr
    Join Date
    Feb 2006
    Posts
    112

    Default Combine 3 fields from 1 table, and compare to 3 fields in another table

    Hey guys,

    I have a real quandry here, hopefully someone can point me in the right direction.


    I have a table, "pre-alerts", that has 3 fields: SerialNumber, LPO and APO

    I also have a master table that has the same 3 fields, plus many more

    What i need to do is combine the three fields in "pre-alerts", and compare them to the same fields combined in the other larger table and do the following

    Code:
    for each record in MasterTable
    If pre-alert->Serial+LPO+APO = MasterTable->Serial+LPO+APO
           Copy append record to  temporary table, "discreps"
    else
          Post pre-alert->APO to MasterTable->APO where pre-alert->Serial = MasterTable-> serial 
    
         Post pre-alert->LPO to MasterTable->LPO where pre-alert->Serial = MasterTable-> serial 
    Next
    My guess is this has to be some sort of nested for loop, or while loop., but i just dont know enough xbasic yet to code this myself.

    Can anyone lend some suggestions, or perhaps snippets?

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

    Default

    Quote Originally Posted by Classick
    Hey guys,

    I have a real quandry here, hopefully someone can point me in the right direction.


    I have a table, "pre-alerts", that has 3 fields: SerialNumber, LPO and APO

    I also have a master table that has the same 3 fields, plus many more

    What i need to do is combine the three fields in "pre-alerts", and compare them to the same fields combined in the other larger table and do the following

    Code:
    for each record in MasterTable
    If pre-alert->Serial+LPO+APO = MasterTable->Serial+LPO+APO
           Copy append record to  temporary table, "discreps"
    else
          Post pre-alert->APO to MasterTable->APO where pre-alert->Serial = MasterTable-> serial 
    
         Post pre-alert->LPO to MasterTable->LPO where pre-alert->Serial = MasterTable-> serial 
    Next
    My guess is this has to be some sort of nested for loop, or while loop., but i just dont know enough xbasic yet to code this myself.

    Can anyone lend some suggestions, or perhaps snippets?
    Why not just create a set linking master table to pre-alert with the linking expression Serial+LPO+APO? Then create a set linking mastertable to pre-alert with the linking field serial. Then create browses for each set displaying the fields you want to examine.

  3. #3
    Member
    Real Name
    Dave Classick Jr
    Join Date
    Feb 2006
    Posts
    112

    Default

    Hey Stan,

    thanks for your reply.

    i dont thiink that would work. Maybe i can better explain the goal.

    The master table contains records of units that were scanned into the receiving dept of this company.

    However, the only information that is scanned in (relative to this problem) is the serial number.

    currently, the LPO and APO are uploaded daily, as they are received from a different source. As it stands, the linking code between this opperation is Serial+isblank(shipdate) meaning that if the unit exisited in the database already, i would be a new instance of it if it hadnt shipped yet, otherwise it was repaired in the past and shipped off of the work in progress status.

    The problem that i am trying to solve, is that recently, we have noticed that the same unit (serial number) will exist more than once in the pre-alert file, leading to matching the wrong PO numbers.

    i am sorry if this sounds confusing, ill try to take some screen caps tomorrow to illustrate.

    but what needs to happen is a search of the master table, to see if the combo of Serial# and both po numbers exist, if they do... i need to capture which serials do and request new PO numbers for them.

  4. #4
    "Certified" Alphaholic Mike Wilson's Avatar
    Real Name
    mike wilson
    Join Date
    Apr 2005
    Location
    Grand Rapids, Michigan
    Posts
    4,231

    Default Maybe a start

    Dave,

    You wrote this:
    The problem that i am trying to solve, is that recently, we have noticed that the same unit (serial number) will exist more than once in the pre-alert file, leading to matching the wrong PO numbers.
    I don't know if this will help but the following code will mark the records with duplicate or more of the same serial numbers. Maybe you can do something with that. Assuming the serial number is numeric and serial number field is named 'snfield'.

    Happy trails,
    Mike W

    Code:
    dim tbl as p
    dim sntest as N
    
    tbl=table.open ("pre-alert")
    query.filter = ""
    query.order = ""
    query.options =""
    xi=tbl.query_create()
    
    tbl.fetch_first()
    while .not. tbl.fetch_eof()
        sntest = tbl.ssn
        probe = a5_get_records_in_query("pre-alert","snfield = "+quote(sntest))
           if probe <> 0 then
    	tbl.change_begin()
    	     tbl.mark()
    	tbl.change_end(.t.)
            end if
       tbl.fetch_next()
    end while
    xi.drop()
    tbl.close()

  5. #5
    "Certified" Alphaholic Mike Wilson's Avatar
    Real Name
    mike wilson
    Join Date
    Apr 2005
    Location
    Grand Rapids, Michigan
    Posts
    4,231

    Default oops, mistake in code

    Dave,
    Mistake (red) in code.

    Code:
    dim tbl as p
    dim sntest as N
    
    tbl=table.open ("pre-alert")
    query.filter = ""
    query.order = ""
    query.options =""
    xi=tbl.query_create()
    
    tbl.fetch_first()
    while .not. tbl.fetch_eof()
        sntest = tbl.snfield
        probe = a5_get_records_in_query("pre-alert","snfield = "+quote(sntest))
           if probe <> 0 then
    	tbl.change_begin()
    	     tbl.mark()
    	tbl.change_end(.t.)
            end if
       tbl.fetch_next()
    end while
    xi.drop()
    tbl.close()

  6. #6
    VAR
    Real Name
    Cheryl Lemire
    Join Date
    Jul 2003
    Location
    Pembroke Pines, FL
    Posts
    2,914

    Default

    Dave,

    When you are scanning the serial number, which table is this entered into? How are you bringing in the data for the LPO and APO and which table are they going into?

    Obviously a serial number can only be in house one time. I understand the same serial number can come back to receiving later for further repair or whatever, but it should only be there once with the ship date blank.

    My thought is that you want an update operation for the LPO and APO which would find all matching serial numbers where the ship date is blank, then assign the LPO and APO to that record.

    As long as your initial entry of the serial number being scanned is running the Serial+isblank(shipdate) there should be no duplicates there.
    Cheryl
    #1 Designs By Pagecrazy
    http://pagecrazy.com/

  7. #7
    Member
    Real Name
    Dave Classick Jr
    Join Date
    Feb 2006
    Posts
    112

    Default

    Quote Originally Posted by Cheryl Lemire
    Dave,

    When you are scanning the serial number, which table is this entered into? How are you bringing in the data for the LPO and APO and which table are they going into?

    Obviously a serial number can only be in house one time. I understand the same serial number can come back to receiving later for further repair or whatever, but it should only be there once with the ship date blank.

    My thought is that you want an update operation for the LPO and APO which would find all matching serial numbers where the ship date is blank, then assign the LPO and APO to that record.

    As long as your initial entry of the serial number being scanned is running the Serial+isblank(shipdate) there should be no duplicates there.
    Thanks for your reply Cheryl... currently we DO filter out for ship date..

    the problem is what to do with the ones that come up as a duplicate.

    Are they just duplicate serial numbers? or are they duplicate serial+APO+LPO?

    if the latter, then we need to isolate those serial numbers on a report and send them back to the supplier indicating that they need new PO #'s.

    on the surface that might seem easy, just make the key serial+apo+lpo when creating the post operation... but i cant do that because of the following example.

    The serial abc123 is in the system already WITH an LPO (123) and APO(abc) AND a shipdate

    abc123 just arrived again today (say two weeks later) but it was only received in, and doesnt have a PO #

    The opperation goes through, sees that abc123 doesnt have a shipdate and matches it up to the PO #'s incorrectly

  8. #8
    Member
    Real Name
    Dave Classick Jr
    Join Date
    Feb 2006
    Posts
    112

    Default

    I took the screen caps with excel, just to illustrate better

    They are pretty self explanitory, but ill write little descriptions

    Here is a glimpse of a query run off the master table.. you can see that the record that is repeated below is highligheted in red, and the ones that have no match are in green.



    Next, we have the Pre-alert file. this file is sent to us every day, with updated with more and more records. it is this reason that sometimes there are duplicate PO #'s and why i need to detect them and seperate them



    Here is what the pre-alert table should look like after all is said and done, note the missing record.



    And here is where the missing record was moved to.

  9. #9
    "Certified" Alphaholic Mike Wilson's Avatar
    Real Name
    mike wilson
    Join Date
    Apr 2005
    Location
    Grand Rapids, Michigan
    Posts
    4,231

    Default Clear mud

    Dave,
    Prior post:
    The serial abc123 is in the system already WITH an LPO (123) and APO(abc) AND a shipdate

    abc123 just arrived again today (say two weeks later) but it was only received in, and doesnt have a PO #

    The opperation goes through, sees that abc123 doesnt have a shipdate and matches it up to the PO #'s incorrectly
    I believe you when you say there is a mismatch of the SN and PO's incorrecty, but your example do not provide any clue why that might happen. It seems to me this is the source of your error and your issue. How is this mismatch occuring?

    Following Post:

    Beautiful thumbnails, clear as mud issue.

    you can see that the record that is repeated below is highlighted in red,
    No Master Table duplicates I can see, and nothing in red I can see.
    and the ones that have no match are in green
    What is not matching? I see them in the Master table once and in the Pre-alert table once. How does that equal a non-match. While there are no LPO and APO for the green items in the Master table, others don't have that either and they are not picked out, so it must not be that which distinguishes them as not matching.
    Here is what the pre-alert table should look like after all is said and done, note the missing record.
    And here is where the missing record was moved to.
    Is it missing, or is it moved? It seems to have been intentionaly moved to a different table, which makes this record moved and/or removed.. not missing. Very confusing? Sorry I couldn't help.
    Mike W

  10. #10
    Member
    Real Name
    Dave Classick Jr
    Join Date
    Feb 2006
    Posts
    112

    Default Re: Combine 3 fields from 1 table, and compare to 3 fields in another table

    ill try to clarify.


    when a package is recieved in to the DB, only the serial is entered. we rely on the pre-alert file to post the LPO and APO.

    So, week 1:

    serial# abc123 is received in, and xxx and yyy are entered from that days pre-alert.

    each prealert file is not created new each week, but a concatination of the previous weeks data.

    now jump to week 3

    the same unit is received in again... same serial number.. no po#'s

    the pre alert shows up and NOW contains 2 instances of the "key"
    serial+LPO+APO

    When the operation is run to match the PO's to the serial... naturally it will match the same PO's to that 2nd instance.

    THIS IS THE PROBLEM... i need to have some way to indicate that the 2nd instance on the pre-alert file needs a new set of PO's.

    The serial # in the master table needs to remain PO-less until the customer sends new data.

    hopefully this clarifies things. i know it is a little confusing, and very difficult to describe.

    so the problem isnt that they are mis-matched, it is that they ARE matched.

    i dont know enough xbasic yet to write the code from scratch to
    open the table ( or table map)
    compare each record in the prealert table
    with each record from a query of the master table
    and if the serial+LPO+APO matches, copy the record to the discreps table
    otherwise post the LPO and APO to the matching serial #

  11. #11
    VAR
    Real Name
    Cheryl Lemire
    Join Date
    Jul 2003
    Location
    Pembroke Pines, FL
    Posts
    2,914

    Default Re: Combine 3 fields from 1 table, and compare to 3 fields in another table

    Dave,

    I do not see a simple way of doing this, but here is my suggestion.

    I would create two new tables: 'alert' and 'alert_temp'. I would also, if you have not done so already, is have a field in the master_table for a unique_id ... I like to use: REMSPECIAL(API_UUIDCREATE()). When you get the prealert in for the day you would need to take several steps:

    1) import alert worksheet received into the _alert table.
    2) append operation from alert to alert_temp, do not append dupl serial+lpo+apo
    3) append operation from alert_temp to master_table, only append filtered: isblank("Inv_id")
    4) update operation on master_table to assign unique_id, only update filtered: isblank("unique_id")
    5) create new set: parent is alert_temp, one to one link with master_table as child - link: Serial_Num+LPO+APO
    6) update operation on new set, assign calc value: master_table->unique_Id
    selected records filter: isblank("unique_id").AND.isblank("master_table->ship_date")
    7) delete all records in alert table

    I hope that makes sense. Like I said, it will not be 'easy' but it should resolve your issues. I just tested the above scenario in an application I am working on by copying my master table into an alert table and an alert_temp table, in the alert table I removed the unique_id field values so they were blank, I also took the po# in the alert table for 4 records and deleted those values so they would be considered 'new'. Each operation either added the 4 records or updated the 4 records accordingly.

    Good luck
    Cheryl
    #1 Designs By Pagecrazy
    http://pagecrazy.com/

Similar Threads

  1. Combine zipcode and zip plus 4 fields?
    By Diana Faust in forum Alpha Four Version 6 and Prior
    Replies: 1
    Last Post: 12-08-2005, 07:37 PM
  2. How many fields can you have in a table?
    By Fred Daniel in forum Alpha Five Version 6
    Replies: 3
    Last Post: 07-04-2005, 10:49 PM
  3. combine 2 fields into 1 in a table.
    By chadg@epix.net in forum Alpha Five Version 5
    Replies: 2
    Last Post: 03-31-2005, 03:53 AM
  4. Compare Memo Fields
    By Jerry Gray in forum Alpha Five Version 6
    Replies: 17
    Last Post: 11-20-2004, 08:44 PM
  5. Posting Parent table fields to Child table
    By Jimmie1234 in forum Alpha Five Version 5
    Replies: 1
    Last Post: 09-11-2002, 12:41 PM

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
  •