1. Home
  2. Point-Of-Sale Software
  3. Method to do Mass Field Updates

Method to do Mass Field Updates

We're trying to clean up our email list. In the process thereof I'd like to place different information in the Company field (which we use to display brief notes on the POS display) based on wheter or not we have a valid and current email address for a given customer.

So, I have a comma-delimited text list for all our invalid emails. Associated with the email address is the customer's first and last name.

So, what I would like to do is to update the Company Field with the words "Update Email Address' for every matching first and last name combo in the list of invalid names.

I am VERY open to suggestions on how best to do this. The thought of typing in this info for almost 2000 records is not terribly appealing.

Thanks in advance!

Bud Izen

Reply to
Bud Izen
Loading thread data ...

WARNING - this procedure is not for the faint of heart or technically clumsy. PROCEED AT YOUR OWN RISK.

Run this query and export the results: SELECT FirstName, LastName, ID FROM Customer

Open the exported file with Excel. Copy your existing list into the same worksheet.

Insert a blank column between LastName & ID. In this column, use =R1C1&" "&R1C2 to make a new column of FirstName[space]LastName. Do this twice (once for the exported columns and once for your list). Use VLOOKUP to select an ID from the exported data. IN the VLOOKUP 'range' you will need to use an absolute reference (See Excel help if you don't know what I mean by R1C1 or absolute reference).

Once you have an ID associated with the 'bad e-mail' names, you can enter a new formula in another column: ="UPDATE Customer SET Company = 'Bad e-mail' WHERE ID = "&R1C1 Fill this formula down the entire column so that you have a series of query statements, one for each Customer ID.

Ideally, wait until the store is closed. Back up you database. Copy the entire collection of query statements into a new So Admin query window. Run. The result will be '1 record affected' because the results are only from the last query executed. You can then double-check by inspecting a few accounts in SO Manager. If there was a problem, you can restore from backup and try again.

Good luck, Tom

Reply to
Terrible Tom

Interesting concept, Tom. Very clear instructions, so I don't foresee a problem. I've made a copy of our system on my portable, so I can test this without worry.

Thanks for your suggestions. I'll let you know how it turns out.

Bud

"Terrible Tom" wrote:

Reply to
Bud Izen

I got the idea from someone else on this group. I have used it before with good results.

I'd love to hear how it goes.

Tom

Reply to
Terrible Tom

BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.