How to print mailing labels for customers needing turn-on service

Related Topics



The request


"Can you please make a command so that I can print out only customers who have used us within the past 2 years (or since 2012), deleting customers marked with no service.  I need their name and address."


Some clarifications


1. When she says "used us within the past 2 years", does she mean "used us for a turn-on", or for anything at all?


Default: any job at all


2. Should only DONE/CLOSED tickets be selected?


Default: all jobs selected


3. What about customers who are already on the schedule for a turn-on?


Default: skip these customers


4. Should duplicate customer addresses be skipped?


Default: skip duplicates



The SQL command:


select * from custlist where !noservice and exists(select * from tracklog where custlist.fileno = tracklog.fileno and  between(actiondate,ctod($$PROMPT_DATE$$),date())) and !exists (select * from tracklog where custlist.fileno = tracklog.fileno and component = 'TURN ON' and inlist(status,'INITIAL','OPEN') and between (actiondate,date(),date()+90) )



 is broken down as follows:



Command, broken down Means

select *


Select all fields


 from custlist


from customers table


where !noservice and


Select customers where the NOSERVICE flag is not checked


exists(select * from tracklog where custlist.fileno = tracklog.fileno and  between(actiondate,ctod("$$PROMPT_DATE$$"),date()))


and where the customer has ordered a job between a date in the past (operator will  enter 1/1/2012 when prompted) and "today"


and !exists (select * from tracklog where custlist.fileno = tracklog.fileno and component = 'TURN ON'  and inlist(status,'INITIAL','OPEN') and between (actiondate,date(),date()+90) )


and an OPEN job ticket for a TURN ON for this customer within the next 90 days does not exist






Screenshot sequence showing this SQL SELECT being used



Begin by selecting Print -> Mailing Labels -> Avery Labels






The Avery Address label printer screen will appear











The SQL SELECT COMMAND LIBRARY screen will appear. Check / select the SQL command created for this exercise

and click SELECT   (or click TEST to run the select and let you view the results)






A date prompt screen will appear.


Enter 1/1/2012


(click in month field, enter 1, click in day field, enter 1, click in year field enter 12, then click somewhere else on the form to see the date

get set to 1/1/2012)


Click OK




You will be returned to the Record Selection Manager screen with the record selection performed.


Note that the count in this case is 1,954 records selected of 4,818






Since the "SKIP DUPLICATE ADDRESSES" option is checked, H2OS will now remove records with duplicate customer addresses





Click OK



Notice the PREVIEW checkbox is checked. This means the mailing labels

will appear on screen for examination before sending them to the actual






Click YES


Selected records will now appear as mailing labels.


Click the printer icon on the toolbar to send these labels to the printer


Click the CLOSE icon this toolbar to close this screen without printing.