Assign Sequential number based on field value

[pkdata]

I have a master file of 2.3mm records containing 84 unique "campaigns" and I need to assign a sequential RECNO() for each record of each campaign. Any help is greatly appreciated.

 

[Mike Lewis]

Try this:

1. Create a new integer field. Call it, say, ID.

2. Execute this code from the command window:

REPLACE ALLL ID WITH RECNO()

I think that will do what you want.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Or, do you mean you want the ID to be unique within the campaign? In other words, Campaign 1 has IDs 1, 2, 3, .... Campaign 2 has IDs 1, 2, 3, .... . And so on.

If so, something like this will do it:

lnCampaign = 0
lnID = 0
SELECT TheTable
SCAN
  IF TheTable.CamPaignID <> lnCampaign
    lnID = 0
    lnCampaign = TheTable.CampaignID
  ENDIF
  lnID = lnID + 1
  REPLACE ID WITH lnID
ENDSCAN

This is off the top of my head (and done in a hurry), but it should give you the general idea.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[pkdata]

Thanks Mike, but that assigns the record number for each record in the entire file without looking at the campaign value.
I need the recno() assigned uniquely for each campaign; I will end up with 84 records with a recno() of 1, for example.

 

[pkdata]

Thanks again Mike.

Using code in your second reply, if ID field set to integer, all records are populated with 1.
If ID field set to numeric, standard RECNO() for entire file is populated.

 

[Olaf Doschke]

With Mikes second code, the SCAN must be in order of campaigns. Or in other Words Mike assumed the physical order of records is in campaign order. INDEX ON CampaignID TAG CampaignID once and before the scan SET ORDER TO CampaignID, then it will work and only reset lnID with each new campaign.

Bye, Olaf.

 

[pkdata]

Thanks Olaf, but still didn't work. Might have to rethink process.

 

[Olaf Doschke]

No, that process is ok, where did you now put the INDEX and SET ORDER?

Bye, Olaf.

 

[Mike Lewis]

... still didn't work. Might have to rethink process.

You shouldn't have to re-think the process.

I think my code should work (thanks to Olaf's point about being sure the table is in index order). If it doesn't, it's probably a simple error rather than anything fundamentally wrong.

In what way didn't it work. What result did you get?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

Perhaps he only has one record per campaign?

That would result in every ID being set to 1

B-)

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Olaf Doschke]

Well, lets hear the error or result.

What we know is the table has the 2.3 million records of 84 campaigns, the campaignid and id are fields of that table. What we don't yet know is the field names and types. Numeric fields are assumed for both fields, the source code makes that rather clear.

All names must be changed to your situation and of course you also need to open the table, eg USE TheTable in advance to be able to SELECT TheTable and SCAN it, but that should be obvious.

Bye, Olaf.

 

[GriffMG]

Ah, good point Olaf, he would need 23million campaigns for my hypothesis to be correct!

B-)

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[GriffMG]

Oops 2.3million campaigns

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Olaf Doschke]

Oh, and the expectation may not be clear.

Mikes Code set all records IDs, but it won't affect any new records. You'd need something more fancy to compute the nextid for a record and the campaign ID then is a must have value from each INSERT, eg you wouldn't be able to work with calling a stored procedure from the id field and APPEND BLANK, that would cause the next id to be computed for campaign id 0, always.

Overall it's rather not a good idea to have a two part primary key. You would use a autoinc id and campaign id and would use Mieks initial approach. Then you still have a sequential number incrementing with each new record of the whole table and of each campaign. To no the recno of the campaign from that would not be possible, but you could

SELECT id, Cast(0 as int) As recno from TheTable Where campaignID = 42 Readwrite
REPLACE ALL recno with Recno() and then have an on-the-fly generated translation from the overall id as a counter of all records to the recno of the campaign.

If mm really means million, that on the fly translation cursor will have some tenthousand records for an average campaign, but it should still be good enough. And if you only want to know the count, simply do

SELECT Count(*) From TheTable GROUP BY campaignID

There is no need to create a sub counter field. I avoid such computed fields wherever possible at whatever costs. The numbering of records is a moving target, since records can also be deleted. You won't want to renumber, ou won't want to update foreign keys and anything else involved with such computed fields, if you once have tried to work it out that way. It's only a burden without the benefit it would need to have to pay the cost.

Bye, Olaf.

 

[craigber]

Taking a step back and make sure we're all using the correct terminology.

RECNO() is unique to each row in a table and is managed by VFP. You can't have duplicate RECNO()s in a table.

What you want is a compound primary key that is based on the campaign. Expanding on Mike's code above, add a numeric column named Id to the table, then...

lnCampaign = 0
lnID = 0
SELECT TheTable
INDEX ON CampaignId TAG CampaignId
GO TOP

SCAN
IF TheTable.CamPaignID <> lnCampaign
lnID = 0
lnCampaign = TheTable.CampaignID
ENDIF
lnID = lnID + 1
REPLACE ID WITH lnID
ENDSCAN

INDEX ON STR(CampaignId) + STR(Id) TAG PrimaryKey

Craig Berntson
MCSD, Visual C# MVP,

http://www.craigberntson.com/blog

 

[Mike Lewis]

PKdata, if you still can't get it to work, please tell us in what way it is failing.

In fact, it would be helpful if you could give us some code to reproduce the problem you are seeing.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[pkdata]

This works great; thank you. Sorry for not getting back to you all sooner, was in meetings all day yesterday and this morning. Hope everyone has a great day!

 

[pkdata]

Olaf,

thank you so much for all the thought and effort you gave to help! One note, I agree with you ref the sub counter field, however, once all campaign files were concatenated, client came back requesting this be added for tracking purposes.

Again, thank you very much.

 

[Olaf Doschke]

Ok, fine, I'll just add: For the purpose of tracking a separate counted ID on it's own would work, too. It's a primary key on it's own, you can locate the record and then read any additional info, eg campaignid, can join the main campaign table and add the campaign name, purpose, a summary, memos whatever you want, the primary key is the key to data, it is not data itself, like the key to your house or flat is just the key to it and nothing else.

Bye, Olaf.