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

Remove all but the most current records

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

    #31
    Re: Remove all but the most current records

    Hmmm, I'll have to look at that. Thanks Tim.
    Sergeant Richard Hartnett
    Hyattsville City Police Department
    Maryland

    Comment


      #32
      Re: Remove all but the most current records

      As I was writing the script and before testing it, I thought of summarize.. so I left it there in the form in case anyone is interested in a different approach as I have eluded to very early in the thread..

      Originally posted by G Gabriel View Post
      Later..
      After minor head-scratching:
      You could make a very simple script that orders all children sorted by the linking field then by dates
      go down the records, preserving the ones that meet the desired criteria and deleting the rest..
      Last edited by G Gabriel; 11-18-2007, 03:20 AM.

      Comment


        #33
        Re: Remove all but the most current records

        Hi Tim,

        Originally posted by Tim Kiebert View Post
        If I apply Ira's filter to the 299 records I get 76 as a result. Examing these 76 I see that a couple of the sec_sub_pa values, (21-1205() and 21-1209()), each have three records with the same date. They have however different N7 values. So not sure if there is a problem with the original data but figured I'd bring it to your attention.
        Using Lookupx() you could do something like this for the filter. It absolutely will match 1 and only 1 ticket number (assumes character ticknum here and index name is ticknum+invert(cdate(datefield))

        recno()="lookupc('C','"+trim(ticknum)+"','ltrim(str(recno()))',dbf(),'indexname')"

        For 7,000 records, method and speed are not necessarily an issue, however the lookup functions run faster if the table is open before using them, so just open a reference to the table in the code if needed. It may be open already depending upon the context tat you run the filter.

        I suspect that the summarize is effectively faster in cases of larger tables (probably 100k +) but an actual comparison would need to be done. I'm also absolutely sure your looping method is the slowest method, although it obviously works as well.
        Regards,

        Ira J. Perlow
        Computer Systems Design


        CSDA A5 Products
        New - Free CSDA DiagInfo - v1.39, 30 Apr 2013
        CSDA Barcode Functions

        CSDA Code Utility
        CSDA Screen Capture


        Comment


          #34
          Re: Remove all but the most current records

          Hello Ira,

          Thanks for that. Yes that gets the same result as the summarize. But I wasn't sure where you used your construction. May be you meant to have the first quote before the first "recno()???

          Anyway I used this in the query by expression genie.
          Code:
          recno()=lookupn("C",Sec_Sub_Pa,"recno()","trafcodetesttable","Aaaaa")
          This though only finds or filters the records. Rich's aim was to eliminate all the other records. What would you recommend to get the wanted records into a table by them selves or get rid of the unwanted. Of course the filter works well enough that it may be just as easy to leave them all in.

          Also, do you know if it is possible to achieve a result table from a summarize operation without having the fieldnames prefixed with the summary type.
          Last edited by Tim Kiebert; 11-18-2007, 10:03 AM. Reason: spelingg
          Tim Kiebert
          Eagle Creek Citrus
          A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

          Comment


            #35
            Re: Remove all but the most current records

            Tim,

            The help file mentions this as well:

            Changing the Field Names in the New Summary Table

            Alpha Five creates the field names for the summary tables by using the summary method and original field name. for example if your original field is called value and the summary operation is to total that field then the new field name will be total_value. The procedure for changing the field names in the table is as follows.

            1.Create a new table with the same fields, but with the field names that you want.

            2.Append the summary table to the new table.

            3.Discard the summary table.
            http://support.alphasoftware.com/alp...Operations.htm

            But obviously is a completely separate Append Operation.

            I know...not really what you were asking! :)



            Using the code option of 15 --it represents Null
            eliminates the prefix...AND all the data in the column as well!


            Another possible way woud be to follow the summary operation with a TABLE_RESTRUCTURE() to change the field names...so guess this or an Append is all I could come up with.
            Last edited by MikeC; 11-18-2007, 11:32 AM. Reason: more info
            Mike
            __________________________________________
            It is only when we forget all our learning that we begin to know.
            It's not what you look at that matters, it's what you see.
            Henry David Thoreau
            __________________________________________



            Comment


              #36
              Re: Remove all but the most current records

              Hi Tim,

              Originally posted by Tim Kiebert View Post
              Yes that gets the same result as the summarize. But I wasn't sure where you used your construction. May be you meant to have the first quote before the first "recno()???
              Yes I did.

              Originally posted by Tim Kiebert View Post
              Anyway I used this in the query by expression genie.
              Code:
              recno()=lookupn("C",Sec_Sub_Pa,"recno()","trafcodetesttable","Aaaaa")
              This though only finds or filters the records. Rich's aim was to eliminate all the other records. What would you recommend to get the wanted records into a table by them selves or get rid of the unwanted. Of course the filter works well enough that it may be just as easy to leave them all in.
              To get everything but the records for the purpose of deletion, either use a .not.() around the expression, or
              Code:
              <>
              instead of the = sign. The issue is just dealing with the quotes to get a filter expression right, but modifying yours from above
              Code:
              "recno()<>lookupn('C',Sec_Sub_Pa,'recno()','trafcodetesttable','Aaaaa')"
              Originally posted by Tim Kiebert View Post
              Also, do you know if it is possible to achieve a result table from a summarize operation without having the fieldnames prefixed with the summary type.
              I don't think so, but I'd have to revisit it.

              Changed:
              I forgot to add in the qualifier so that the expression does not look at the next ticket number, so the equation changes a small bit so that it only is valid/invalid when the current ticket number and the look up are the same

              (don't have time to test now - will post later if needed)
              Last edited by csda1; 11-18-2007, 11:58 AM.
              Regards,

              Ira J. Perlow
              Computer Systems Design


              CSDA A5 Products
              New - Free CSDA DiagInfo - v1.39, 30 Apr 2013
              CSDA Barcode Functions

              CSDA Code Utility
              CSDA Screen Capture


              Comment


                #37
                Re: Remove all but the most current records

                Originally posted by csda1 View Post
                Using Lookupx()
                There is no such a fnction in alpha..
                Perhaps you meant: lookup("L"...)

                Comment


                  #38
                  Re: Remove all but the most current records

                  G,
                  Context! the x is near the c on the keyboard and Ira's example following this statement has lookupc()....just a simple typo.

                  BUT, if memory serves, a recent thread by an Alpha 4 user used lookupx() so do believe it is an old function used in early versions.
                  Mike
                  __________________________________________
                  It is only when we forget all our learning that we begin to know.
                  It's not what you look at that matters, it's what you see.
                  Henry David Thoreau
                  __________________________________________



                  Comment


                    #39
                    Re: Remove all but the most current records

                    Mike:
                    That context again.. I am the one who started that sermon about context, so I am quite familiar with it..

                    The first time....... I thought it was a typo.. The second time, Hmmm?

                    I believe it's an old function, plus other applications alo have such function.

                    Comment


                      #40
                      Re: Remove all but the most current records

                      G,

                      I suspect Ira was using the term "lookupx()" on purpose as a deliberate shortcut for saying "lookupn() or lookupc() or lookupd() or lookupL() depending upon what data type the search key might be".

                      Let the "x" be a wildcard and you will see what I mean, and what I think Ira was saying. Sometimes one can go off track by taking things too literally. Happens to me a lot. :) -- tom

                      Comment


                        #41
                        Re: Remove all but the most current records

                        I am not really sure why this thread is still going..
                        I am not following this discussion back and forth about lookup, but just couple of things caught my eye:

                        I don't know what to make of this:
                        recno()="lookupc('C','"+trim(ticknum)+"','ltrim(str(recno()))',dbf(),'indexname')"

                        second, while I agree you can filter with lookup, yet lookupc or lookupn using "C" will not produce the desired outcome.

                        There is a much simpler solution, but I think I am stumpling on a bug.. still working on it.

                        Comment


                          #42
                          Re: Remove all but the most current records

                          Tom:
                          Yes, I made all of the above assumptions and I am not here trying to discredit Ira either, only pointing out that there isi no such function (it was brought up in a different thread as a function in alpha), at least not now.

                          Comment


                            #43
                            Re: Remove all but the most current records

                            Tom,

                            That is correct.

                            Mike: Xlookup() is a function that is similar from Alpha 4, and was the reference someone saw in recent threads elsewhere.

                            Alpha, for no really good reason (except it might have been very early in A5 development), has separate functions depending upon the return value type. This was not really required when designing a function, as it could have returned the type based upon what the expression evaluated.

                            Instead, Alpha gave us LookupC(), LookupN, LookupD, LookupL which are all essentially the same function. It has been common practice to refer to these as LOOKUPx() because it all depends on what you want to return, and the person writing the expression should take it as a "use appropriate type" as needed.

                            If I state "characterfield", I don't expect any one to use that name verbatim, nor if I ask someone to press "Any Key", I don't typically expect any user to search for the "Any" key!:)

                            As for the lookupc() expression that I did give (except for the incorrect placement of the quote pointed out by Tim) does in fact select correct records (which I explained how to take all except those records) in that generic requirement, but was not tailored to the specific database. I also mentioned that you can correct for the case where "C" closest selection goes to the next ticket number, in which case you need to qualify the result comparing the ticket number to the lookup ticket number.

                            When I say "similar to", it means that the reader is expected to modify from the sample given to the exact needs of the situation.
                            Regards,

                            Ira J. Perlow
                            Computer Systems Design


                            CSDA A5 Products
                            New - Free CSDA DiagInfo - v1.39, 30 Apr 2013
                            CSDA Barcode Functions

                            CSDA Code Utility
                            CSDA Screen Capture


                            Comment

                            Working...
                            X