Alpha Video Training
Results 1 to 24 of 24

Thread: Update Query for Groups of Records

  1. #1
    Member
    Real Name
    Connie DeCinko
    Join Date
    Mar 2008
    Location
    Phoenix, AZ
    Posts
    72

    Default Update Query for Groups of Records

    Rather than struggle with this all day, I thought I would ask for help from all you smart people.

    I have a table similiar to:
    ItemNo ItemCategory ItemSortNo

    ItemNo is unique, and ItemCategory is repeated several times as a category can have multiple items. Out of say 100 records, 5 are assigned to category 1001. I need to set an incremental value of ItemSortNo for those 5. Then do the same for category 1002, etc. So for each category group, the ItemSortNo starts over again at 1.

    I can update one at a time by setting a filter on that category number. What I need to do is get the list of category numbers and loop over them to perform the operation on the entire table.

    I'm used to SQL where I might query and get a result set of just that column, turn it into an array or list and use that to loop with. However, it appears that in Alpha, you get the entire record, all columns.

    So, how does one perform such an operation as I've laid out?

  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: Update Query for Groups of Records

    Quote Originally Posted by CDeCinko View Post
    So, how does one perform such an operation as I've laid out?
    By entering a filter expression for the operation.

  3. #3
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,300

    Default Re: Update Query for Groups of Records

    Without claiming to be one of the smart people it occurs to me that one solution would be to open an instance of the table with a script. Then sort it based on the ItemCategory field. That can be done using a query. It can also be done using a permanent index.

    Then initialize a counter with the number 1, fetch the first record, assign counter value to the record.

    then fetch the next record, if the category field is the same as the prevoius record, increment counter and assign it to the record, otherwise reset the counter to 1 and assign that value to the record.

    fetch the next record and do the same thing.

    continue until you've processed them all; then close the table


    The xbasic commands you'll need:

    <tbl>.open() and <tbl>.close()
    <tbl>.fetch_first()
    <tbl>.fetch_next()
    while .not. <tbl>.fetch_eof()
    end while
    <tbl>.change_begin()
    <tbl>.change_end()
    <tbl>.index_primary_put() or <tbl>.query_create()

    A few minutes in the xbasic reference and all will become clear.
    Last edited by Tom Cone Jr; 04-30-2008 at 01:29 PM.

  4. #4
    Member
    Real Name
    Connie DeCinko
    Join Date
    Mar 2008
    Location
    Phoenix, AZ
    Posts
    72

    Default Re: Update Query for Groups of Records

    I got that far, but what would expression would I use so it groups by the category?

  5. #5
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default Re: Update Query for Groups of Records

    I need to set an incremental value of ItemSortNo for those 5.
    Why?

    Seems redundant to me.

    In order to assign a sort number to the items (whether you do that with a script or black magic) you have to sort those items somehow or decide on how the sort number should be assigned, i.e., sort them.
    If you could sort these items, then why do you need the field ItemSortNo?

    Sounds to me, you simply need either an index, a range or a query with the proper sort order.

  6. #6
    Member
    Real Name
    Connie DeCinko
    Join Date
    Mar 2008
    Location
    Phoenix, AZ
    Posts
    72

    Default Re: Update Query for Groups of Records

    First, it's not my table so not my decision on having or not having a sort order column. But at some point in time, the record order or item number sort order will not always match the preferred sort order. Today they do only because the data has been recently added. Problem is, some short sighted code caused the sort order to number from 1 to 999 for the entire table instead of within each category. Now I need to correct it.

  7. #7
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default Re: Update Query for Groups of Records

    Connie:
    Help me out here because I am still foggy on this:
    If I were to suggest anything, I would probably repeate what Tom has already said. But, to implement that, you have to make a decision on how you are going to order these records in the first place in order to run the script.
    So, let's do this baby steps:
    How are you going to order these records?

  8. #8
    Member
    Real Name
    Connie DeCinko
    Join Date
    Mar 2008
    Location
    Phoenix, AZ
    Posts
    72

    Default Re: Update Query for Groups of Records

    Order by ItemCategory, then by ItemNo. So initially the sort of ItemNo and ItemSortNo would be the same. Later we'll adjust via a form so they may not be the same.

  9. #9
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default Re: Update Query for Groups of Records

    Later we'll adjust via a form so they may not be the same.
    And what will it be later?
    I am not sure if I am making my point clear?
    In order to assign a sort number, whether now or in the future, .. YOU HAVE TO SORT THE RECORDS FIRST... so what's the point of having that number?

    Granted, you might be abiding by someone else's idiocracy... but in th end, you will have to sort the records in order to give them a sort number.. and since we live in a world mostly run by idiots, I guess you have to add that idiotic sorting field..

    So, follow what Tom has suggested with the additional step of sorting the records first in order to give them the proper sort order.

    Sorry if my reaction seems improper, it's just certain things strike me as odd..

  10. #10
    "Certified" Alphaholic
    Real Name
    Mike Christensen
    Join Date
    Nov 2005
    Location
    Michigan U.P.
    Posts
    5,937

    Default Re: Update Query for Groups of Records

    I can see a use for a sort_order field and also can see why it makes no difference intially what the order may be. I use just such an order field to manipulate the order of records in more than one of my embedded browses based on one-to-many sets.

    If this is the case then the category has to be sorted at first but other than that no other sort is necessary as when the sort_order field is created most likely via Tom's code (as that does seem the simplest approach), then the records can be sorted with a script later on. If this sort_order field is to be used to change the order at some time or even many times in the future, then what Connie wants is just a field that will regulate the order of the records...not related to anything else at all.

    But then maybe I am misinterpreting what Connie's (her boss's use) will be. :)
    Last edited by MikeC; 04-30-2008 at 04:17 PM.
    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
    __________________________________________




  11. #11
    Member
    Real Name
    Connie DeCinko
    Join Date
    Mar 2008
    Location
    Phoenix, AZ
    Posts
    72

    Default Re: Update Query for Groups of Records

    Yes, thank you for seeing what I am trying to do. I have some basic code that will number all records. I just need a nudge in where/how to reset the number for each change in the category.

    Code:
    dim tbl as p
    dim shared query as p
    dim shared query.filter as c
    dim shared query.order as c
    dim shared query.options as c
    dim shared query.description as c
    dim shared srtCounter as n
    
    tbl = table.open("bidassmbly")
    
    query.filter = ".t."
    query.order = "Bidsheet_No"
    query.options = ""
    
    tbl.query_create()
    
    srtCounter = 1
    
    tbl.fetch_first()
    while .not. tbl.fetch_eof()
    	tbl.change_begin()
    		tbl.__SrtOrderTest = srtCounter
    	tbl.change_end(.t.)
    	srtCounter = srtCounter + 1
    	tbl.fetch_next()
    end while
    tbl.close()
    end
    Quote Originally Posted by MikeC View Post
    I can see a use for a sort_order field and also can see why it makes no difference intially what the order may be. I use just such an order field to manipulate the order of records in more than one of my embedded browses based on a one-to-many set.

    If this is the case then the category has to be sorted at first but other than that no other sort is necessary as when the sort_order field is created most likely via Tom's code (as that does seem the simplest approach), then the records can be sorted with a script later on. If this sort_order field is to be used to change the order at some time or even many times in the future, then what Connie wants is just a field that will regulate the order of the records...not related to anything else at all.
    One use for this sort_order is for displaying in a browse a specific order. For instance, you need to assemble the parts of a sandwich in the correct order reguardless of what order you chose your ingredients.

  12. #12
    "Certified" Alphaholic
    Real Name
    Mike Christensen
    Join Date
    Nov 2005
    Location
    Michigan U.P.
    Posts
    5,937

    Default Re: Update Query for Groups of Records

    Connie,
    My scripting usually gets the job done but is not the "nicest" looking by far!! I think this would work for you, but if others here give another way definitely try theirs too! This can most likely be abbreviated but is much easier to understand when in the form I am giving.

    The last part of your script add what is in red.

    Code:
     
    dim vCategory as c
     
    tbl.fetch_first()
    vCategory=tbl.Bidsheet_No
    while .not. tbl.fetch_eof()
       if vCategory<>tbl.Bidsheet_No
         srtCounter=1
          tbl.change_begin()
          tbl.__SrtOrderTest = srtCounter
          tbl.change_end(.t.)
          srtCounter = srtCounter + 1
          tbl.fetch_next()
        else
          tbl.change_begin()
          tbl.__SrtOrderTest = srtCounter
          tbl.change_end(.t.)
          srtCounter = srtCounter + 1
          tbl.fetch_next()
       end if
    end while
    tbl.close()
    end
    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
    __________________________________________




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

    Default Re: Update Query for Groups of Records

    try this. This should check the current record category against the previous record category and increment the counter when the category changes.

    Code:
    dim tbl as p
    dim shared query as p
    dim shared query.filter as c
    dim shared query.order as c
    dim shared query.options as c
    dim shared query.description as c
    dim shared srtCounter as n
    dim vCatNow as C
    dim vCatPast as C
    
    tbl = table.open("bidassmbly")
    
    query.filter = ".t."
    query.order = "Bidsheet_No"
    query.options = ""
    
    tbl.query_create()
    srtCounter = 1
    
    tbl.fetch_first()
    	vCatPast = tbl.category
    while .not. tbl.fetch_eof()
    	vCatNow = tbl.category	
    		if vCatNow <> vCatPast
    				srtCounter = srtCounter + 1
    		end if
    	tbl.change_begin()
    		tbl.__SrtOrderTest = srtCounter
    	tbl.change_end(.t.)
    		vCatPast = vCatNow
    	tbl.fetch_next()
    end while
    tbl.close()
    end
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

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

    Default Re: Update Query for Groups of Records

    Late to the feast but should this portion
    Code:
    if vCatNow <> vCatPast
    	srtCounter = srtCounter + 1
    end if
    be

    Code:
    if vCatNow <> vCatPast
            srtCounter = 1
    else
    	srtCounter = srtCounter + 1
    end if

  15. #15
    Member
    Real Name
    Connie DeCinko
    Join Date
    Mar 2008
    Location
    Phoenix, AZ
    Posts
    72

    Default Re: Update Query for Groups of Records

    Yes, folks we finally have a winner! Here is the working code:

    Code:
    dim tbl as p
    dim shared query as p
    dim shared query.filter as c
    dim shared query.order as c
    dim shared query.options as c
    
    tbl = table.open("bidassmbly")
    
    query.filter = ".t."
    query.order = "Bidsheet_No"
    query.options = "M"
    
    tbl.query_create()
    srtCounter = 0
    
    tbl.fetch_first()
    	vBidsheetNoPrev = tbl.Bidsheet_No
    
    while .not. tbl.fetch_eof()
    	vBidsheetNoCur = tbl.Bidsheet_No
    
    	if vBidsheetNoCur <> vBidsheetNoPrev
    		srtCounter = 1
    	else
    		srtCounter = srtCounter + 1
    	end if
    	
    	tbl.change_begin()
    		tbl.__SrtOrderTest = srtCounter
    	tbl.change_end(.t.)
    		vBidsheetNoPrev = vBidSheetNoCur
    	tbl.fetch_next()
    end while
    tbl.close()
    end

  16. #16
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,300

    Default Re: Update Query for Groups of Records

    Cool!

    -- tom

  17. #17
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default Re: Update Query for Groups of Records

    This thread leaves me with the newly introduced glorious idea that:

    For each sort order in an embedded browse, you need a field in the table dedicated just for that!

    I don't know how I got along all these years without it!

    By the way, you don't (and perhaps shouldn't) dim query, query.filter etc. These are system variables.

  18. #18
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,300

    Default Re: Update Query for Groups of Records

    G has a good point about dimming the query.filter, query.options, etc.

    The query object is global. It's various properties are available to you without declaring them in your scripts.

    -- tom

  19. #19
    Member
    Real Name
    Connie DeCinko
    Join Date
    Mar 2008
    Location
    Phoenix, AZ
    Posts
    72

    Default Re: Update Query for Groups of Records

    Quote Originally Posted by G Gabriel View Post
    By the way, you don't (and perhaps shouldn't) dim query, query.filter etc. These are system variables.
    Hmm... I got that right out of some sample code in Dr. Wayne's XBasic book, page 80. That's also the only place I've seen those variables dimmed as shared.

  20. #20
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default Re: Update Query for Groups of Records

    I don't think there is harm in that, it just seems unnecessary.

  21. #21
    "Certified" Alphaholic
    Real Name
    Mike Christensen
    Join Date
    Nov 2005
    Location
    Michigan U.P.
    Posts
    5,937

    Default Re: Update Query for Groups of Records

    G,
    For each sort order in an embedded browse, you need a field in the table dedicated just for that!
    Are you implying that it is possible to move records up and down in regards to how they display without having such a field?? Maybe by manipulating the record number, but this has been brought up in the past and everyone else seems to feel that a field used specifically for this purpose is the easiest way to do it (by everyone I mean from the several posts I had found regarding this).

    Take my screenshot for example--how would you move the product in Lane A-1 down to Lane B-2 without having Lane numbers (or NOT using the lane numbers or an additional field to do it)?? Psudocode is all I am asking for really.....


    Attachment 17176
    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
    __________________________________________




  22. #22
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default Re: Update Query for Groups of Records

    Are you implying that it is possible to move records up and down in regards to how they display without having such a field??
    The question was not about moving records.. was about sorting..
    But, even if you want to actually move the records, you can.. easily..I do that in my calendar program all the time.
    There was a thread over a year ago about moving records. I attached a zip file showing how you can do it.

    The logic behind a field to sort a table escapes me. In order to give that field values in a certain order, you have to do just that, establish a certain order. If you can establish a certain order, then what's the point of having that field?

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

    Default Re: Update Query for Groups of Records

    Quote Originally Posted by Stan Mathews View Post
    Late to the feast but should this portion
    Code:
    if vCatNow <> vCatPast
    	srtCounter = srtCounter + 1
    end if
    be
    Code:
    if vCatNow <> vCatPast
            srtCounter = 1
    else
    	srtCounter = srtCounter + 1
    end if
    Yup! Thanks Stan. I was mistakenly thinking all in the category would have the same sort number and the succeding category would be incremented. I completely missed this statement in Connies first post:
    ...So for each category group, the ItemSortNo starts over again at 1.
    .
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

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

    Default Re: Update Query for Groups of Records

    Hey, no big thing. This is a group effort, correct?

Similar Threads

  1. Browse does not clear records after query and 0 records found
    By georgefurnel in forum Alpha Five Version 8
    Replies: 3
    Last Post: 03-23-2008, 05:20 PM
  2. Update Operation Not Finding Records to Update
    By davidab in forum Alpha Five Version 8
    Replies: 11
    Last Post: 11-13-2007, 09:44 AM
  3. Right-Click Menus and selected groups of records
    By Graham Wickens in forum Alpha Five Version 7
    Replies: 4
    Last Post: 02-22-2007, 01:25 PM
  4. Update fails to find any records to update
    By joshua brimdyr in forum Alpha Five Version 5
    Replies: 6
    Last Post: 03-11-2003, 03:47 AM
  5. Groups and printing groups
    By gatosub in forum Alpha Five Version 4
    Replies: 5
    Last Post: 08-03-2000, 08:15 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
  •