Update Query for Groups of Records

[CDeCinko]

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?

[Stan Mathews]

Re: Update Query for Groups of Records

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

By entering a filter expression for the operation.

[Tom Cone Jr]

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.

[CDeCinko]

Re: Update Query for Groups of Records

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

[G Gabriel]

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.

[CDeCinko]

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.

[G Gabriel]

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?

[CDeCinko]

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.

[G Gabriel]

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

[MikeC]

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. :)

[CDeCinko]

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.

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

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.

[MikeC]

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.

 
[COLOR=red]dim vCategory as c[/COLOR]
 
tbl.fetch_first()
[COLOR=red]vCategory=tbl.Bidsheet_No[/COLOR]
while .not. tbl.fetch_eof()
[COLOR=red]   if vCategory<>tbl.Bidsheet_No[/COLOR]
[COLOR=red]     srtCounter=1[/COLOR]
[COLOR=red]      tbl.change_begin()[/COLOR]
[COLOR=red]      tbl.__SrtOrderTest = srtCounter[/COLOR]
[COLOR=red]      tbl.change_end(.t.)[/COLOR]
[COLOR=red]      srtCounter = srtCounter + 1[/COLOR]
[COLOR=red]      tbl.fetch_next()[/COLOR]
[COLOR=red]    else[/COLOR]
      tbl.change_begin()
      tbl.__SrtOrderTest = srtCounter
      tbl.change_end(.t.)
      srtCounter = srtCounter + 1
      tbl.fetch_next()
[COLOR=red]   end if[/COLOR]
end while
tbl.close()
end

[Mike Wilson]

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.

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

[Stan Mathews]

Re: Update Query for Groups of Records

Late to the feast but should this portion

if vCatNow <> vCatPast
	srtCounter = srtCounter + 1
end if

be

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

[CDeCinko]

Re: Update Query for Groups of Records

Yes, folks we finally have a winner! Here is the working 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