Issue:
When trying to use the Customer Query Builder, the fields in the Customer Billing information for the email and print options are not available.
Solution:
The Invoice Print/Email options were added to the table, but the Query Builder was not updated to include these new fields. The previous Email_Invoice flag is available but is no longer used.
The Query Builder application uses data stored in two tables within the database to determine the fields available for use in the application.
We can add these new fields, so they are available in the Customer Query Builder.
The script below will insert the correct records into the table so user can select these newer fields in their queries.
--- Insert the AR_Customer_Bill Invoice Option fields for use in the Customer Query Builder ---
INSERT INTO [dbo].[SD_Schema]
([Table]
,[Column]
,[DataType]
,[DataLength]
,[LinkedTo]
,[JoinType]
,[Version]
,[Sequence]
,[PrintIt]
,[Cat]
,[Introduced]
,[LinkedColumn]
,[Description])
VALUES
('AR_Customer_Bill','Email_Cycle_Invoice','char',1,'','','6.1.0.17','081-034',1,'AR','6.1.0.17','',''),
('AR_Customer_Bill','Email_Job_Invoice','char',1,'','','6.1.0.17','081-035',1,'AR','6.1.0.17','',''),
('AR_Customer_Bill','Email_Service_Invoice','char',1,'','','6.1.0.17','081-036',1,'AR','6.1.0.17','',''),
('AR_Customer_Bill','Email_Other_Invoice','char',1,'','','6.1.0.17','081-037',1,'AR','6.1.0.17','',''),
('AR_Customer_Bill','Print_Cycle_Invoice','char',1,'','','6.1.0.17','081-038',1,'AR','6.1.0.17','',''),
('AR_Customer_Bill','Print_Job_Invoice','char',1,'','','6.1.0.17','081-039',1,'AR','6.1.0.17','',''),
('AR_Customer_Bill','Print_Service_Invoice','char',1,'','','6.1.0.17','081-040',1,'AR','6.1.0.17','',''),
('AR_Customer_Bill','Print_Other_Invoice','char',1,'','','6.1.0.17','081-041',1,'AR','6.1.0.17','','')
GO
INSERT INTO [dbo].[SD_Query_Builder_Def]
([Query_Type]
,[Category]
,[Friendly_Name]
,[Data_Field_Name]
,[DataType]
,[Cat_Num]
,[Sub_Cat]
,[Sort_Order]
,[Table_Name]
,[Table_Join]
,[Dependent_On]
,[Raw_DataField]
,[Raw_Table_Name])
VALUES
('C','Customer','Email_Cycle_Invoice','AR_Customer_Bill.Email_Cycle_Invoice','char',20,'Bill_To',12400,'AR_Customer_Bill'
,'Inner JOIN AR_Customer_Bill On AR_Customer.Customer_Id = AR_Customer_Bill.Customer_Id','AR_Customer'
,'AR_Customer AR_Customer_Bill.Email_Cycle_Invoice','AR_Customer_Bill'),
('C','Customer','Email_Job_Invoice','AR_Customer_Bill.Email_Job_Invoice','char',20,'Bill_To',12500,'AR_Customer_Bill'
,'Inner JOIN AR_Customer_Bill On AR_Customer.Customer_Id = AR_Customer_Bill.Customer_Id','AR_Customer'
,'AR_Customer AR_Customer_Bill.Email_Job_Invoice','AR_Customer_Bill'),
('C','Customer','Email_Service_Invoice','AR_Customer_Bill.Email_Service_Invoice','char',20,'Bill_To',12600,'AR_Customer_Bill'
,'Inner JOIN AR_Customer_Bill On AR_Customer.Customer_Id = AR_Customer_Bill.Customer_Id','AR_Customer'
,'AR_Customer AR_Customer_Bill.Email_Service_Invoice','AR_Customer_Bill'),
('C','Customer','Email_Other_Invoice','AR_Customer_Bill.Email_Other_Invoice','char',20,'Bill_To',12700,'AR_Customer_Bill'
,'Inner JOIN AR_Customer_Bill On AR_Customer.Customer_Id = AR_Customer_Bill.Customer_Id','AR_Customer'
,'AR_Customer AR_Customer_Bill.Email_Other_Invoice','AR_Customer_Bill'),
('C','Customer','Print_Cycle_Invoice','AR_Customer_Bill.Print_Cycle_Invoice','char',20,'Bill_To',12800,'AR_Customer_Bill'
,'Inner JOIN AR_Customer_Bill On AR_Customer.Customer_Id = AR_Customer_Bill.Customer_Id','AR_Customer'
,'AR_Customer AR_Customer_Bill.Print_Cycle_Invoice','AR_Customer_Bill'),
('C','Customer','Print_Job_Invoice','AR_Customer_Bill.Print_Job_Invoice','char',20,'Bill_To',12900,'AR_Customer_Bill'
,'Inner JOIN AR_Customer_Bill On AR_Customer.Customer_Id = AR_Customer_Bill.Customer_Id','AR_Customer'
,'AR_Customer AR_Customer_Bill.Print_Job_Invoice','AR_Customer_Bill'),
('C','Customer','Print_Service_Invoice','AR_Customer_Bill.Print_Service_Invoice','char',20,'Bill_To',13000,'AR_Customer_Bill'
,'Inner JOIN AR_Customer_Bill On AR_Customer.Customer_Id = AR_Customer_Bill.Customer_Id','AR_Customer'
,'AR_Customer AR_Customer_Bill.Print_Service_Invoice','AR_Customer_Bill'),
('C','Customer','Print_Other_Invoice','AR_Customer_Bill.Print_Other_Invoice','char',20,'Bill_To',13100,'AR_Customer_Bill'
,'Inner JOIN AR_Customer_Bill On AR_Customer.Customer_Id = AR_Customer_Bill.Customer_Id','AR_Customer'
,'AR_Customer AR_Customer_Bill.Print_Other_Invoice','AR_Customer_Bill')
GO
