Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

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

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

    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
    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.
    There can be only one.

    Comment


      #3
      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.

      Comment


        #4
        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()
        Mike W
        __________________________
        "I rebel in at least small things to express to the world that I have not completely surrendered"

        Comment


          #5
          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 = [COLOR="Red"]tbl.snfield[/COLOR]
              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()
          Mike W
          __________________________
          "I rebel in at least small things to express to the world that I have not completely surrendered"

          Comment


            #6
            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/

            Comment


              #7
              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

              Comment


                #8
                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.

                Comment


                  #9
                  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
                  Mike W
                  __________________________
                  "I rebel in at least small things to express to the world that I have not completely surrendered"

                  Comment


                    #10
                    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 #

                    Comment


                      #11
                      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/

                      Comment

                      Working...
                      X