Unfortunately M2M does not automatically populate the Country field on many screens when a US State is selected. This can cause problems in reporting when an executive wants sales broken down by country and many of them are grouped under Null.
Anyway, if you want to see how many addresses are missing “United States” you can run the following script. Once again, please read my standard disclaimer.
select * from syaddr WHERE FCCOUNTRY = '' AND fcstate IN ('AL','AK','AZ','AR', 'CA','CO','CT','DE', 'DC','FL','GA','HI', 'ID','IL','IN','IA', 'KS','KY','LA','ME', 'MD','MA','MI','MN', 'MS','MO','MT','NE', 'NV','NH','NJ','NM', 'NY','NC','ND','OH', 'OK','OR','PA','RI', 'SC','SD','TN','TX', 'UT','VT','VA','WA', 'WV','WI','WY')
If you want to fix them automatically, you may want to run the following script. It will update all of the blank country records fields that are associated with US states.
UPDATE syaddr SET FCCOUNTRY = 'United States' WHERE FCCOUNTRY = '' AND fcstate IN ('AL','AK','AZ','AR', 'CA','CO','CT','DE', 'DC','FL','GA','HI', 'ID','IL','IN','IA', 'KS','KY','LA','ME', 'MD','MA','MI','MN', 'MS','MO','MT','NE', 'NV','NH','NJ','NM', 'NY','NC','ND','OH', 'OK','OR','PA','RI', 'SC','SD','TN','TX', 'UT','VT','VA','WA', 'WV','WI','WY')
If you try to run the example code in VFP 8 or less, there is a maximum of 24 allowable values for the IN() clause.
Scott, you do realize that what I posted is T-SQL code, correct? Are you still using a VFP version of M2M? If so, I can follow up with VFP code to accomplish the same thing.