How to change one number on global update

[[email protected]]

I have 1000 invoice numbers I would like to change from 13000 to 14000 in my invoice field. I have not made any changes to this application in about 9 years and have forgotten much. Using alpha4v7. Any help?
Chuck

[Dave McClain]

Re: How to change one number on global update

I have 1000 invoice numbers I would like to change from 13000 to 14000 in my invoice field. I have not made any changes to this application in about 9 years and have forgotten much. Using alpha4v7. Any help?
Chuck

Chuck,

>View > Options > Search and Replace > Search for: 13000 >replace with : 14000

Choose the proper field and go. If you are not sure, make a new database and copy your DB to it to test. Changes are not reversible but this is cut and dry.

[[email protected]]

Re: How to change one number on global update

Chuck,

>View > Options > Search and Replace > Search for: 13000 >replace with : 14000

Choose the proper field and go. If you are not sure, make a new database and copy your DB to it to test. Changes are not reversible but this is cut and dry.

Thanks Dave. But I guess I did not explain properly. I want to change all the invoice numbers between 13000-13999 and change them to 14000-14999. If there was a wildcard that could be use in search and replace such as search *3*** and replace *4***, but I think a global update would work, but do not know what script to use.

[Dave McClain]

Re: How to change one number on global update

Thanks Dave. But I guess I did not explain properly. I want to change all the invoice numbers between 13000-13999 and change them to 14000-14999. If there was a wildcard that could be use in search and replace such as search *3*** and replace *4***, but I think a global update would work, but do not know what script to use.

Chuck,

OK, I'm not sure but you could still copy to another database and try the "wild card." I have done that but like you, it's been so long ago that I forget.

[dpawley]

Re: How to change one number on global update

I would use a global update. First search/sort and find the target records ie invoices in the 13000 range.

Then use global update and add 1000 to each invoice number. If the invoice is a character convert from string to numeric, add 1000 and convert back to string.

[[email protected]]

Re: How to change one number on global update

I would use a global update. First search/sort and find the target records ie invoices in the 13000 range.

Then use global update and add 1000 to each invoice number. If the invoice is a character convert from string to numeric, add 1000 and convert back to string.

Thanks Dave,
I tried your suggestion this weekend and I could not get it to work because I have forgotten most functions so I spent most of my time in the alpha 4v6 reference manual. I will keep trying.
Chuck

[dpawley]

Re: How to change one number on global update

Chuck,

I believe your expression in the global update should look something like one of the following:
The first INVOICENO you would use if the field is a number
The second would be if it is definded as a character field of length 6 with no decimal(ie the 6,0 at the end of the STR(n,length,decimal)).

You should only need one of the examples and not both!

 
                          [B]U p d a t e   F i e l d s[/B]
[U][B]Field        Expression[/B][/U]
 INVOICENO    INVOICENO+1000
 INVOICECHR   STR((VAL(INVOICECHR)+1000),6,0)

Please make sure you make a backup before performing any global updates incase something goes wrong.

And if you do not want to apply the update to all records in the table you would want to run a query (search/sor) and select only the records where invoice number is in the target range.

[[email protected]]

Re: How to change one number on global update

Chuck,

I believe your expression in the global update should look something like one of the following:
The first INVOICENO you would use if the field is a number
The second would be if it is definded as a character field of length 6 with no decimal(ie the 6,0 at the end of the STR(n,length,decimal)).

You should only need one of the examples and not both!

 
                          [B]U p d a t e   F i e l d s[/B]
[U][B]Field        Expression[/B][/U]
 INVOICENO    INVOICENO+1000
 INVOICECHR   STR((VAL(INVOICECHR)+1000),6,0)

Please make sure you make a backup before performing any global updates incase something goes wrong.

And if you do not want to apply the update to all records in the table you would want to run a query (search/sor) and select only the records where invoice number is in the target range.

Hi Dave,
I hope you are still with me. This is the first time I have had to work on this issue. Here is what I have found out so far. I did a search/sort for invoices 13000-14000 and it was successful. I then did a global update to the search/sort list and ran this expresion
STR((VAL(INVOICE_NO)+7000),5,0)
Here is what happened. The invoice numbers were changed to 20000-21000 as they should, except at random the first 2 numbers of the invoice numbers were changed to 27 or 34 or 41, (example 27338, 34339, 41340, 41556, 34660). The last 3 numbers were always correct. I looked at the original invoices and discovered that where this problem occurred there were more than 1 child record in that invoice (parts for that invoice). If there were 2 child records the invoice number became 27000, if there were 3 child records the invoice number became 34000. if there were 4 child records it became 41000 etc. Do you have any idea what is going on. Thank you for your help so far.

Chuck

[Doug Page]

Re: How to change one number on global update

In my opinion you should just run this against the table you are changing and not a set. Then it should all work perfectly. If the link field is Invoice Number then you could run it twice - once for each table.

Explanation:
A set is just a virtual representation of the combination of tables. So if you were using tables INVOICE_MASTER and INVOICE_DETAIL and a set INVOICES and INVOICE_MASTER had 2 records and INVOICE_DETAIL had 1 detail record for the first master and 3 detail records for the second, the your set would appear to have four records which is the number of combinations of the master and detail records. So using your update against the set you would change the first record which has one combination of master-detail and the renumbering works. The second record of the set is the first combination of the second master record and the renumbering would work correctly on this record. However, when you continue to the next record in the SET, it is still pointing at the same invoice number field which has already been changed so it would add the 7000 again. The same is true for the third combination which is why you are seeing the progression that you are.

If the master/detail files look like this:
INVOICE_MASTER
13000 CUSTNO1
13001 CUSTNO2
INVOICE_DETAIL
13000 PROD1
13001 PROD1
13001 PROD2
13001 PROD3
INVOICES

Then the original set would look like this:
13000 CUSTNO1 PROD1
13001 CUSTNO2 PROD1
13001 CUSTNO2 PROD2
13001 CUSTNO2 PROD3

And after the renumbering update cycles through the first record:
20000 CUSTNO1 PROD1
13001 CUSTNO2 PROD1
13001 CUSTNO2 PROD2
13001 CUSTNO2 PROD3

And after the renumbering update cycles throught the second record:
20000 CUSTNO1 PROD1
20001 CUSTNO2 PROD1
20001 CUSTNO2 PROD2
20001 CUSTNO2 PROD3

Now you can see that although the update has not even done its thing on the 3rd and 4th records the Invoice Number has alread updated. That is because the set is a virtual table made up of the actual tables and when we updated the second record of the set it changed the invoice field in the INVOICE_MASTER table which then immediately updated ALL records in the set. So when your update routine goes to its completion it would add another 14000 to the invoice number making the fianl result as 34001.

Hopefully you can follow my explanation and see why you should never run updates against a set and only a table.

[[email protected]]

Re: How to change one number on global update

Doug,
Thanks for the insight. That solved the problem. I applied the global update to each table and it worked. Thanks to all for my requests for help. I dont think I could have solved it by myself.
Chuck